Introduction to Excel

introduction to excel

Excel is the most important app in business. This introduction to Excel will get you started even if you are an absolute beginner.

Microsoft Excel is the leading digital spreadsheet that manages, calculates, and presents data. It is one of the most fundamental tools for data analysis. Many jobs require prospective candidates to have advanced Excel skills.

This introduction to Excel will show the basics for getting started with Excel for Windows and Mac. Knowing Excel is an important life skill!


Introduction to Excel

This Introduction to Excel is Lesson 1 in Excel Basics. These lessons cover topics to help you learn data analysis using Excel.

For all the lessons, see The Ultimate Guide to Finally Learn Excel.


Columns, Rows and Cells

Excel uses a grid of columns and rows to create cells. The columns are labeled with letters A, B, C, D, etc. The rows are named with numbers 1, 2, 3, etc. A cell is an intersection of columns and rows. The cell at the intersection of column B and row 3 is called B3.

Cell B3 is Selected

To begin, you need to know some basic keyboard shortcuts that will make you an efficient user of Excel. The following tables show several important keyboard shortcuts that are useful.

Excel Keyboard Shortcuts

Excel uses several modifier keys to perform different functions. These are great to be a proficient user of Excel.

Modifier Keys

Special KeyMacWindows
Alt or OptionAlt
CancelEscEsc
CommandCmd
ControlCtrl
Enter or ReturnEnter
FunctionfnFn
ShiftShift

For a list of all the different keyboard symbols, see What are the Keyboard Symbol Names?

File Shortcuts

The following keyboard shortcuts relate to opening, saving, and printing files.

ActionMacWindows
New workbook⌘ NCtrl N
Open workbook⌘ OCtrl O
Save workbook⌘ SCtrl S
Save as⌘ ⇧ SF12
Print⌘ PCtrl P
Close workbook⌘ WCtrl F4
Quit Excel⌘ QAlt F4

Data Entry Keys

The following shortcuts allow for rapid data entry. When you enter data and hit ⏎ (Enter), Excel will save the data and move to the cell directly below. With the following shortcuts, you can force the cell to move in any direction.

ActionArrowsMacWindows
Enter and cell downReturn
Enter and cell up⇧ ⏎Shift Return
Enter and cell rightTabTab
Enter and cell left⇧ TabShift Tab

You should learn to use Excel keyboard shortcuts instead of the mouse or trackpad. The keyboard is mightier than the mouse!

Editing Shortcuts

The following shortcuts are used to edit in Excel. Keyboard shortcuts are much faster than using a mouse or trackpad to use the menu. 

ActionMacWindows
Help⌘ /F1
Undo⌘ ZCtrl Z
Redo⌘ YCtrl Y
Copy⌘ CCtrl C
Cut⌘ XCtrl X
Paste⌘ VCtrl V
Paste Special⌃ ⌘ VCtrl Alt V
Find⌘ FCtrl F
Select All⌘ ACtrl A
Edit cell⌃ UF2

You need to know Copy, Cut, Paste, and Undo. You will use them all the time.

Advanced Shortcuts

ActionMacWindows
Insert CommentFn ⇧ F2Shift F2
Add Hyperlink⌘ KCtrl K
Calculate FormulasF9F9
Absolute AddressF4F4
Repeat Last ActionF4F4
Show Formulas^ `Ctrl `
Enter Current Date⌃ ;Ctrl ;
Enter Current Time⌘ ;Ctrl Shift :
Delete (dialog box)⌘ –Ctrl –
Insert (dialog box)⌘ +Ctrl Shift +
Flash Fill⌃ ECtrl E
AutoSum⌘ ⇧ TAlt =
Select Tab to the Right⌥ →Ctrl PgDn
Select Tab to the Left⌥ ←Ctrl PgUp

Note that F4 can do two separate but important things: 

  1. absolute address for cells in formulas
  2. repeat last action.

Some keys on some keyboards have special symbols and functions. To use these keys as F1, F2, F3, or other standard function keys, combine them with the Fn key.

Selection Shortcuts

The following shortcuts select entire rows, columns, or tables. These shortcuts are much faster then dragging the mouse or highlighting cells.

ActionMacWindows
Select Entire Row⇧ SpaceShift Space
Select Entire Column⌃ SpaceCtrl Space
Extend Last Cell Right⌘ ⇧ →Ctrl Shift →
Extend Last Cell Left⌘ ⇧ ←Ctrl Shift ←
Extend Last Cell Up⌘ ⇧ ↑Ctrl Shift ↑
Extend Last Cell Down⌘ ⇧ ↓Ctrl Shift ↓
Top Cell⌘ ↑Ctrl ↑
Bottom Cell⌘ ↓Ctrl ↓
Left Cell⌘ ←Ctrl ←
Right Cell⌘ →Ctrl →

