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.
Contributed by: Priya
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.
Formatting: It uses rules to highlight interesting data
- Data Bars
- Greater than
- Top 10%
- Clear Format
Charts: It helps us to visualize data with certain predefined charts easily.
- Clustered Column
- Stacked Column
- Stacked Bar
Totals: These predefined formulas automatically calculates total for you
- Sum (Columnwise)
- Running Total
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.
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)|
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
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
It replaces both vlookup and hlookup. It can look for and return values both horizontally and vertically.
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).
It helps us to slice out subset of data.
It gives count of Blank on the selected range.
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!0