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.

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.

See Excel Keyboard Shortcuts for Windows and Mac.

Modifier Keys

Special Key	Mac	Windows
Alt or Option	⌥	Alt
Cancel	Esc	Esc
Command	⌘	Cmd
Control	⌃	Ctrl
Enter or Return	⏎	Enter
Function	fn	Fn
Shift	⇧	Shift

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.

Action	Mac	Windows
New workbook	⌘ N	Ctrl N
Open workbook	⌘ O	Ctrl O
Save workbook	⌘ S	Ctrl S
Save as	⌘ ⇧ S	F12
Print	⌘ P	Ctrl P
Close workbook	⌘ W	Ctrl F4
Quit Excel	⌘ Q	Alt 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.

Action	Arrows	Mac	Windows
Enter and cell down	↓	⏎	Return
Enter and cell up	↑	⇧ ⏎	Shift Return
Enter and cell right	→	Tab	Tab
Enter and cell left	←	⇧ Tab	Shift 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. 

Action	Mac	Windows
Help	⌘ /	F1
Undo	⌘ Z	Ctrl Z
Redo	⌘ Y	Ctrl Y
Copy	⌘ C	Ctrl C
Cut	⌘ X	Ctrl X
Paste	⌘ V	Ctrl V
Paste Special	⌃ ⌘ V	Ctrl Alt V
Find	⌘ F	Ctrl F
Select All	⌘ A	Ctrl A
Edit cell	⌃ U	F2

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

Advanced Shortcuts

Action	Mac	Windows
Insert Comment	Fn ⇧ F2	Shift F2
Add Hyperlink	⌘ K	Ctrl K
Calculate Formulas	F9	F9
Absolute Address	F4	F4
Repeat Last Action	F4	F4
Show Formulas	^ `	Ctrl `
Enter Current Date	⌃ ;	Ctrl ;
Enter Current Time	⌘ ;	Ctrl Shift :
Format Cells (dialog box)	⌘ 1	Ctrl 1
Delete (dialog box)	⌘ –	Ctrl –
Insert (dialog box)	⌘ +	Ctrl Shift +
Flash Fill	⌃ E	Ctrl E
AutoSum	⌘ ⇧ T	Alt =
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.

Action	Mac	Windows
Select Entire Row	⇧ Space	Shift Space
Select Entire Column	⌃ Space	Ctrl 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

These number formatting shortcuts change numbers to other formats.

Action	Mac	Windows	Result
General	⌃ ⇧ ~	Ctrl Shift ~	43650.63
Number	⌃ ⇧ 1	Ctrl Shift 1	43,650.63
Time	⌃ ⇧ 2	Ctrl Shift 2	3:07 PM
Date	⌃ ⇧ 3	Ctrl Shift 3	4-Jul–19
Currency	⌃ ⇧ 4	Ctrl Shift 4	$43,650.63
Percent	⌃ ⇧ 5	Ctrl Shift 5	4365063%
Scientific	⌃ ⇧ 6	Ctrl Shift 6	4.37E+04
Outside Borders	⌃ ⇧ 7	Ctrl Shift 7	Cell 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 2021, 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 Type	File 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 123, text 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 Format	Example
General	no specific format
Number	1,234.56
Currency	$123.45 (dollars or other currency)
Accounting	$ 123.45 (symbol on left edge of cell)
Short Date	12/31/18
Long Date	Monday, December 31, 2018
Time	12:30:15 AM
Percentage	12.34%
Fraction	1/3
Scientific	1.23E+05
Text	format as a text label (left aligned)

Excel Error Messages

Excel uses several error messages to help identify problems. 

Error Message	Description
######	Column width of a cell is too narrow
#NAME?	Unrecognized text in a formula
#N/A	No answer can be found
#REF!	Invalid cell reference
#VALUE!	Wrong argument type or operand
#NUM!	Invalid numeric values in a formula or function
#DIV/0	Division 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. 

Calculation	Operator	Example
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

Formula	Description	Result
=1+2*3	2*3 is calculated first then +1	7
=(1+2)*3	1+2 is calculated first then *3	9
=20/4–3	20/4 is calculated first then –3	2
=20/(4–3)	4–3 is calculated first then divided into 20	20
=10/5*2	Same level so left to right	4
=2*3^2	3 is squared first then multiplied by 2	18

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

Function	Purpose
AVERAGE	arithmetic mean
COUNT	counts cells with numbers only
COUNTA	counts cells with numbers or text
COUNTBLANK	counts empty cells
MIN	minimum value
MAX	largest value
PRODUCT	product of cells
SUM	sum 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 Reference	Example	Column	Row
Absolute	$B$5	absolute	absolute
Mixed	$B5	absolute	relative
Mixed	B$5	relative	absolute
Relative	B5	relative	relative

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

https://youtu.be/FvU4onTHvA8

Introduction to Excel Video

https://youtu.be/fOkdhwesDnw

---