Number Formatting Shortcuts

ActionMacWindowsResult
General⌃ ⇧ ~Ctrl Shift ~43650.63
Number⌃ ⇧ 1Ctrl Shift 143,650.63
Time⌃ ⇧ 2Ctrl Shift 23:07 PM
Date⌃ ⇧ 3Ctrl Shift 34-Jul–19
Currency⌃ ⇧ 4Ctrl Shift 4$43,650.63
Percent⌃ ⇧ 5Ctrl Shift 54365063%
Scientific⌃ ⇧ 6Ctrl Shift 64.37E+04
Outside Borders⌃ ⇧ 7Ctrl Shift 7Cell Borders

Microsoft 365

Microsoft Office is the most popular software suite. Microsoft 365 is available as a subscription for both macOS and Windows. It includes the latest version of Word, Excel, Powerpoint, and more. 

University students and faculty can get a free subscription for Microsoft 365 by using their university email. It includes the latest version of Word, Excel, Powerpoint, and more. It is available for both macOS and Windows.

Microsoft Excel with a Microsoft 365 subscription is the latest version of Excel. Previous versions include Excel 2019, Excel 2016, Excel 2013, Excel 2010, and Excel 2007.

Excel File Formats

Excel 365 can save into 26 different file formats. Here are the most important file types.

File TypeFile Extension
Excel workbook.xlsx
Excel template.xltx
Excel macro-enabled workbook.xlsm
Excel macro-enabled template.xltm
PDF.pdf
OpenDocument spreadsheet.ods
Comma separated values.csv
Web page.htm

Excel cells are arranged in rows and columns. Columns are named A, B, C, D, and so on. Rows are named 1, 2, 3, 4, and so on. The maximum rows and columns are 1,048,576 rows by 16,384 columns. So, the first cell is A1 and the last cell is XFD1048576.

Excel cells can contain numeric values such as 123text labels such as Sales, and calculated values such as 12,300. Workbooks usually contain areas for inputs that include labels and variable values and areas for outputs for calculations and results.

Number Formatting

Excel can handle both numbers and text. It has many formatting options for numbers.

Excel is precise to 15 digits even if the display shows less digits. So, 5.12 would be displayed when 5.123456789012345 is the actual stored value.

Excel counts dates beginning with January 1, 1900. So, January 1, 1900 is day 1 and January 1, 2020 is day 43,831 for Excel. 

Number FormatExample
Generalno specific format
Number1,234.56
Currency$123.45 (dollars or other currency)
Accounting$ 123.45 (symbol on left edge of cell)
Short Date12/31/18
Long DateMonday, December 31, 2018
Time12:30:15 AM
Percentage12.34%
Fraction1/3
Scientific1.23E+05
Textformat as a text label (left aligned)

Excel Error Messages

Excel uses several error messages to help identify problems. 

Error MessageDescription
######Column width of a cell is too narrow
#NAME?Unrecognized text in a formula
#N/ANo answer can be found
#REF!Invalid cell reference
#VALUE!Wrong argument type or operand
#NUM!Invalid numeric values in a formula or function
#DIV/0Division by zero
#SPILL!Spill formula error in a dynamic range

Math in Excel

Excel uses the following symbols as math operators. Formulas can include numbers, cell references, or functions. 

CalculationOperatorExample
Addition+=A1+5
Subtraction=7-B2
Multiplication*=A5*B1
Division/=C3/C5
Exponentiation^=2^3

Math Order of Operations

Remember PEMDAS

  1. Parentheses
  2. Exponents
  3. Multiplication and Division from left to right
  4. Addition and Subtraction from left to right

Numbers, Cell References, and Cell Ranges

Working with functions requires knowing the function syntax. For example, the SUM function adds a list of values and its syntax is: =SUM (number1, [number2], [number3], …). Terms in brackets are optional. 

Assume the cells A1-A5 contained the following numbers: 1, 3, 5, 7, and 9. If you wanted to add all the values in cells A1-A5, there are three different ways to use the function that would be equivalent. Each would show 25 as the answer in the cell.

  1. Numbers
    =SUM (1, 3, 5, 7, 9)
    This format uses the actual numbers. It gives a correct answer but if the given numbers change, the sum is wrong. This is called hard coding the numbers. This method is not recommended.
  2. Cell References
    =SUM (A1, A2, A3, A4, A5)
    This format uses the cell references rather than the actual numbers. The advantage is that if the numbers in A1-A5 change, the new correct answer is shown. This method is correct, but not as efficient as using the range as in #3.
  3. Cell Range
    =SUM (A1: A5)
    This format uses the cell range rather than the actual numbers. The advantage is that if the numbers in A1-A5 change, the new correct answer is shown. This method is the preferred method.

Excel Formula Examples

