10+ Simple Yet Powerful Excel Tricks for Data Analysis

Sunil Ray 28 Apr, 2020 • 7 min read

Overview

  • Microsoft Excel is one of the most widely used tools for data analysis
  • Learn the essential Excel functions used to analyze data for business analytics
  • Data Analysis with Excel serves as a precursor to Data Science with R or Python

*This article was originally published in 2015 and updated in April 2020.

 

Introduction

I’ve always admired the immense power of Excel. This software is not only capable of doing basic data computations, but you can also perform data analysis using it. It is widely used for many purposes including the likes of financial modeling and business planning. It can become a good stepping stone for people who are new to the world of business analytics.

Even before learning R or Python, it is advisable to have knowledge of Excel. It does no harm to add Excel to your skillset. Excel, with its wide range of functions, visualizations, and arrays empowers you to quickly generate insights from data which would be hard to see otherwise. And this is a crucial aspect of any business analytics project.

In fact, we have designed an entire comprehensive program on Business Analytics for you, with Excel as a key component! Make sure you check it out and give yourself the gift of a business analytics career.

I feel fortunate that my journey started with Excel. Over the years, I’ve learned many tricks to work to deal with data faster than ever. Excel has numerous functions. It becomes confusing at times to choose the best one.

In this article, I’ll provide you some tips and tricks to work on Excel and save you time. This article is best suited to people keen to upgrade their data analysis skills.

excel, data analysis

 

Commonly used functions

1. Vlookup(): It helps to search a value in a table and returns a corresponding value. Let’s look at the table below (Policy and Customer). In Policy table, we want to map city name from the customer tables based on common key “Customer id”. Here, function vlookup() would help to perform this task.excel, data analysis, lookup

Syntax: =VLOOKUP(Key to lookup, Source_table, column of source table, are you ok with relative match?)

For the above problem, we can write the formula in cell “F4” as =VLOOKUP(B4, $H$4:$L$15, 5, 0) and this will return the city name for all the Customer id 1 and post that copy this formula for all Customer ids.

Tip: Do not forget to lock the range of the second table using a “$” sign – a common error when copying this formula down. This is known as relative referencing.

 

2. CONCATENATE():  It is very useful to combine text from two or more cells into one cell. For example, we want to create a URL based on the input of hostname and request path.excel, data analysis, concatenate

Syntax: =Concatenate(Text1, Text2,.....Textn)

Above problem can be solved using formula, =concatenate(B3, C3) and copy it.

Tip: I prefer using the “&” symbol, because it is shorter than typing a full “concatenate” formula, and does the exact same thing. The formula can be written as  “= B3&C3”.

 

3. LEN() – This function tells you about the length of a cell i.e. number of characters including spaces and special characters.

Syntax: =Len(Text)

Example: =Len(B3) = 23

 

4. LOWER(), UPPER() and PROPER() –These three functions help to change the text to lower, upper, and sentence case respectively (First letter of each word capital).

Syntax: =Upper(Text)/ Lower(Text) / Proper(Text)

In a data analysis project, these are helpful in converting classes of a different case to a single case else these are considered as different classes of the given feature. Look at the below snapshot, column A has five classes (labels) whereas Column B has only two because we have converted the content to lower case.
excel, data analysis

5. TRIM(): This is a handy function used to clean text that has leading and trailing white space. Often when you get a dump of data from a database the text you’re dealing with is padded with blanks. And if you don’t deal with them, they are also treated as unique entries in a list, which is certainly not helpful.

Syntax: =Trim(Text)

6. IF(): I find it one of the most useful functions in excel. It lets you use conditional formulas that calculate one way when a certain thing is true and another way when false. For example, you want to mark each sales as “High” and “Low”. If sales are greater than or equals to $5000 then “High” else “Low”.

Syntax: =IF(condition, True Statement, False Statement)
excel, data analysis, conditional

 

Generating inference from Data

1. Pivot Table: Whenever you are working with company data, you seek answers for questions like “How much revenue is contributed by branches of North region?” or “What was the average number of customers for product A?” and many others.

Excel’s PivotTable helps you to answer these questions effortlessly. A pivot table is a summary table that lets you count, average, sum, and perform other calculations according to the reference feature you have selected i.e.  It converts a data table to an inference table which helps us to make decisions. Look at the below snapshot:
excel, data analysis, PivotAbove, you can see that table on the left has sales detail against each customer with the region and product mapping. In the table to the right, we have summarized the information at region level which now helps us to generate an inference that the South region has the highest sales.

Methods to create Pivot table:
Step-1: Click somewhere in the list of data. Choose the Insert tab, and click PivotTable. Excel will automatically select the area containing data, including the headings. If it does not select the area correctly, drag over the area to select it manually. Placing the PivotTable on a new sheet is best, so click New Worksheet for the location and then click OKexcel pivotStep-2: Now, you can see the PivotTable Field List panel, which contains the fields from your list; all you need to do is to arrange them in the boxes at the foot of the panel. Once you have done that, the diagram on the left becomes your PivotTable.
excel pivotAbove, you can see that we have arranged “Region” in row, “Product id” in column and sum of “Premium” is taken as value. Now you are ready with pivot table which shows Region and Product wise sum of premium. You can also use count, average, min, max and other summary metrics.

 

