The Ultimate Guide to Learn Excel Basics

Excel is the most important computer app in business. It is used in every industry and by many professionals. Many jobs require prospective candidates to have advanced Excel skills.

Microsoft Excel is the leading digital spreadsheet that manages, calculates, and presents data. It is one of the most fundamental tools for data analysis. 

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


Lesson 1

Introduction to Excel for Beginners

Getting Started With Excel

Excel cells are arranged in rows and columns. 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. In the example below, cell B3 is selected.

Cell B3

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. 

Excel Keyboard Shortcuts

Excel uses several modifier keys to perform different functions. These keyboard shortcuts are useful for any Excel user. These are necessary to be an advanced user of Excel. See Excel Keyboard Shortcuts for Windows and Mac.

For the complete lesson, see Lesson 1: Introduction to Excel for Beginners.


Lesson 2

Excel Statistical Functions

Statistics

Statistics is the study of mathematics used to explain groups of data, or data sets. Statistical methods are used to compare different groups or data sets. The following table lists the basic statistic terms.

StatisticDescriptionExcel Function
Meanarithmetic average of a set of numbersAVERAGE
Medianvalue that occurs in the middle of a data set when arranged from lowest to highest, half the values above and half below the median valueMEDIAN
Modevalue that occurs most frequently in a data setMODE.MULT, MODE.SNGL
Standard Deviationmeasure of how widely the data values are dispersed from the arithmetic meanSTDEV.P, STDEV.S

There are many Excel functions for statistical analysis.

Basic Statistical Functions

FunctionDescription
AVERAGEIFSaverage based on a logical test (replaces AVERAGEIF)
COUNTIFScount based on a logical test (replaces COUNTIF)
LARGEnth largest value
MEDIANmedian or the middle value in a set
MODE.SNGLmode or most frequent number
RANDrandom number from 0 to 1
RANDBETWEENrandom number between numbers specified
RANK.AVGrank in a list of numbers, any ties are averaged
RANK.EQrank in a list of numbers, ties are shown as first number
SMALLnth smallest value
STDEV.Pstandard deviation of a population
STDEV.Sstandard deviation of a sample
SUMIFSsum based on a logical test (replaces SUMIF)

For the complete lesson, see Excel Basics Lesson 2: Statistical Analysis.


Lesson 3

Excel Graphs and Charts

Excel Graphs

Excel can display several types of graphs to visualize data. There are at least 26 different chart types in Excel. The following table shows the common chart types.

GraphDescription
Areatrends over time or by category
Bubblethree values, the third value graphed as the size of a bubble
Columnvertical display
Barhorizontal display
Combocombines two or more chart types
Linetrends over time or by category
Piecomponents of a total amount
Stockstock prices and and volume trends over time
X Y (Scatter)compares pairs of numeric values on the x and y axes

For the complete lesson, see Excel Basics Lesson 3: Visualizing Data With Graphs.


Lesson 4

Excel Logic Functions

There are 11 logical functions in Excel. They are on the Formulas ribbon and the Logical button. They include IF and IFS functions.

Excel Logical Functions

Excel Logical Functions

The following table shows all the Excel logical functions.

FunctionDescription
ANDreturns TRUE only if all arguments are TRUE
FALSEreturns the logical value FALSE
IFchecks whether a condition is met, returns one value if TRUE and another value if FALSE
IFERRORreturns a specified value if expression is an error and the function value otherwise
IFNAreturns a specified value if the expression resolves to #N/A, and the function value otherwise
IFSruns multiple tests and returns a value corresponding to the first TRUE result
NOTchanges TRUE to FALSE, or FALSE to TRUE
ORreturns TRUE if any arguments are TRUE
SWITCHreturns a result on the first matching value
TRUEreturns the logical value TRUE
XORreturns a logical “exclusive or” of all arguments

For the complete lesson, see Excel Basics Lesson 4: Using Logic in Decision Making.


Lesson 5

Excel Text Functions

Excel is famous for working with numbers and calculations. However, Excel has many text functions. It is the most useful app for working with cleaning text from a database. 

Excel Text Functions

Here are the basic text edit functions in Excel.

FunctionDescription
CLEANremoves all non printable characters
TRIMremoves spaces from a text string except for single spaces between words
LEFTextracts a given number of characters from the left
LENcounts characters in a cell
MIDextracts from the middle given a starting point and the number of characters needed
RIGHTextracts a given number of characters from the right
TEXTconverts a value to text in a specific number format

For the complete lesson, see Excel Basics Lesson 5: Working with Text

Lesson 6

Time Value of Money using Excel Financial Functions

Time value of money (TVM) is the idea that money is worth more today than in the future. Time value of money is a basic concept in finance. Excel can calculate many TVM problems.

ConceptExcel FunctionDescription
Present ValuePVlump sum at the beginning of a problem
Future valueFVlump sum at the end of a problem
PaymentPMTannuity, stream of equal payments per period (+ or -)
Number of periodsNPERnumber of periods (months, quarters, half years, years)
Interest rateRATEinterest rate per period (annual interest rate / periods per year)

For the complete lesson, see Time Value of Money using Excel Financial Functions.

Lesson 7

Excel Lookup Functions

Excel can select a specific value from a long list of values. These are called Reference and Lookup functions. These functions allow the use of different input values based on criteria to produce an output.

Data is often stored in a list on a worksheet. A list that categorizes values is called a lookup table.The lookup and reference functions allow you to find a value based on the lookup table criteria.

The most important lookup functions are:

  1. VLOOKUP
  2. HLOOKUP
  3. XLOOKUP
  4. LOOKUP

For the complete lesson, see Excel Basics Lesson 7: Lookup and Reference Functions.

Lesson 8

Excel What If Analysis

What if analysis is the process of changing values to see how the changes affect the outcome of the formulas in a spreadsheet. This is a powerful data analysis feature of Excel.

The important What If Analysis tools in Excel are:

  1. Goal seek
  2. Data tables
  3. Solver
  4. Pivot tables
  5. Pivot charts

For the complete lesson, see Excel Basics Lesson 8: What If Analysis.

Lesson 9

Excel Data Analysis

Using Excel is a great way to start data analysis. Excel can handle large data sets with tools like pivot tables, pivot charts, and regression analysis.

For the complete lesson, see Excel Basics Lesson 9: Data Analysis.

Excel Resources

Excel Video Tutorials

Mr. Excel
Bill Jelen is Mr. Excel has been on Youtube for over 10 years and has thousands of great videos. This is a great resource with so many good teaching videos. 

Excel is Fun
Mike Girvin has over 3,000 videos on Youtube. He has over 86 million views. Wow. It is a great resource for financial and statistical help. I have learned so many things from this channel. 

Leila Gharani
Leila Gharani is my new favorite Excel channel. She is great especially for finance and data visualization. She is very helpful on creating amazing charts and her videos are high quality.

Excel Campus
This video channel is from Jon Acampora. He has a great video series on creating Excel dashboards with pivot tables, pivot charts, and slicers. He also has lots of information about VBA and macros in Excel.

Finally Learn
This is my video channel. I have videos on accounting, finance, and Excel.


Jeff Mankin

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

Recent Posts

link to Expense

Expense

What is an expense? Expense is a cost to operate a business to produce revenue. In accounting, an expense occurs when an asset is used. This could include a cash outflow or consuming an...