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.

Quick Analysis
Power Pivot
What-if Analysis
3D References
Forecast sheet
Filled maps
Sumproduct
Xlookup
Ideas
Natural Language Query
Remove Blanks
IFERROR Function
Filter Function
Remove Duplicates
Data Types
Slicers
COUNTBLANK
COUNTA
3D Maps
Analysis ToolPak
Get Data from Web
Sort Function
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!

If you’re looking to deepen your Excel skills or start from scratch, our free online Excel course are a great resource to help you master the basics and advance your proficiency.

Upgrade from Excel to Power BI: Excel is great, but if you’re ready for more advanced analytics and visualization, check out our free Power BI course. It’s a perfect follow-up to your Excel knowledge, helping you visualize data like a pro!

Contributed by: Priya