2. Creating Charts: Building a chart/ graph in excel requires nothing more than selecting the range of data you wish to chart and press F11. This will create an Excel chart in default chart style but you can change it by selecting different chart style. If you prefer the chart to be on the same worksheet as the data, instead of pressing F11, press ALT + F1.

Of course, in either case, once you have created the chart, you can customize to your particular needs to communicate your desired message.excel charts

Data Cleaning

1. Remove duplicate values: Excel has inbuilt feature to remove duplicate values from a table. It removes the duplicate values from given table based on selected columns i.e. if you have selected two columns then it searches for duplicate value having same combination of both columns data.
excel, remove duplicates
Above, you can see that A001 and A002 have duplicate value but if we select both columns “ID” and “Name” then we have only one duplicate value (A002, 2).
Follow the these steps to remove duplicate values: Select data –> Go to Data ribbon –> Remove Duplicatesremove duplicates

2. Text to Columns: Let’s say you have data stored in the column as shown in below snapshot.
data formatting, text to columnsAbove, you can see that values are separated by semicolon “;”. Now to split these values in a different column, I will recommend to use the “Text to Columns” feature in excel. Follow the below steps to convert it to different columns:

  1. Select the range A1:A6
  2. Go to “Data” ribbon –> “Text to Columns”
    data formatting, text to columnsAbove, we have two options “Delimited” and “Fixed width”. I have selected delimited because the values are separated by a delimiter(;). If we would be interested to split data based on the width such as the first four character to the first column, 5 to 10th character to the second column, then we would choose Fixed width.
  3. Click on Next –>Mark checkbox on for “Semicolon” then Next and finish.
    data formatting, text to columns

Essential keyboard shortcuts

Keyboard shortcuts are the best way to navigate cells or enter formulas more quickly. We’ve listed our favorites below.

  1. Ctrl +[Down|Up Arrow]: Moves to the top or bottom cell of the current column and combination of Ctrl with Left|Right Arrow key, moves to the cell furthest left or right in the current row
  2. Ctrl + Shift + Down/Up Arrow: Selects all the cells above or below the current cell
  3. Ctrl+ Home: Navigates to cell A1
  4. Ctrl+End: Navigates to the last cell that contains data
  5. Alt+F1: Creates a chart based on selected data set.
  6. Ctrl+Shift+L: Activate auto filter to data table
  7. Alt+Down Arrow: To open the drop-down menu of auto filter
  8. Alt+D+S: To sort the data set
  9. Ctrl+O: Open a new workbook
  10. Ctrl+N: Create a new workbook
  11. F4: Select the range and press F4 key, it will change the reference to absolute, mixed and relative.

Note: This isn’t an exhaustive list. Feel free to share your favorite keyboard shortcuts in Excel in the comments section below. Literally, I do 80% of excel tasks using shortcuts.

 

End Notes

Excel is arguably one of the best tools ever made, and it has remained the gold standard for nearly all businesses worldwide. But whether you’re a newbie or a power user, there’s always something left to learn. Or do you think you’ve seen it all and done it all? Let us know what we’ve missed in the comments.

Looking to get started in the data field? We have curated the perfect multi-course program for you! Check out the Certified Business Analytics Program and launch your career!

Sunil Ray 28 Apr 2020

I am a Business Analytics and Intelligence professional with deep experience in the Indian Insurance industry. I have worked for various multi-national Insurance companies in last 7 years.

Frequently Asked Questions

Lorem ipsum dolor sit amet, consectetur adipiscing elit,

Responses From Readers

Clear

Abdel
Abdel 03 Nov, 2015

The article is very good, maybe you can add SUMIF, SUMIFS and MATCH which I use a lot.

Anupam Basu
Anupam Basu 03 Nov, 2015

