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!
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.
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.
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.
Excel Statistical Functions
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.
|Mean||arithmetic average of a set of numbers||AVERAGE|
|Median||value that occurs in the middle of a data set when arranged from lowest to highest, half the values above and half below the median value||MEDIAN|
|Mode||value that occurs most frequently in a data set||MODE.MULT, MODE.SNGL|
|Standard Deviation||measure of how widely the data values are dispersed from the arithmetic mean||STDEV.P, STDEV.S|
There are many Excel functions for statistical analysis.
Basic Statistical Functions
|AVERAGEIFS||average based on a logical test (replaces AVERAGEIF)|
|COUNTIFS||count based on a logical test (replaces COUNTIF)|
|LARGE||nth largest value|
|MEDIAN||median or the middle value in a set|
|MODE.SNGL||mode or most frequent number|
|RAND||random number from 0 to 1|
|RANDBETWEEN||random number between numbers specified|
|RANK.AVG||rank in a list of numbers, any ties are averaged|
|RANK.EQ||rank in a list of numbers, ties are shown as first number|
|SMALL||nth smallest value|
|STDEV.P||standard deviation of a population|
|STDEV.S||standard deviation of a sample|
|SUMIFS||sum based on a logical test (replaces SUMIF)|
For the complete lesson, see Excel Basics Lesson 2: Statistical Analysis.
Excel Graphs and Charts
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.
|Area||trends over time or by category|
|Bubble||three values, the third value graphed as the size of a bubble|
|Combo||combines two or more chart types|
|Line||trends over time or by category|
|Pie||components of a total amount|
|Stock||stock 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.
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
The following table shows all the Excel logical functions.
|AND||returns TRUE only if all arguments are TRUE|
|FALSE||returns the logical value FALSE|
|IF||checks whether a condition is met, returns one value if TRUE and another value if FALSE|
|IFERROR||returns a specified value if expression is an error and the function value otherwise|
|IFNA||returns a specified value if the expression resolves to #N/A, and the function value otherwise|
|IFS||runs multiple tests and returns a value corresponding to the first TRUE result|
|NOT||changes TRUE to FALSE, or FALSE to TRUE|
|OR||returns TRUE if any arguments are TRUE|
|SWITCH||returns a result on the first matching value|
|TRUE||returns the logical value TRUE|
|XOR||returns a logical “exclusive or” of all arguments|
For the complete lesson, see Excel Basics Lesson 4: Using Logic in Decision Making.
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.
|CLEAN||removes all non printable characters|
|TRIM||removes spaces from a text string except for single spaces between words|
|LEFT||extracts a given number of characters from the left|
|LEN||counts characters in a cell|
|MID||extracts from the middle given a starting point and the number of characters needed|
|RIGHT||extracts a given number of characters from the right|
|TEXT||converts a value to text in a specific number format|
For the complete lesson, see Excel Basics Lesson 5: Working with Text
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.
|Present Value||PV||lump sum at the beginning of a problem|
|Future value||FV||lump sum at the end of a problem|
|Payment||PMT||annuity, stream of equal payments per period (+ or -)|
|Number of periods||NPER||number of periods (months, quarters, half years, years)|
|Interest rate||RATE||interest rate per period (annual interest rate / periods per year)|
For the complete lesson, see Time Value of Money using Excel Financial Functions.
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:
For the complete lesson, see Excel Basics Lesson 7: Lookup and Reference Functions.
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:
- Goal seek
- Data tables
- Pivot tables
- Pivot charts
For the complete lesson, see Excel Basics Lesson 8: What If Analysis.
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 Video Tutorials
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 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.
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.
This is my video channel. I have videos on accounting, finance, and Excel.
Excel Basics Lessons
We have a full set of Excel basics lessons. For all the lessons, see The Ultimate Guide to Finally Learn Excel.
Here are the Excel lessons: