Excel Tutorial | Everything you need to know about Excel

In this Excel Tutorial, let us see some of the key features of Excel 2020 pertaining to Data Analysis. So let’s get started with the Excel Tutorial.

1. Quick Analysis
2. Power Pivot
3. What-if Analysis
4. 3D References
5. Forecast sheet
6. Filled maps
7. Sumproduct
8. Xlookup
9. Ideas
10. Natural Language Query
11. Remove Blanks
12. IFERROR Function
13. Filter Function
14. Remove Duplicates
15. Data Types
16. Slicers
17. COUNTBLANK
18. COUNTA
19. 3D Maps
20. Analysis ToolPak
21. Get Data from Web
22. Sort Function
23. Unique Function

1. Quick Analysis

Whenever you highlight/select any table in Excel there is something called Quick Analysis that appears in the bottom right corner of the selection. Let us deep dive into the features Excel offers to make our analysis easy and quick. 

Features: 

Formatting: It uses rules to highlight interesting data

• Data Bars
• Coloscale
• Iconset
• Greater than
• Top 10%
• Clear Format

Charts: It helps us to visualize data with certain predefined charts easily.

• Clustered Column
• Line
• Stacked Column
• Stacked Bar

Totals: These predefined formulas automatically calculates total for you 

• Sum (Columnwise)
• Average
• Count
• %Total
• Running Total
• Sum(Rowwise)

Tables:  Helps you sort, filter and summarise Data

• Table (Representation)
• Some Recommended Pivot tables

Sparklines: These are mini charts placed in single cells, each representing a row of data in your selection.

• Line
• Column
• Win/Loss

These options avoid us to go through the menu and manually insert each one of these different options during Data Analysis. 

2. Power Pivot

This feature makes Excel a BI tool.

Go to File->Option->Add-ins->.COM Add-ins->Go->Enable Microsoft Power Pivot. 

You can see Power Pivot gets added on the Menu Bar. 

It allows you to connect Oracle, SQL, and other external data sources, so that we can use it for large sets of data (Expands its Capability to handle millions of rows). It turns Excel into a Business Intelligence Analysis tool.

3. What-if Analysis 

Consider the table below,

Items	Quantity	Each Price	Total Sales (in INR)
1	50	5	250
2	40	6	240
3	10	10	100
			590

Scenario: To increase the sales to 2000 Rupees, how many item3 should be sold can be achieved by What-if-Analysis (Data->What -if Analysis-> Goal-Seek)

We get this kind of result in fraction of seconds

4. 3D References

When you are tracking similar kinds of data for a period of time (Ex: Monthly sales/Budget etc) where you wish to make similar kinds of changes like Add Heading, Change Column name, Find Total Sum across sheets etc, you can very easily achieve this using 3D References. All that you need to do is, Hold on Shift key and select all the sheets in which you expect change to be reflected. Make change on one sheet that automatically gets reflected in other sheets as well.

Scenario: When you wish to calculate sum of Units column across all 3 months for each product. 

Create a Sheet Total (move or copy either of the existing month sheets and rename it as total)

Select corresponding  cell in Jan, Hold shift and select another 2 months as well (Feb,March) and Click enter. This will sum units across all 3 months for each product.

5. Forecast sheet

Select the data for which you need the forecast.

Click on Data>Forecast>Forecast sheet. You can get a line forecast as shown below,

6. Filled maps

When you have Zip code/Location specific information and wish to represent that in a Geographical map.

Select the data> Go to Insert> Click Maps>Filled Maps

7. Sumproduct

SUMPRODUCT multiples one range of values by its corresponding row counterparts. 

It is extremely helpful when we want to know Total Sales,  average returns, price points, and margins

8. Xlookup

It replaces both vlookup and hlookup. It can look for and return values both horizontally and vertically. 

Vertical lookup: 

Horizontal lookup: 

9. Ideas

This feature is extremely helpful to generate Popular Pre-defined Charts (Ex: Units by ‘Product’).

10. Natural Language Query

Ideas>Ask a Question you wish to know about data

11. Remove Blanks

Select Data>Go to Home>Find&Select>Go to Special>Enable Blank.  This will highlight the blanks in the selection. 

Click delete on any of the blanks. Click on Shift left. This will help us to get rid of blanks in between rows.

12. IFERROR Function

Whenever you encounter errors while applying formulas you can always give a default value if the execution of the formula fails. This can be achieved by IFERROR() function.

Without IFERROR Function, you get to see something like this, ( On trying to divide by 0)

Using IFERROR Function in such scenarios,

• Quick Tip: See all formulas->  Ctrl+~ helps to see the formula applied throughout the sheet.
• Quick Tip: Hide cell-> Select Cell>Format>Number>Custom>Enter ;;; This will hide the cell but the entered value in the cell is still considered for any formula which uses it.

13. Filter Function

It is a powerful new dynamic array function that solves issues of most of the lookup functions of previous versions.

14. Remove Duplicates

Selecting all 3 columns to remove Duplicates

Selecting only the first 2 column to remove Duplicates: 

Quick Tip: Flash Fill- It automatically detects the pattern of  entering the data in the first row and applies the same on other rows. 

Keyboard Shortcut: Ctrl+E

How to enable Flash Fill?

15. Data Types

On selecting the data and clicking Geography as shown above , Excel gives us an option to extract other related data about that state ( includes Population, Persons per household, largest city etc- shown below).

Creating Reference,

16. Slicers

It helps us to slice out subset of data.

17. COUNTBLANK

It gives count of Blank on the selected range.

18. COUNTA

It gives count of unique values on the selected range.

19. 3D Maps

We have option to create a virtual tour,

20. Analysis ToolPak

This comes as add-ins in Excel.

It adds Data Analysis Tab under Analyze Tab (Shown below)

On Clicking Data Analysis  we get to see many options (Shown below) which we can make use pertaining to our analysis.

21. Get Data from Web

Data got imported to Excel now,

Click Refresh under External Table Data 

Enable Refresh data when opening the file to get live data from Web

22. Sort Function

• Quick Tip: These Dynamic array functions are available only in the 2020 version. So this may not work when the sheet is opened in a lower version. So, BE CAREFUL on that.

23. Unique Function

TRUE>Return items that appear exactly once.

FALSE>Return every Distinct item

These are simple yet powerful features in Excel 2020 which makes ones’ data analysis quicker and easier with just a few clicks like other BI tools. This brings us to the end of the Excel Tutorial. We hope that you learnt more about Excel and how powerful it can be with the help of this Excel Tutorial. You can learn more by joining Great Learning Academy’s free online courses!

Contributed by: Priya