I really admire the topics related to analytics spoken in such forums. And no one does it better than Analytics Vidhya. I have been involved in interviews over the last 5 months or so (all Data Science/Analytics positions, both operational & leadership). I was amazed at the number of times anything to do with Excel was mentioned or asked. Literally 0 (I'm not joking). Excel is a very powerful tool according to me too. Whenever I start doing any analytics I first start off with Excel. It is amazing how just by playing around with a pivot & a chart it's thought provoking. Great job people. Keep up the good work!

Ritik
Ritik 03 Nov, 2015

Also Can you plz tell us the Analytics tools plugin in excel and some of its features

Steven Rutt
Steven Rutt 03 Nov, 2015

One of the other great things about it Excel is all of the Power BI tools they have been adding. Extracting and cleaning data is much easier and it is much easier to work with large data sets.

ritesh
ritesh 03 Nov, 2015

Good article to cover the basic useful excel functionalities. If possible please try to cover some advanced data analysis features in excel, like dashboard preparation, data analysis functions etc. Thanks a lot for sharing.

Murali
Murali 03 Nov, 2015

My favorites. 1. Select data and Alt + E + S [Paste Special] followed by V [only values] or E [Transpose] and many more. 2. To freeze panes: Alt + W + F + F 3. To Clear data: Alt + H + E + A (Select data before using it) 4. Shift between sheets: Ctrl + PgUp/Pg Dn 5. Border: Alt + H + B + A (Select data before using it) 6. Finally Alt + N + N for line graph (Select data before using it)

Hunaidkhan
Hunaidkhan 03 Nov, 2015

When data is Filtered -> Alt + Down + right key + E will allow you to text search the filtered data.( Very useful ) Alt + A + M is shortcut to remove Duplicates, Index and Match is better than Vlookup while searching data in a sheet. Alt + H + V + V is the shortcut to paste special which is very useful to do the analysis faster. Alt + A + T or Alt + D+ F +F is also shortcut to filter the data . i do have many more useful shortcuts which i use for my everyday analysis

Shashi
Shashi 04 Nov, 2015

Excel 2016 in the game now, would change the equation in the analytics industry. Plethora of chart types more features are spicing it up...When it comes to minute customization of data presentation Excel is much better than Tableau/Qlikview. Only pain with excel is as Sunil said handling of data size...MS should seriously think of it.. Can MS build excel based on In-Memory technology for the lightning speed performance?

Achin
Achin 18 Nov, 2015

One function which stand out and have used a lot by me is Vlookup. Absolute fan of this function. Data analysis, reconciliation activities involving financial data. Well this article is nice in a way that it provided range of Excel's capabilities. Well VBA programmers do extend it .

Devaraj
Devaraj 02 Dec, 2015

Awesome. .Thankyou for explaining in such a detail.

manoj
manoj 12 Apr, 2017

alt+E+S+V also use to paste special

Sanjay Tyagi
Sanjay Tyagi 25 May, 2017

VLOOKUP in conjunction with MATCH function helps in dynamic search if headers in both the source table and destination table are the same. Also, INDEX is better to VLOOKUP function since it helps in search data in left side of the lookup value in source data which VLOOKUP cannot.

Mehjabeen Malik
Mehjabeen Malik 02 Mar, 2018

Very Useful article.

timo korhonen
timo korhonen 26 Apr, 2020

Interesting and well-addressed points in Excel, it is very versatile indeed. If, however, Excel is compared to Matlab it is much more tricky and limited to apply for complex tasks. There is a learning curve, much similar to Microsoft Access. The latest version of Matlab supports also huge tables. Cloud-based parallel processing using for instance Amazon mainframes is also supported that is useful for more extensive works, that can be required in various AI, or deep learning apps. Also, stand-alone app-creation based on your Matlab code is supported. Price tag is anyhow still an issue. R may then be worth considering too.

Sagar
Sagar 26 Apr, 2020

Nice article @sunil. I have tried operations you mentioned in the article on LibreOffice spreadsheet instead of MS Excel and they worked. Of course there are very minor differences in getting rid of duplicates(I have to use Standard Filter for this.). Thanks.

Adedeji Hammed Adewale
Adedeji Hammed Adewale 27 Apr, 2020

Analyst Adedeji Hammed Adewale a.k.a. Analyst Capable from Nigeria say kudos to you all for adding to my excel knowledge but pls I need more assistance in in Excel VBA My email address is [email protected] Thanks, I appreciate.

Fabio Valeri
Fabio Valeri 27 Apr, 2020

Thanks for the shortcuts. Indeed, Excel is a useful tool for a preliminary analysis as Anupam Basu mentioned. I use it also to create prototype of, for example, dashboards. But there is a but: if people who have no knowledge of a program language, for example medical students, has to process and analyse data they often use Excel (filtering, column bind(!)) and the step to use R or SQL make it difficult since it seems so easy to do that in Excel. Processing data with Excel (not with VBA) is dangerous. My experience is that if I urge the students to use R, they will enjoy it and only then they realize how dangerous Excel may be.

Joseph
Joseph 26 Aug, 2020

Its a good article, easy to follow and practice the commands.

Rishav Ganguly
Rishav Ganguly 16 Sep, 2020

I use Ctrl+D to fill a column which has a formula typed in its first cell/row(provided the column in consideration is selected first). Also, to select such a column(which contains a formula in its first cell/row) without using mouse, use the following steps: i) find a non-null column, next to the column containing the formula in its first cell. ii) hit Ctrl+down to reach the last cell(last row) of that non-null column. iii) press right/left to come back to the last cell(last row) of the column containing the formula. iv) now hit Ctrl+Shift+Up and the said column will be selected all the way up to the first cell which contains the formula.(Now just press that Ctrl+D to see the magic) Hope this helps!

Galaxy Eduaction
Galaxy Eduaction 15 Feb, 2022

your article is great thanks for sharing.

Henry Kevin
Henry Kevin 22 Jul, 2022

Thank you for this informative and beneficial blog! I became stressed with something however now my thoughts is clear.

Data Visualization
Become a full stack data scientist