3 Classic Excel Tricks to Become an Efficient Analyst

Ram Dewani 15 May, 2020 • 6 min read

Overview

  • Learn 3 classic Excel tricks in this article that will impress your audience
  • Use these Excel tricks in your business analytics or data science role and become an efficient analyst

 

Introduction

Microsoft Excel is the gold standard in data analysis tools. There’s no question about it – industry experts, professionals and veterans still lean heavily on Excel’s prowess and Swiss Army Knife nature to slice and dice their data.

I still remember my initial days learning Excel – I was mesmerized by the incredible array of formulas and functions Excel offered. This remains unparalleled in the industry. No matter what task you’re working on, data manipulation, exploration, visualization, etc., Microsoft Excel seemingly has a solution for everything!

excel_tricks

Excel provides tons of applications and functionalities that we can use according to our own use case. But it can become overwhelming at times, especially if you’re new to the tool. Let me help you out! In this article, we will be discussing some crucial Excel tricks that will make you an efficient analyst, regardless of the domain you’re coming from.

And if you want to learn more about Excel formulas and functions, you should check out the free course that covers a plethora of functions, including VLookUp!

 

Table of Contents

  1. Excel Tips and Tricks #1: Print Titles
  2. Excel Tips and Tricks #2: Flash Fill
  3. Excel Tips and Tricks #3: Heatmap

 

Excel Tips and Tricks #1: Print Titles

Have you been in a situation where you want to print multiple pages in an Excel sheet? Then you must be familiar with this caveat – when you scroll beyond the first page, you’ll notice that the column headers are missing. This leads to flipping through pages continuously creating constant confusion.

excel print titles trick

In this handy Excel trick, I will take you step-by-step through the process or you can check out this video:

 

Step 1:  Go to the Page Layout

In the Home tab on the ribbon, click on Page Layout:

excel print titles trick

 

Step 2: Select the “Print Titles

Locate Print Titles and then click on it. Print titles will help you select rows and columns that you want to select as titles to print them on all printed pages:

excel print titles trick

Step 3: Page Setup

On clicking the “Page Title“,  a “page setup” box will open up. This setup has a lot of options that you may tweak to explore:

excel print titles trick

Step 4: Select the column headers

You’ll see a “Rows to repeat at top” field. Click the button in front of it and then select the row you want as the column header:

excel print titles trick

Step 5: Print your Excel worksheet

Finally, Go to Print or press “CTRL + P” and when you scroll to the second page, you’ll notice that you have the column headers!

excel print titles trick

 

Excel Tips and Tricks #2: Flash Fill

Flash Fill is a lifesaver when you are working on a large dataset, especially when you need to fill data that is already present in other columns!

Flash Fill automatically fills the data in your column when it senses a pattern.

Follow the video below or refer to the steps after that:

Here, we have 3 columns: Names, class, and Fees paid. Now, we want to add 2 more columns for first name and last name. We can manually input the data or split the Names columns but we will go with a much better option – Flash Fill.

Note: Flash fill is available in Excel 2013 or later versions.

Step 1: Make New columns

Enter the new columns – First_name, Second_name which will contain the first name and the last name of each student respectively.

excel flash fill trick

Step 2: Enter the name

In First_name, enter the name in the first cell:

excel flash fill trick

Step 3: Automatically fill the column

As you type the text in the next cell of First_name, flash fill automatically detects a pattern and presents you a preview. Just press Enter and voila, you have all the cells of the column filled up!

excel flash fill trick

Step 4: Repeat the above steps for the next column

We will repeat the same steps for the Second_name and automatically fill its content:

excel flash fill trick

Finally, our data looks like this:

excel flash fill trick

As seen in this example, Flash Fill works wonderfully when you need to input the data you already have. Do try this hack for yourself as it is a big time-saver!

 

Excel Tips and Tricks #3: Heatmap

The world of data is expanding at an exponential rate. It is practically impossible to understand the nature of our data in one glance. As I said earlier, Excel is a wonderful data analysis tool and it provides plenty of options to choose from.