FormulaDescriptionResult
=1+2*32*3 is calculated first then +17
=(1+2)*31+2 is calculated first then *39
=20/4–320/4 is calculated first then –32
=20/(4–3)4–3 is calculated first then divided into 2020
=10/5*2Same level so left to right4
=2*3^23 is squared first then multiplied by 218

Excel Functions

Excel has hundreds of special functions. They start with an equal sign and then the function name. For example, =SUM(A5:B10) would add all the numbers in that range.

The three most used Excel functions are:

  1. SUM
  2. AVERAGE
  3. VLOOKUP

Basic Excel Functions

FunctionPurpose
AVERAGEarithmetic mean
COUNTcounts cells with numbers only
COUNTAcounts cells with numbers or text
COUNTBLANKcounts empty cells
MINminimum value
MAXlargest value
PRODUCTproduct of cells
SUMsum of cells

AVERAGE

AVERAGE returns the mean of values supplied as multiple arguments. AVERAGE can handle up to 255 individual arguments, which can include numbers, cell references, ranges, arrays, and constants.

=AVERAGE (number1, [number2], …)

number1 – A number or cell reference that refers to numeric values.
number2 – [optional] A number or cell reference that refers to numeric values.

COUNT

COUNT returns the count of cells that are contain numbers, ignores text, logical values, error values, and empty cells. Values can be supplied as constants, cell references, or ranges.

=COUNT (value1, [value2], …)

value1 – An item, cell reference, or range.
value2 – [optional] An item, cell reference, or range.

COUNTA

COUNTA returns the count of cells that contain numbers, text, logical values, error values, and empty text (“”). COUNTA does not count empty cells.

=COUNTA (value1, [value2], …)

value1 – An item, cell reference, or range.
value2 – [optional] An item, cell reference, or range.

COUNTBLANK

COUNTBLANK returns a count of empty cells in a range. Cells that contain text, numbers, errors, etc. are not counted. Formulas that return empty text are counted.

=COUNTBLANK (range)

range – The range in which to count blank cells.

MIN

MIN returns the smallest numeric value in a range of values. The MIN function ignores empty cells, the logical values TRUE and FALSE, and text values.

=MIN (number1, [number2], …)

number1 – Number, reference to numeric value, or range that contains numeric values.
number2 – [optional] Number, reference to numeric value, or range that contains numeric values.

MAX

MAX returns the largest numeric value in a range of values. The MIN function ignores empty cells, the logical values TRUE and FALSE, and text values.

=MAX (number1, [number2], …)

number1 – Number, reference to numeric value, or range that contains numeric values.
number2 – [optional] Number, reference to numeric value, or range that contains numeric values.

PRODUCT

PRODUCT returns the product of values supplied as multiple arguments. SUM can handle up to 255 individual arguments, which can include numbers, cell references, ranges, arrays, and constants.

=PRODUCT (number1, [number2], [number3], …)

number1 – A number or cell reference that refers to numeric values.
number2 – [optional] A number or cell reference that refers to numeric values.
number3 – [optional] A number or cell reference that refers to numeric values.

SUM

SUM returns the sum of values supplied as multiple arguments. SUM can handle up to 255 individual arguments, which can include numbers, cell references, ranges, arrays, and constants.

=SUM (number1, [number2], [number3], …)

number1 – A number or cell reference that refers to numeric values.
number2 – [optional] A number or cell reference that refers to numeric values.
number3 – [optional] A number or cell reference that refers to numeric values.

Absolute and Relative cell references

  • Absolute cell reference: $B$5 if you copy across or down the cell reference will stay the same; column and row are absolute
  • Mixed cell reference: $B5 if you copy up/down the cell reference will change, but not left/right; column is absolute and row is relative
  • Mixed cell reference: B$5 if you copy left/right the cell reference will change, but not up/down: column is relative and row is absolute
  • Relative cell reference: B5 if you copy left/right or up/down the cell reference will change; column and row are relative
Cell ReferenceExampleColumnRow
Absolute$B$5absoluteabsolute
Mixed$B5absoluterelative
MixedB$5relativeabsolute
RelativeB5relativerelative

Named Ranges

An alternative to absolute cell references is a named range. A single cell or a range can be named and that name can be used directly in a formula or function. For example a named range can be called ‘Sales’ rather than B7:B45.

To name a cell or a range, first highlight the cell or range and then click the Name Box below the ribbon to the left. The Name Box is just to the left of the Formula Bar. A named range is an absolute cell reference.

Named ranges are located in the Formulas tab on the ribbon under the Define Name button. Named ranges can be added, edited, or deleted.

Excel Basics Video

Excel Basics for Beginners

Introduction to Excel Video

Excel Basics Lesson 1

Jeff Mankin

Jeff Mankin teaches financial literacy. His website is FinallyLearn.com.

Recent Posts