One such option is the famous heatmap. It is particularly useful when you want to create a quick comparative view of the data in visual form. This saves you from scrolling and crunching numbers in your mind, thus leading to confusion.

You can choose from ample colour scale options provided by Excel to suit your requirements.

Let us get down to action and see how can we create a heatmap in Excel!

Here’s a sample data of a company containing monthly sales numbers of 7 unique products. Are you able to make sense out of it in the first glance?

excel heatmaps trick

I know many of you didn’t even try as this a daunting task! So let us see how to make a heatmap in Excel and quickly unearth insights from this kind of data.

You can either watch the below video or follow the steps I have mentioned after this:

 

Step 1: Select the data for heatmap

Select the data for which you want to visualize the heatmap:

excel heatmaps trick

Step 2: Go to Conditional Formating

In the ribbon, go to Home -> Conditional Formatting:

excel heatmaps trick

Step 3: Select a desirable Colour Scale

Once you get into the conditional formatting dropdown, hover on Colour Scales. Here, you will see a bunch of different colour scales spanning from red to green to blue.

To get a preview of the different heatmaps, you can take your mouse and hover over the different colour scales. Select the option you find most suitable for your use case:

excel heatmaps trick

excel heatmaps trick

Step 4: Analyse your data

The final step is to visually understand the data and form your logic. In this case, I chose a Green – Yellow – Red colour scale. The green coloured cells show good performance of a product in terms of sales and red coloured cells show poor sales performance of the product.

excel heatmaps trick

Heatmap is a great way to visualize your data according to conditions. Another great thing about these heatmaps is that they are dynamic. Even if you change the data for any cell value, the whole heatmap will change and adapt itself to the change in the order of value.

 

End Notes

In this article, we covered 3 classic Excel tricks to become an efficient analyst. I hope these tricks will help you with day-to-day niche tasks and save you a lot of time.

You can also check out and enroll in the free Excel Formulas and Functions course to enhance your learning and take a big step towards becoming a better analyst, regardless of your domain.

Let me know your Excel hacks, tips, and tricks in the comments section below!

Ram Dewani 15 May 2020

Frequently Asked Questions

Lorem ipsum dolor sit amet, consectetur adipiscing elit,

Responses From Readers

Clear

Amit Mittal
Amit Mittal 15 May, 2020

It's good to know a few new tricks in excel. Your videos are small and easy to understand, that's a great point. Keep up the momentum man.

Adeoba
Adeoba 16 May, 2020

I sincerely appreciate this videos. I'll appreciate other offers that can be useful for business intelligence. Thank you.

A_RP
A_RP 16 May, 2020

Great info for newbies. The free course link doesn’t work.

Shaban Farjad
Shaban Farjad 16 May, 2020

Thanks for technic in excel very useful ,I am interested mor know about the other technic in excel I hope you succeed Shaban Farjad

Prasoon Khare
Prasoon Khare 16 May, 2020

There excel tricks are real time saver. Thanks for sharing and I will further share your link with my friends and colleagues (....way I got it !!). Step by step explanation and quick short videos are very helpful to understand. Nice work and best of luck!

Awais Sami
Awais Sami 16 May, 2020

It was easy to understand & learn. Now what's next. Looking forward to learn more tricks.

Wasantha Halpathotage
Wasantha Halpathotage 16 May, 2020

Thank you so much for very useful excel tricks. This will be definitely enhance our analytical skill and will save our time.

Gerald Hall
Gerald Hall 17 May, 2020

Very good indeed.

Pankaj Kalra
Pankaj Kalra 24 May, 2020

Mr. Ram diwani & Analytics Vidya: Its really helpful, very good job done by you

Caroline Chapman
Caroline Chapman 30 May, 2020

I like the clear and concise explanation and happy to pick up new tricks in excel. I love excel, self taught by Mrs Google.

Rajeev
Rajeev 02 Jan, 2021

Very useful tips to have insights on numbers.

Irina
Irina 02 Feb, 2022

Thank you for your tricks, all at tiles are very short and easy to understand with small videos it’s really awesome!

  • [tta_listen_btn class="listen"]