## 25 Amazing Excel Tips For Massive Productivity Gains

Most people use Microsoft Excel to Organise and Visualize large amounts of Data & Save Time by Getting Complex Tasks Done Faster.

Yes, I find people using long and cumbersome methods to do simple tasks, which makes them much longer to get things done. They are frustrated but think that there is no other way.

Actually, you can improve your productivity by learning some simple tips and tricks, shortcuts, formulas that will save you countless hours.

Also, learn to Analyze Data Quickly with Excel here.

Here’s my collection of the Best & Most Amazing Excel Tips & Tricks that I have compiled for you. I am sure you will learn something new, and gain from these amazing tips that will give you massive productivity gains.

## List of Amazing Excel Tips For You

2. Begin Using Range Names & Get Rid of Absolute Referencing
3. Master IF Function For Quick Decision Making
4. Conditionally SUM a few rows only, based on a criterion with SUMIF
5. Count only a few cells, based on any logic you can think of, by using the COUNTIF function
6. Go Beyond Formatting Tables to Actually using them For amazing Insights
7. Stop using Average. Use the Median Function to get a better sense of your data.
8. Use Special Data Types built into Excel to get Demographic, Weather, and other useful information
9. Learn to Combine Cells to Make Better Looking Reports
10. The Amazing Flash Fill can Do wonders to save you time
11. Learn some Date Magic in Excel
12. Find the difference between any 2 dates, with or without counting the Weekends – your choice!
13. Convert any data from Centigrade to Fahrenheit, or centimetres to inches. There are hundreds of free conversions built into Excel for you.
14. Master Pivot Tables To Analyze Any Data within minutes. Also, check out our Free MasterCourse on Analyzing Data With Pivot Tables on YouTube
15. Create Self Updating Pivot Charts
16. Create Beautiful Printouts & Reports With Beautiful Printing Options within Excel
17. Move around easily by Freezing or Splitting Sheets and Views easily
18. Don’t have time to analyze data? Can’t figure out where to start? Too much data? Fret not. Get Free Analysis Ideas done for you in a jiffy. And for more Advanced Data Analysis, use the Analysis Toolpak.
19. Hide or Show Grid Lines to make Excel look like Word or PowerPoint. Plus it makes better output and a pleasing view too.
20. Setup Links to other sheets or other workbooks with this simple hyperlinking technique and never have to search for your sheets. It is best is to create a Table of Contents with this technique.
21. Use Color To Enhance your experience working with multiple sheets, and create a livelier Workbook that looks amazing.
22. Before you analyze anything, remember to Remove Duplicates in a single click
23. Never get invalid or wrong data in your data. Setup Drop Down Lists so that all data entry is done to conform to the valid values only. Saves countless hours fixing data quality issues. Nip the evil in the bud with proper data validation with drop-down lists.
24. Moving around? Don’t have your own laptop? Don’t want to pay for an Excel license? No worries. You can use Excel For Free on the web. All you need is an Internet account and a free Microsoft account.
25. Got a colleague or Team to Brainstorm ideas? Now you can all work on the same Excel file at the same time with Collaboration built right into Microsoft Excel.
Do watch this video a couple of times to make sure you get all the points clearly and master them. Also, don’t forget to like this Amazing Tips & Trick in Excel video, and subscribe to our channel on Youtube by clicking here.

## Master These Excel Lookup Functions To Save Time

One of the most popular Excel Lookup functions is VLOOKUP. Another Excel function to check out is the HLOOKUP function, which can do a horizontal lookup.

The newly added XLOOKUP is becoming very popular too. (The XLOOKUP function is currently only available in Office 365 versions).

For the advanced & power users of Excel, the mastery of Excel lookup functions like INDEX, MATCH & OFFSET can be considered vital. These functions are considered the “advanced” lookup functions in Excel.

With the help of these functions, you will be able to analyze data quickly. To learn more, you should enrol in the data analysis and interpretation training class

But with the introduction of XLOOKUP, some of the jugglery created by mixing INDEX & MATCH combination is no longer required.

## VLOOKUP Function of Excel

The most MUST HAVE Function ever. Even Excel gurus can’t live without it. I polled a group of Excel experts recently, asking if Excel’s VLOOKUP was overrated. I got a severe backlash for even mentioning it.

Almost everyone said that it is their GO TO function, an absolute must-have and that Excel won’t be that useable if this VLOOKUP function was taken away from Excel!

Most people swear by their VLOOKUP functions. It is their GO TO function when they want to lookup value of any type.

According to legend, VLOOKUP mastery is what separates the Pro Excel users from the Amateurs!

Vlookup is akin to using a dictionary. You know the word, and you want to find out the meaning. This dictionary is the range of cells that contain the lookup up value, and its associated value. The V in VLOOKUP stands for the dictionary data array being a vertical dictionary. So for a vertical lookup you must use VLOOKUP function only.

=VLOOKUP(word, dictionary, column number of meaning, exact_match_ype)

In the VLOOKUP formula, the first column in the dictionary must contain the lookup up value, and the first row should be of the data. You should not include the headings in the dictionary table. The difficulty most people have with VLOOKUP is the last flag – the logical value of TRUE or FALSE (You can use 1 for True and 0 to indicate the False flag).

Once a matching value is found out, you will be able to get the return value based on the search. The error value of N/A will be generated if there is no exact match until the last row or the last column of the array.

The mystery is created because to use VLOOKUP for an exact match, you have to specify the last optional flag, and set its value to a FALSE or a 0. By default, it is set to 1, which is useful for an approximate match type only. So for an exact match of a specific value, the last parameter is not really optional… it is mandatory.

Practical Examples of VLOOKUP:

= VLOOKUP(50, age_income, 2) will find the income from the second column of the age_income range for the age value of 50. Note that this is set as an approximate match lookup, as the last optional value is not specified.

=VLOOKUP(A5, \$K\$10:\$L\$100, 2, FALSE)

In this VLOOKUP example, we are looking for a piece of information from the column of data in the range \$K\$10 to \$L\$100. The search value is in cell A5, and the particular value of interest is in the lookup array that is set as an absolute reference.

The above example uses the reference functions that will generate an Exact match. If the correct value is not found, an error value of N/A will be received.

There are two major shortcomings in using the VLookup function of Excel.

• First of all, the VLOOKUP is really a slow function. It is obvious when you do a lookup on a large list of 100,000 values or more.
• Secondly, VLOOKUP can only look up a corresponding value from the columns on the right of the looked-up value. It can’t look to the left!

Make sure you master this Excel function really well.

## HLOOKUP Function in Excel

An often forgotten cousin of the VLOOKUP formula, this Horizontal Lookup and Reference function in Excel works in a similar way too. The only difference is that in this case, a lookup dictionary is a horizontal dictionary of columns, denoted by the H.

HLOOKUP is most used in range lookups, rather than exact matches, as columns are not the best suited for exact values, because of their limit of only16,000 columns. Whereas a list can grow vertically to over a million records easily.

In the following formula, this lookup function searches for the closest match, especially when we are not searching for an exact match, but an approximate match.

The dictionary is the table array and it is recommended that we use the absolute reference to lock the cells from moving.

=HLOOKUP(A5, \$G\$2:\$K\$100, 2)

Here the HLOOKUP will search for the exact or the next smallest value in the lookup table source data range of \$G\$2 to \$K\$100, and return the second row. If you want the third row, you can change the 2 into a 3.

Both VLOOKUP & HLOOKUP return single-cell values from a single row or a single column.

## XLOOKUP Function in Excel

Did you know that new functions are added to Excel quite frequently, and these are extremely useful functions making approximate matches as well as exact matches?

Finally, after years of backlash at Microsoft for creating the mess with the Match Type (True and False) in VLOOKUP, they got rid of it completely in the Excel XLOOKUP function.

By default, XLOOKUP is set to do an exact match.

XLOOKUP requires a deeper understanding of the various scenarios it can handle. I’d recommend a dedicated video tutorial on XLOOKUP by Excel MVP Chandoo to learn how to use XLOOKUP in Excel.

This new XLOOKUP function of Excel is only available from Microsoft Office 365 users. It does not work on Excel 2016 or Excel 2019 versions.

Good knowledge of these lookup functions can help you to prepare the data in advance before you begin to analyze or summarize data with Excel.

## Using INDEX Function in Excel

If you know the row number, you can find the value on that row or column cell directly. INDEX can be used as an Array function also. Paired with MATCH, you can find any value on any row or column in a 2-dimensional array.

## How To Use Excel MATCH Function

When you want to find an exact match in an array and return the row number in the array, MATCH comes to your rescue.

It is one up on VLOOKUP, which requires you to know the column you want to return. MATCH can find a match for a value that is lower, exactly equal or higher than the specified value.

=MATCH(value, range_Lookup_Cells, FALSE)

This simple example MATCH function will look for the value in the range_Lookup_Cells, and return the row of the matched value exactly.

Index and Match can work on a single row or a single column of data too. These 2 lookup functions of Excel are the exact opposite of each other.

The index finds the value on a particular row, and Match finds the row or column number where the match happens to be found in the data range.

Both Index and Match can also run as an array form of the Excel Lookup Function.

Paired with INDEX, an INDEX & MATCH Function can manage to look up on the left or the right of any array of cells.

Want to Improve Your Excel Skills?

Learn the Key Features of Excel Quickly & Easily, by

To Get Most out of Excel, Learn the  Pivot Table techniques in our
Pivot Table Masterclass Training
!

## Master the OFFSET Function within Excel

To navigate your way in a two-dimensional array of rows and columns, you can use the OFFSET function in Excel. It can traverse any number of Rows or Columns, and get you the value.

### How to use the offset function in Excel:

=OFFSET(Starting Cell, Row to move up or down, Columns to move left or right, Number of rows required to be returned, number of columns required to be returned)

I generally use OFFSET more than INDEX and MATCH combinations. Using one super-powerful OFFSET function is more straightforward.

Thee

Once you start using Offset in Excel, you wouldn’t want to use other lookup functions of Excel.

## When Do I Use the INDIRECT Function of Excel?

The Excel INDIRECT function returns the reference specified by a string text. Any References are immediately evaluated to display their contents.

You can Use the INDIRECT function when you want to change the reference to a cell within a formula, without changing the formula itself.

=INDIRECT(A3)

The above Indirect function will check what is in cell A3. And A3 will have the cell reference to another cell. So if A3 contains B35, Excel will then read the value in cell B35.

Thus, we can get the value of the reference in cell A3. The reference is to cell B3, which may contain the value 45.

The INDIRECT can be very useful in creating custom management dashboards and reports.

## What does the FORMULATEXT Function of Excel Do?

Displays the text of another formula. This formula helps to see all formulas next to their values and can be useful to spot mistakes and issues with formulas.

=FORMULATEXT(A3) will provide you with the formula in cell A3 as a Text Value.

This FormulaText function is useful to see the formula without having to go into Editing mode.

## Begin Using ROWS Function of Excel

Displays the row number of a reference cell.

=ROWS(A1:B4)

The above function will return a value of 4. This is because there are 4 Rows in the given range.

## Use the COLS Function in Microsoft Excel?

Displays the column number of a reference cell. As evident in the following syntax, COLS function is useful to count the columns in a range.

This can be useful, when combined with INDIRECT and OFFSET, the function begins to help in generating a dynamic range.

=COLS(A1:B4)

Will return a 2. This is because there are 2 Columns in the given range: A & B.

## Use the TRANSPOSE Function in Excel like a Pro

Converts rows into columns and columns into rows. Just like the Transpose feature in Paste Special, but done programmatically.

So if you use TRANSPOSE(A1:D3), you have selected 4 columns and 3 rows.

After the Transpose is completed, you will get an array reference of 3 Columns, and 4 Rows. The horizontal table would have flipped and will be visible vertically.

So the next time you have a list of products that you want to flip from rows to columns, you can use the Transpose function.

Pretty nice use of hanging values in rows into columns.

## When Do I Use the UNIQUE Function of Excel?

The UNIQUE function of Excel generates a list of unique values that automatically spill down. An array function can be used to create data validation lists too.

Available from Microsoft Office 365 onwards. This UNIQUE function is not available in Excel 2016 or Excel 2019.

=UNIQUE(Data_Range)

## Learning the Lookup Functions in Excel Quickly & Easily

As you can see, there are a lot of LOOKUP functions in Excel, and learning and mastering them takes time. But once you do master them, you can do wonders with your Excel skills.

It is worth the effort to learn the Excel Lookup Functions. Call Intellisoft at +65-6296-2995 for Excel 365 Classroom Training that covers the key Lookup functions of Excel.

You will definitely enjoy it!

Cheers,

Vinai

Founder of ExcelChamp. Master Trainer at Intellisoft Systems in Singapore.

## Need Tips on Pivot Tables, Data Analysis or Creating Better Charts?

Do check out these resources

And if you want to learn Advanced Excel fast, then check out our detailed guide.

Articles Written By Vinai.

Vinai is passionate about maximizing Excel to improve everyday productivity. He conducts online Excel workshops as well as classroom workshops in Singapore, Australia, Middle East. If you would like to engage him to train your staff in maximising Excel, do contact us.

## How to Refresh Pivot Table in Excel

If your pivot table is created in Microsoft 365, Excel 2021, Excel 2019, Excel 2016, Excel 2013, Excel 2010 or Excel 2007, you can refresh it quickly and easily with just a few clicks, or even automatically.

Excel Pivot table Refresh is extremely easy. There are multiple ways to refresh a pivot table in any version of Microsoft Excel. And they can be used without writing any VBA code too.

## Why do you need to refresh the pivot table?

When you create a pivot table in Excel, Microsoft reads the entire data into its memory in a Pivot cache. This pivot cache is then used to build a summarized version of the report from this compressed source data. The pivot cache is actually stored internally within the Excel file and is read into memory whenever you open that Excel file.

Unfortunately, this pivot cache is not automatically refreshed every time you edit the original data.

### Key Reasons Why You may need to Refresh a Pivot Table in Excel

1. A refresh of the pivot table is necessary when the underlying data changes,
2. A source data value is corrected or updated at a subsequent time,
3. Additional Rows are added in the raw pivot table data source,
4. Existing Rows are deleted in the original data used as a source for the Pivot Table, or
5. When the day, week, month or quarter changes, new data arriving from the sources (SAP, ERP, upstream data sources) changes.

So when you create a pivot table to analyze data, and then after a few days or weeks, the data has changed, you do not need to re-create the Pivot Table. You can simply refresh it.

As you can see, there are several instances when data changes, and we need to refresh the Pivot Table. Any or all of these use cases make it necessary to refresh the pivot table – manually or automatically.

The key way to do this pivot table refresh is to prod Excel into refreshing the pivot table manually, or automatically, depending on your choice. The options to do this in many ways is listed in this article, in detail.

First of all, you must create a Pivot Table in any version of Excel. Then you can summarize data, group it, and analyze it your way. Actually, there are more than a dozen things you can do with Excel pivots, that will truly transform your data.

As mentioned, all of these versions are supported: Microsoft 365, Excel 2021, Excel 2019, Excel 2016, Excel 2013, Excel 2010 and Excel 2007. You can refresh the pivot table in any of these Excel versions.

## 4 Methods To Refresh a Pivot Table in Excel

### 1. Existing Data Values change, but there is no change in the number of rows of data:

In this method, you change the data first.

Then go to the Pivot Table. Right-click inside the Pivot, and choose the Refresh option from the right-clicked menu.

The Pivot Table is refreshed immediately. Use this easy way every time the source data changes.

You can also click on the Refresh All button in the Excel Ribbon.

### 2. There is a change in the size of the data in Excel:

New rows or columns are added to the data set, and this new data is not reflected inside the pivot table. In this case, we need to redefine the data range source used for the pivot table, to include the latest data rows & columns. There are a couple of ways to do this.

#### a. If your pivot table is based on data from a range of cells, like cells A1:G100, you need to expand this range to include additional rows and columns.

The easiest way is to go to Pivot Table Analyze Tab and choose the Change Data Source option button. The current source data range is highlighted. Go ahead and reselect the new data range, and click the OK button.

Once the data source is changed, the Pivot table is refreshed automatically too. If you wish, you can click on the Refresh button on the Pivot Table Analyze tab again at any time.

Once this is done, then you can click on the Refresh All button, and the data is refreshed from the newly selected rows/columns, and updated in the pivot. The pivot table has been refreshed instantly.

#### c. All you need to do is to click on the Refresh button under the Analyze Tab, and the pivot table is refreshed automatically.

Using a Table as a data source for the Pivot Table is a good practice you should use more often. It uses the entire Excel table when creating a new pivot table, and any extra rows added or removed later on will automatically be considered as part of the Pivot Table.

### 3. Set the Pivot Table to Automatically Refresh every time the Excel file opens:

This is the best option because you do not have to remember to refresh the pivot table at all. It will be refreshed automatically every time the Excel file is opened.

To refresh the pivot table automatically upon opening the Excel file, go to the Pivot Table Analyze tab, and look for Pivot Table Options.

When the above popup opens, select the third check box “Refresh data when opening the file”. This will ensure that every time you open the Excel file, the Pivot table will refresh automatically.

But be careful, if there are large amounts of data, or uses an external data source, the data is stored externally in a shared drive, the initial opening refresh may take some time. During this time, it may seem that Excel is not responding.

So just be patient for a few moments, and once the refresh is complete, you are good to begin your analysis of the latest, refreshed data.

Also, at the time of running this automatic refresh, you must have access to the data source. This means that the person opening the file must have access to the shared file path, and have the associated permissions to open the source file to read, update and consume it.

That’s it. We have successfully refreshed our pivot table in Excel. If you had created any custom calculation in the Pivot table, that custom calculation will be automatically updated too.

## How To Refresh a Pivot Table in Earlier Versions of Excel

If you are using an earlier or outdated version of Excel, you can still refresh the pivot table by using the step outlined above, with some minor changes to the menu or options inside the Pivot Table.

## How to Refresh Pivot Table in Excel 2016:

1. Right-Click inside the Pivot Table & Choose Refresh.
2. Redefine the range of the source data by clicking on the Change Data Source button.
3. Use a Table as a source of data. That way, when the data increases, the Table will expand, and all you need to do is to click  Refresh on the Analyze Tab.
4. Automatically Refresh a Pivot Table upon opening by setting the Pivot Table Options under the Analyze Tab.

## How To Refresh Pivot table in Excel 2013:

1. First, make the Pivot Table.
2. Right-Click inside any single Pivot Table & Choose Refresh.
3. Redefine the range of the source data by clicking on the Change Data Source button. This is available under Pivot Table Options or the Pivot Table Analyze tab. Different versions patches may show you a different option.
4. Use a Table as a source of data. That way, when the number of rows increases, the Table will expand, and all you need to do is to click  Refresh on the Analyze Tab in Excel 2013.
5. Automatically Refresh a Pivot Table upon opening by setting the Pivot Table Options under the Analyze Tab.

## How To Refresh Pivot Table in Excel 2010:

• Make the Pivot Table.
• Right-Click inside the Pivot Table & Choose Refresh.
• Redefine the range of the source data by clicking on the Change Data Source button in Excel 2010.
• Use a Table as a source of data. That way, when the data increases, the Table will expand, and all you need to do is to click  Refresh on the Analyze Tab.
• Automatically Refresh a Pivot Table upon opening by setting the Pivot Table Options under the Analyze Tab.

## How To Refresh a Pivot Table in Excel 2007:

### First, make the pivot table by clicking on the source data to select it.

1. Then Click Insert > Pivot Table. Click OK to select the entire data.
2. A new pivot table is created in a new Sheet in Excel 2007.
3. Check the Category columns to go into Rows. Check the Numeric values you want to summarize in the Values Area.
4. Then go to the pivot table by clicking anywhere inside the Pivot.

### Now to Refresh the Pivot Table created in Excel 2007.

1. Right-Click inside the Pivot Table & Choose Refresh. The data in the pivot will be refreshed.
2. Redefine the range of the source data by clicking on the Change Data Source button from the Pivot Table Options Tab of Excel 2007.
3. Use a Formatted Table as a source of data. If your data is not set as a table, go to Home > Format as Table.  Your source data will convert to a table. Now when the data increases, the Table will expand, and all you need to do is to click  Refresh on the Pivot Table Options Tab.
4. Automatically Refresh a Pivot Table upon Open by setting the Pivot Table Options under the Pivot Table Options Tab.

## To Refresh a pivot table in Microsoft Excel 365:

1. Right-Click inside the Pivot Table & Choose Refresh.
2. Redefine the range of the source data by clicking on the Change Data Source button.
3. Use a Table as a source of data. That way, when the data increases, the Table will expand, and all you need to do is to click  Refresh on the Analyze Tab.
4. Automatically Refresh a Pivot Table upon opening by setting the Pivot Table Options under the Analyze Tab.

## In Conclusion

With all these versions of Excel created by Microsoft, it is possible to refresh a pivot table manually or automatically. And it is almost similar or identical in the different versions. Once you master it, you can do the refresh in any version of Excel.

Hope this helps you.

Do share it, and subscribe to our ExcelChamp Newsletter to be notified of new tips and tricks in Excel to make you super awesome in utilizing Excel in your everyday work!

Cheers,
Vinai Prakash

Join the next cohort of Excel Data Analysis MasterClass

## 201 Things To Learn in Microsoft Excel: The Complete List

You might already know that Microsoft has a ton of features to learn…

But what the heck are they?

Well, you’re in for a treat because I’ve put together a complete list.

Some are beginner.

Others are a MUST KNOW gems.

And some are Nice to know…

But they’re all here.

Let’s dive right in.

## Editing & Shortcuts in Excel

1. New Workbook: Doing data entry, and edit existing values is pretty straightforward. Simply open a New Workbook (Control N), and begin creating Column Headings, and then enter the corresponding data in Rows.

Add data you’d like to record – to keep or to do some quick calculations.

2. Editing: Once keyed in, you can edit any value by simply overwriting it. For Formulas, you can edit by clicking in the Formula bar below the Ribbon, or by clicking the Function key F2. The F2 key invokes the edit mode for formulas. Pressing Enter or moving to another cell commits the formula, and the resulting value is recalculated and displayed in the cell.

You can see a million+ rows (1,048,576 to be exact) & up to 16,384 columns in a single Excel Sheet.

And you can load upto 255 sheets  in a single Excel workbook… which is more than sufficient in most cases.

But don’t stretch everything to the limit. Excel will slow down and might even crash… so plan your data acquisition in Excel.

3. Keyboard Shortcuts: There are numerous shortcuts to the most popular features… more than what you can mostly remember… But you must remember these few essential shortcuts of Excel.

Control A: Select All Cells in a range or entire sheet.

Control B: Bold

Control C: Copy

Control V: Paste

Control X: Cut

Control S: Save

Control ` View All Excel Formulas in a Click

F4: Change from Relative to Absolute Referencing. For example, a reference to the C4 cell changes to \$C\$4 upon clicking F4 key.

F7: Spell Check

For a complete list of shortcuts in Excel, visit Bastien Mensink’s mega list of shortcuts.

And for the Mac, you can also visit CFI’s Excel shortcuts.

4. Find & Select: This often unused gem is available on the far right of the Home Tab of Excel.

You can do multiple things with the sub options available under the Find & Select dropdown:

• find any values (Control F),
• replace any value,
• go to a particular Cell or Range (F5),
• identify only Formula cells,
• pick only Constant values,
• find cells having a data validation rule set on them,
• identify only Conditional Formatting, or
• pick  cells with Notes / Comments attached.

There are even more options hidden under the Go To Special setting, which allows you to even detect row and column differences, identify blanks, and even find precedent or dependent values.

The Find & Select feature has helped me several times in a tight spot to find things not so apparent.

5. Paste Special: While Cut, Copy, and Paste are fairly common use in Windows or Mac, the Paste Special deserves a specific mention, for it is really a nifty tool that saves time.

Paste Special allows you to paste only Formulas, only Values, only Formats, or only Data Validation to name a few.

It can also Transpose cells from rows to columns and vice versa. You need to explore  Paste Special to see its goodness and value.

6. Creating & Using Range Names: A Range is a group of cells that is given a name. Now it becomes a named range, and it can be be used to reference multiple cells within Excel formulas. They make more sense, and are an absolute fun to read too and get the meaning instantly.

Instead of referring to a formulas, such as:

=C5+P5-Z5*F3-AA5,

with range names defined, the same formula could now read as:

= basic+Overtime_Allowance-total_salary*Tax_Rate-loan_installment

Ranges can be created with the Name Box, or by going to the Name Manager (Formula > Name Manager).

Once defined, you can edit, rename, apply or delete a named range. And name ranges work across worksheets and workbooks too.

Range Names are an absolute must know features that ranks among my Top 5 Things in Excel that I use the most often…

7. Templates: If you’d like to save time doing some step repeatedly, you can use Templates. There are literally thousands of ready made templates available… some from Microsoft’s own site, and you can also create your own templates and reuse them.

Ready Templates for Invoices, Billing Statements, Calendars, Flow charts, monthly budget, attendance tracking, inventory management, Gantt charts, to-do lists, schedules and trackers are aplenty. Simply click and download a new one anytime you need. And once you have customized it to your use, you can save it on your computer, to reuse at a late time too.

8. Info: The Info tab for Excel is available from File > Info. It can help you to inspect your workbook, set a password to protect it, check the version history, and Manage versions or recover an unsaved workbook.

You can also use the Info button to key in the Keywords, Author, Title and Categories for your Excel files. It can come in handy when you are searching for a lost Excel file, but can’t remember its filename.

Advanced Properties can even show the total time taken to edit. You can add specific custom attributes to track in the Excel file, and edit them in the future.

9. Drawing Tools: If you use a touch screen laptop, a tablet or a phone, you can use the draw tools to draw shapes, key in data, convert from ink to text pretty easily.

Now there is support for the Apple Pencil, and the Surface Pens. So touch users are taken care of. There are additional accessibility options available too.

## Formatting in Excel

10. Conditional Formatting: A handy tool to selectively find and color cells that meet a certain criteria. It can be useful to identify high values, low values, cells equal to a certain value, find duplicates in a jiffy. You can specify your own rules too, and edit or manage them anytime.

Conditional formatting also allows you to display data bars, gradient colors like a traffic light, or display icons that make it apparent what you are looking for. A great management dashboard utility is hiding here.

Conditional Formatting made its way with Excel 2007, and in Microsoft 365 it is even better. I am surprised that people are still unaware of Conditional Formatting after 15 years.

Conditional Formatting is my Must Use tool when creating management dashboards and Reports that can highlight stuff on auto pilot.

11. Custom Formatting: Before Conditional Formatting, all you could do to identify cells based on simpler rules was to color them using Custom Formatting. It had 4 basic sets – colors for positive numbers, negative numbers, zero formatting, and a specific formatting for Text cells.

A good many people use Custom Formatting to add a Prefix or a Suffix to data values. It is worth exploring custom formatting. A great resource for learning Custom Formatting is the video tutorial by Leila Gharani, MVP.

12. Sorting: With quick buttons to sort in the ascending or descending order, you can click on A-Z or Z-A icons. Excel now automatically requests you to expand the selection, to avoid getting all your rows and columns messed up after a sort.

With Custom Sorting, you can specify a particular sorting order. For example, you want to sort departments by Finance, Sales, Accounts, Manufacturing & Services, in that order. In such cases, neither ascending sort, nor a descending sort will be useful. Custom sort can do this quickly. Previously it was hidden under File > Tools > Options. Now the custom sort is available from the Data > Sort menu.

Advanced Sorting can allow you to up your game, and sort as well as sub-sort on a plethora of columns. So multi-level sorting by Country, then by Department, then by Division, and finally by Cost center is no problem at all.

Excel on the web now has the option to sort by conditional Formatting too. This is really awesome! Thanks Danielle for adding this!

13. Filtering adds a quick filter all the columns on the header row. Then you can filter a particular value, above or below a particular value, Top N or Bottom N results quickly.

Advanced Filtering takes your filtering options to the next level. Now you can filter the list, but get the filtered results copied to another area of your Excel sheet.

Specifying multiple criteria, and keeping the criteria visible are the two plus points in the Advanced Filter.

## Excel’s Mega List of Amazing Functions

There are over 500+ functions in Microsoft Excel. You don’t have to learn all of them, but it is good to know as many as you can learn, and some are good to know, in case you need… The actual syntax can be looked up when needed.

Before we jump to functions, an important concept to learn is cell referencing.

14. Relative & Absolute Referencing: No formulas or functions can be useful if you don’t know when and how to convert a relative reference to a cell (C1) to an absolute reference, like (\$C\$1).

The \$ sign fixes the row or the column from changing. When both the row and the column are fixed, it is considered Absolute Reference.

And you can gain additional traction by using Mixed Cell Referencing – using \$C1 or C\$1. This fixes only the row or the column.

And now for the most useful Excel functions. I had to divide them into further groups for easier understanding. I’ll go through them one by one.

### Auto Sum Functions of Excel

On the Home Tab are the Auto sum functions, hidden under the Sigma symbol.

15. SUM:  Provides you with a Sum of values is the most common function used in Excel, to add a set of numbers. Behind the dropdown, you can use other functions to Count Values, Average Values, Find the Minimum or Maximum Value. Clicking on the More Functions option will pop-up the Insert Function dialog box. Now you can pick from any of the other available functions in Excel.

16. COUNT: Counts the numbers in a range of cells. Only numeric values are counted. Blanks are ignored. But duplicates are counted.

17. MINIMUM: The Excel Minimum function returns the smallest value from a group of cells. Use it to find or calculate the minimum value in Excel

18. MAXIMUM: Use Maximum function to find the maximum value in Excel. It returns the highest value from a group of cells.

But if you want to learn how to find maximum value in Excel with condition, you have to use MAXIF.

For multiple conditions, you can also use MAXIFS function in Excel.

19. AVERAGE: Returns the average value from a group of cells. Though not the best representation of a set of numbers, Average is still heavily used in the corporate world. You must learn to use it, but don’t rely on it overly. Other statistical functions like MEDIAN could be better at times.

### Logical Functions in Excel

Logical Functions: Learn the basics of applying logic to determine if a value is correct or not. Logical functions return any value based on the function evaluating as a True or a False.

Commonly used Logical functions:

20. IF: If the marks in a test are less than or equal to 50, display a result of Pass, else display the result of Fail. Example:

=IF(Marks >= 50, “Pass”, “Fail”)

=IF(C4>=36.4, “Fever”, “Normal”)

In the latest version of Excel 365 and Excel 2019, Microsoft has debuted the IFS function, which allows you to check for multiple conditions in one go. This eliminates the cumbersome Nested IF formulas that are often a pain to debug.

21. IFS: check for each condition to be true, and then returns the corresponding value. There is no Else in the IFS, but you can simulate one as the last condition, to be set as TRUE.

22. AND: This function can be used to check if multiple conditions are all true, and then return a confirmation value. Quite helpful to check for this condition AND that condition AND some other condition is True or not, before deciding on a resultant action.

=AND(B4=”Blue”, C4=”Dress”, D4=”Promotion”)

Will return a TRUE only if all the three conditions are true: the color is blue, cloth is a Dress, and is on Promotion.

Combined with an IF function, the AND and the OR functions can deliver more. You have give a higher discount if all the conditions are met, as illustrated in the example.

=IF(AND(B4=”Blue”, C4=”Dress”, D4=”Promotion”), 20%, 10%)

23. OR: The OR logical function can be used to check if any of the multiple conditions are true, and then return a confirmation value. Quite helpful to check for this OR that OR something else before deciding on the resulting action.

24. NOT: This logical function can be used to negate the result of any calculation. It can change TRUE to FALSE and vice versa.

25. IFERROR: Checks if an expression is giving an error or not. Useful to display a more graceful message rather than showing the actual ugly errors like #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME?, or #NULL!.

=IFERROR(value, value_if_error)

26. IFNA: Similar to IFERROR, this function catches the #N/A error type. Useful because usually VLOOKUP & HLOOKUP return the #N/A error when an exact match is not found. IFNA can rectify the result if needed.

27. SWITCH: This newly added function to Office 2019 and Office 365 is not available in previous versions. The SWITCH function evaluates one value or expression against a list of values, and returns the result corresponding to the first matching value. If there is no match, an optional default value can be returned.

The SWITCH function is familiar in programming language like Python & PHP, but having it in Excel simplifies it. I find myself using SWITCH more often than the IFS function. But both have save me a lot of time that was lost in writing and debugging Nested IFs.

### Database Functions of Excel

Database function assume your data to be like a database – with proper column names and rows of data, nicely formatted in a table.

28. DSUM: Add all the numbers that match a particular criteria. For example, this formula will add the sales column in the range A10:Z100, where the criteria matches the column and field settings in B2:E5.

=DSUM(A10:Z100, “Sales”, B2:E5)

29. DAVERAGE: Averages the cells  in the specified database column, as per the criteria.

30. DCOUNT: Counts the cells that contain numbers in a database table, as per the criteria. Text and blanks are skipped.

31. DCOUNTA: Counts non blank cells in a database, based on the criteria specified. Text as well as numbers are all counted.

32. DMAX: Pick the Maximum value from the selected database entries, as per the criteria. Only try this on numeric columns.

33. DMIN: Pick the Maximum value from the selected database entries, as per the criteria. works with numeric columns only.

34. DVAR: Estimates variance based on a sample from the selected database records.

35. DSTDEV: Estimates standard deviation based on a sample from the selected database records that match the criteria. Gives a good indication of the spread from the mean. The higher the standard deviation, the more is the spread.

### Lookup Functions in Excel

Some popular Excel Lookup functions are VLOOKUP, HLOOKUP & the newly added XLOOKUP is becoming very popular too. (XLOOKUP is currently only available in Office 365 versions.)

For the power users of Excel, the mastery of INDEX, MATCH & OFFSET can be considered vital, as these are considered the advanced lookup functions in Excel.

But with the introduction of XLOOKUP, some of the jugglery created by mixing INDEX & MATCH combination is no longer required.

36. VLOOKUP: The most MUST HAVE Function ever. Even Excel gurus can’t live without it. I polled a group of Excel heavy weight asking if Excel’s VLOOKUP as overrated. I got a severe backlash for even mentioning it. Almost everyone said that it is their GO TO function, an absolute must have, and that Excel won’t be that useable if this function was taken away. Most people swear by their VLOOKUP functions.

According to legend, VLOOKUP mastery is what separates the Pro Excel users from the Amateurs!

Vlookup is akin to using a dictionary. You know the word, and you want to find out the meaning. The V in VLOOKUP stands for the dictionary being a vertical dictionary.

=VLOOKUP(word, dictionary, column number of meaning, exact_match_ype)

The difficulty most people have with VLOOKUP is the last flag – TRUE or FALSE ( You can use 1 for True and 0 to indicate the False flag). The mystery is created because to use an exact match, you have to specify the last optional flag, and set its value to a FALSE or a 0. By default, it is set to 1, which is useful for an approximate match type only.

Further, VLOOKUP can only lookup columns on the right of the looked up value. It can’t look to the left!

37. HLOOKUP: An oft forgotten cousin of VLOOKUP, this Lookup and Reference function in Excel works in a similar way too. The only difference is that in this case, the lookup dictionary is a horizontal dictionary of  columns, denoted by the H.

HLOOKUP is most used in range lookups, rather than exact matches, as columns are not the best suited for exact values, because of their limit of 16000. Where a list can grow vertically to over a million records easily.

38. XLOOKUP: Finally, after years of backlash at Microsoft for creating the mess with the Match Type (True and False) in VLOOKUP, they got rid of it completely in the Excel XLOOKUP function. And by default, XLOOKUP is set to do an exact match.

XLOOKUP requires a deeper understanding of the various scenarios. I’d recommend a dedicated video tutorial on XLOOKUP by Excel MVP Chandoo to learn how to use XLOOKUP in Excel.

This new XLOOKUP function of Excel is only available from Microsoft Office 365 users. It does not work on Excel 2016 or Excel 2019 versions.

39. INDEX: If you know the row number, you can find the value on that row or column cell directly. INDEX can be used as an Array function also. Paired with MATCH, you can find any value on any row or column in a 2 dimensional array.

40. MATCH: When you want to find an exact match in an array, and return the row number in the array, MATCH comes to your rescue. It is one up on VLOOKUP, which requires you to know the column you want to return. MATCH can find a match for a value that is lower, exactly equal or higher than the specified value.

Paired with INDEX, a INDEX & MATCH Function can manage to lookup on the left or the right of any array of cells.

41. OFFSET: To navigate your way in a two dimensional array of rows and columns, you can use the OFFSET function in Excel. It can traverse any number of Rows or Columns, and get you the value.

How to use offset function in Excel:

=OFFSET(Starting Cell, Row to move up or down, Columns to move left or right, Number of rows required to be returned, number of columns required to be returned)

I generally use OFFSET more than INDEX and MATCH combinations. Using one super-powerful OFFSET function is more straightforward.

Once you start using Offset in Excel, you wouldn’t want to use other lookup functions of Excel.

42. INDIRECT: Returns the reference specified by a text string. References are immediately evaluated to display their contents. Use INDIRECT when you want to change the reference to a cell within a formula without changing the formula itself. Very useful in creating custom management dashboards and reports.

43. FORMULATEXT: Displays the text of another formula. This helps to see all formulas next to their values and can be useful to spot mistakes and issues with formulas.

44. ROWS: Displays the row number of a reference cell.

45. COLS: Displays the column number of a reference cell.

46. TRANSPOSE: Converts rows into columns and columns into rows. Just like the Transpose feature in Paste Special, but done programmatically.

47. UNIQUE: Generates a list of unique values that automatically spill down. An array function, it can be used to create data validation lists too. Available from Microsoft Office 365.

### Financial Functions of Excel

There are over 55 Financial functions that calculate interest, accrued interest, Discounts, Interest payments, Present value, payment for an annuity, Rates, Yield of treasury bills and much more. Here are some of the top Financial functions in Excel you must know are:

48. ACCRINT: Return the accrued interest on a security that pays interest periodically.

49. DISC: Returns the discount rate for security.

50. FV: Returns the future value of an investment.

51. IRR: Returns the internal rate of return for a series of cash flows. Useful for Project Managers and Cost Accountants.

52. NPV: Calculates the Net Present Value of an investment, at a fixed rate.

53. PV: Finds the present value of an investment.

54. PMT: Find the monthly payment of an annuity.

55. YIELD: Returns the yield of a security that pays periodic interest.

### Statistical Functions of Excel

You don’t need a dedicated SPSS kind of huge software for doing Statistical analysis. Excel has a plethora of stats functions to assist in calculating Median, Standard Deviation, Variance, F Test, T Test, Analysis of Variance (ANOVA), do Correlation and Regression, and much more.

To display a list of statistical functions in Excel, start by inserting a function, and choose Statistical Functions from the Category list.

56. BETA.DIST: Returns the beta cumulative distribution function

57. BINOM.DIST: Returns the individual term binomial distribution probability

58. CORREL: Returns the correlation between two data sets. The closer the returned number to 1, the higher the correlation.

59. COUNTBLANK: Counts blanks in a range of cells.

60. COUNTIF: Counts cells only if they meet a certain criteria.

61. SUMIF: Sums values based on the criteria in another cell range.

62. CORREL: Finds the correlation between two data sets.

63. FORECAST: Returns a Forecasted value based on the past linear trend.

64. FREQUENCY: Returns a frequency distribution as a vertical array

65. GAMMA: Returns the gamma function value of a range

66. GROWTH: Returns values along an exponential trend, based on the past data. Compare with FORECAST which is a linear forecast.

67. INTERCEPT: The intercept value on a linear regression line. If you are familiar with y=mx + c equation for regression, the intercept is the constant c value.

68. SLOPE: For the linear regression line, the Slope produces the value for the m in the y=mx + c equation.

69. LARGE: While Max can pick the highest value, LARGE can pick the second or third highest values. Simply put the rank number you are looking for.

LARGE(A1:A10, 3) gives you the third largest value. This is because we put a 3 as the last argument in the function.

70. MEDIAN: Median find the middle value from a range of numbers. If you want to find Median in Excel, the simplest way is to write MEDIAN(A1:A10).

Median is better than Average, as it is not affected by extreme outlier values.

70. SMALL: Exactly opposite of LARGE. SMALL will provide the n’th smallest value.

SMALL(A1,A10, 2) will find the second smallest value.

71. LINEST: The array of both parameters of a  Linear estimation trend – the slope and the intercept, both are provided in a single array function.

72. TREND: Returns values along a linear trend after analyzing all the past data, and then providing a trend line that can so the projection.  Very useful for linear forecasting, so you can use it for budgeting and planning.

73 VAR.P: Provides the variance of a range of values, taken as a whole population. Note that the Standard Error handling is different for the Population and the Sample in a Variance calculation.

### Math & Trigonometric Functions

There are over 50 different functions delving into the Sine, Cosine, Tangent, Absolute, Aggregate values and more. Some key functions are:

74. SIN: Returns the sine value of a given angle.

75. COS: Returns the cosine of a number

76. TAN: Returns the tangent of a number

77. ABS: Returns the absolute value of a number

78. CEILING: Rounds a number to the nearest integer or to the nearest multiple of significance

79. DEGREES: converts radians to degrees.

80. FACT: provides the factorial of a number

81. INT: rounds the number down to the nearest integer.

82. LCM: provides the least common multiple. Useful for primary school children… though they have to work it out manually.

83. MOD: Returns the remainder from a division.

84. RAND: Returns a random number between 0 and 1.

85. ROUND: Rounds an number to a specified number of digits.

86. ROUNDUP & ROUNDDOWN: can round a number up or down, to the specified digits.

87. SQRT: Returns a positive square root of the number.

88. TRUNC: Truncates any number to an integer.

### Excel Date and Time Functions

Excel is meant to handle numbers. It is still not good at managing dates, and many times dates like MM/DD/YY can be mixed up with DD/MM/YY. This can be a pain when you work in a multi national company which has offices all over the globe, and people routinely email files of different country and currency formats to one another.

Dates are actually stored as numbers in Excel.

So you have no choice but to use existing Excel functions to make the best of these dates. Because without Dates (Date of Birth, Date of Hire, Sales Date, Purchase Date, Manufacturing Date etc.), we can’t do any meaningful data analysis.

Here are the most commonly used Date and Time functions in Excel that you must familiarize yourself with, to save time and get things done pronto!

89. DATE: combines any year, month and day to create a date. useful if your day month and year are available in separate columns.

=DATE(year, month, day)

90. YEAR: Extracts the YYYY portion of any date. Useful if you want to look at years to sort or analyze by.

91. MONTH: Extracts the MM portion of any date (1-12). Useful to know the month of any date, and in doing grouping or sorting or filtering values by month.

92. DAY: Extracts the DD portion of any date (1 to 31).

93. EOMONTH: This useful function returns the last day of the month, n months in the past or future. You can use this function to calculate any due dates or expiration dates that need to land on the last day of a particular month.

94. NETWORKDAYS: The difference between 2 dates, without counting the weekends. Optionally, you can also add a list of holidays, which may be during weekdays. So this can be pretty useful for calculating the number of actual working days. Saturdays and Sundays are not counted at all.

95. NETWORKDAYS.INTL: If you live in a country where Saturday and Sunday are not the default off days, you can use the Networkdays.Intl function. This International function can handle a single off day, or 2 continuous off days… say Thursday and Friday are off for you, then this function can be used to exclude only those 2 days. I have used it heavily when working in Middle Eastern countries. If you have staff having different off days, you’ll find this function very useful.

96. NOW: Provides you with the current date and time. This is a volatile function. It will change to show the current time every time you recalculate any formula. I like it to show the report’s date and time, like Report as of dd-mmm-yyyy hh:mm:ss.

97. TODAY: Returns the current date (today’s date). This too is a volatile function and will change every time you refresh Excel. So I like to have it in the Invoice Date column. Every time I open the New Invoice File, it is pre-filled with today’s date. The date is returned as the format of your PC – it could be dd-mm-yy or mm-dd-yy or something similar.

98. WEEKDAY: Generates a number that will identify the day of the week. Values are from 1 to 7. You can choose which day will be considered 1. By default, Sunday is counted as the First day.

99. WORKDAY: Add a specific number of working days to any date. Generates a Serial number corresponding to the date. You have to convert the serial number to a date, or just change the format of the cell to a Short Date and you’ll see a date in the future. This excludes non-working days.

100. WORKDAY.INTL: This international brother of WORKDAY will allow you to skip any particular day or weekend of your choice.

101. YEARFRAC: Calculates the fraction of the year represented by the number of whole days between the start date and the end date. You can use YEARFRAC to identify the proportion of a whole year’s benefits, or obligations to assign to a specific term.

### Text Functions in Excel

Text functions in Excel help to extract and clean up the data. If you have to extract certain characters off serial numbers or to tidy up erroneous data, you can rely on Text functions.

Prior to the Power Query days, I used to heavily use Text functions in Excel. Nowadays, I try to do as much cleanup using Microsoft Power Query, and then the remaining can be done in Excel, with the simple and advanced text functions in Excel.

102. CHAR: displays the ASCII character from the number provided. You can use different character sets too.

103. CLEAN: Removes all non printable characters from the supplied text. Can be useful to remove junk and strange characters. I use it to strip the junk characters from spam comments.

104. CODE: This is an exact opposite of the CHAR function. Supply any text, and this CODE function will return the numeric code of your supplied character, in your chosen character set.

105. CONCAT: The shorter form of Concatenate function was introduced in Excel for Office 365. It can help you to combine multiple text strings together. CONCAT does not leave any spaces between 2 text by default. To add a space, you must explicitly add it.

106. CONCATENATE: This function is still going to be available for backward compatibility. But it is better and shorter to use CONCAT. If you are stuck with an older version of Excel, use CONCATENATE to combine multiple pieces of text.

107. LEFT: Provides you any number of characters from the left of the text string. Useful to pick the first few chars of a serial number.

108. RIGHT: Picks the last few characters from the right side of any text string.

109. MID: Once you specify the starting point, and the number of characters needed, it can pick any text from the middle, beginning or end of any text. Useful to extract something in between a text. Can be good if you can spot a pattern in picking up the text and make it dynamic.

110. LEN: The total number of characters in a string. If there are any preceding or trailing blanks, they are counted too. Useful to pick up any anomalies in fixed-length text.

111. FIND: Find any string in another string. Useful to pick a substring. FIND is case-sensitive. It returns a #VALUE error if nothing is found. So pair it with IFERROR to avoid ugly errors.

112. SEARCH: Similar to FIND, it can pick a substring in any other string. But SEARCH is not case-sensitive.  Also returns a #VALUE if nothing is found.

113. UPPER: Converts any string into all upper case characters.

114. LOWER: Converts the string into all small case letters.

115. PROPER: This function can convert your text into a Camel Case Text. The first character after a space is capitalized. Good for proper names.

116. REPLACE: Replaces any part of the string with any other string. Useful for conditional replacements used within nested functions.

117. REPT: Used to repeat a text character a fixed number of times. In the old days, people used this function to generate a dashed line of 60 characters. I don’t find any particular use of this function other than for beautifying some text outputs.

118. TEXT: Converts a value to text in a specific format. An amazing number of variations are possible from this hidden gem. Numbers, Floating Points, Dates, and Amounts can be nicely formatted with this. Worth exploring more.

=TEXT(TODAY(),”DDDD”) generates Monday

=TEXT(TODAY(),”DDD”) generates Mon

=TEXT(TODAY(),”DD”) generates 01

=TEXT(TODAY(),”D”) generates 1

119. TEXTJOIN:  Newly introduced in Microsoft 365, this is one up on CONCAT. It can concatenate multiple values, and even separate them with a delimiter.

120. TRIM: Simple function that can remove spaces from the front and back of a string. Useful to kill all blanks, and Trim the text.

121. VALUE: Useful to convert a number stored as text into a real number.

122. VALUETOTEXT: Converts a number into its Text equivalent. This function is the exact opposite of VALUE.

123. Viewing Formulas: To view any formulas, you can go to Formula > Show Formulas. The shortcut key is Control + ` (this key is usually above the Tab and below the Escape key on most keyboards.

124. Editing Formulas: To edit any formula, simply double click in the cell or press the F2 key, and edit the formula in the cell. You can also edit any formula in the formula bar.

125. Inspecting Formulas:  For long formulas or nested formulas that have a long syntax, it can be tedious to check where the error is coming from.

So to edit any formula, and see the intermediate results of any portion of the formula, you can highlight the section that you want to evaluate. Then click F9, and it will show you the result.

Keep in mind that this is a destructive change. So if you press enter, you will lose your formula section. Therefore, after testing the formula edit section, remember to back out of the change.

## Exploring Tables in Excel

Tables were introduced by Microsoft with Excel 2007. Since then, Excel tables have come a long way, becoming a must use feature. The name is a misnomer, for the hidden functionality below the “Format as Table” button doesn’t do it any justice.

126. Create a Table: From the Home > Format as Table button. You get a series of pastel shades. Pick any one. A table is created, and you see colourful lines. Notice a new tab added in the ribbon – Table Tools. This new tab contains all formatting and usability options for Tables.

Edit the Table, Rename it, Show Totals, Add Filters, and more here. It is a breeze to add new rows and columns in a table. And you can refresh a table anytime.

Tables can be created from external data coming from Text files, CSV, Databases or other Excel files too.

Table columns have range names created. In fact, Microsoft created a completely new Structured references to handle table column usage. It is an amazing must have tool in you Excel arsenal.

127. Slice the data with Slicers & Timelines (Date based slicers). This is much easier to view the data, specially with multiple slicers and timelines. It is a visual treat to analyze data with Excel Tables. To learn more, check out these resources –  Tables and ExcelCampus Table Tutorial.

## Pivot Tables To Summarize Data in Excel

Pivot Tables help you quickly summarize data with Excel. You don’t have to write any formulas or functions. Simply use the mouse to create a Pivot table in a flash.

You could be looking at the Total Sales by Country, Division, Group, Year, Quarters or Months in less than a minute.

Pivot Table is a key features for business analysts & power users of Excel.

128: Create a Pivot table from any data, range or table. The default pivot table is created on a new sheet. Columns from the data headings become fields. Check a few of the fields, and you can see a quick summary emerge. Drag and drop fields into rows and columns and begin your data analysis.

129: Create Cross Tab Reports: Pivot Tables enable you to create Cross Tab Reports, Do sum, count, average etc.

130: Group Data in Pivots: Advanced Features of Pivot Tables allow you to group data, and analyze % of grand total, row total or column total. You can look at the cumulative growth of the data, and slice it with different columns, Years, Quarters etc. with ease.

As the data grows, you simply Refresh the pivot table and it is instantly updated with the data up to the month, or the minute…

Recent advances in Pivot table technology allow you to create pivots from Multiple Tables, multiple files, and it builds a  Data Model for you to pick different elements from different tables.

131. Create Report Filter Pages: This hidden utility under the Pivot Table Options allows you to create a separate tab for each filtered field value in the same worksheet.

Check out these key resources to learn and advance your knowledge of pivot tables – Creating a New Calculation in an Excel Pivot Table, How To Show Values and Percentages in Excel Pivot Tables, How to summarize data fast.

## Visualizing Data in Excel Charts

If you do any kind of analysis or presentation, you must have used Excel’s charts.

An Excel bar chart or a Pie chart is commonly placed in PowerPoint Presentations in the boardroom or with clients.

Charts have been available in Excel since the very first version. They haven’t changed much, but new chart types have been added over the years, almost in every new version.

132. Create a Chart in Excel: Today you can create over 18 different types of charts in Excel easily. You must know the strength of each chart to be able to choose it effectively.

133. Column Chart is best suited when you have a few columns. For more than 10 columns, this type of chart begins to look cluttered. And if you have a comparison of last year vs this year, for multiple products, it may be difficult to see what you want your audience to see. So it is better to use column charts when you have no more than 10 columns.

134. Line Chart looks better as it is less cluttered compared to a column or bar charts. But don’t make the mistake of displaying data labels for each data point, for it will spoil it. A line chart comparing two different currencies or products or sales territories is an excellent choice.

135. A Bar chart is not as good as a column chart, for it is difficult to visualize things horizontally compared to vertically. A bar chart is almost like a column chart flipped onto its side, but I still prefer column charts over bar charts.

136. Pie Charts are best to show the distribution of a region, product, or something similar. But it only makes sense to split it into a pie chart for 3-5 categories only. I have seen pie charts showing over 30 different items, with most smaller areas only getting the thinnest slice, and we are unable to discern anything from such charts.

137. Area charts are like a filled chart, showing peaks and vallies. It is more like a line chart, but with the bottom area all filled up. Looks better if shown only for a single dimension.

138. Scatter Plot are best to show the relationship of 2 variable over a period of time. An X Y Scatter plot is the best choice when trying out correlation and regression to spot the trend and forecast the next values.

139. A Stock Chart is useful to show fluctuations in stock prices. It can be useful to show fluctuations in water level, voltage, daily rainfall or annual temperatures or humidity too.

140. A Surface Chart is is useful when you want to find the optimum combinations between two sets of data. Just like a topographic map, the colors & patterns indicate the areas that are in the same range of values.

To create a Surface chart in Excel, make sure that both the categories and the data series are numeric values.

141. A Radar Chart compares the aggregate values of several data series. Radar,  and Radar with Markers show values relative to a central point. Radar with Markers chart shows the markers for the individual points and a Radar chart shows without the markers for the individual points.

142. A Treemap Chart provides a hierarchical view of your data and makes it easy to spot patterns, such as which items are a store’s best sellers. The tree branches are represented by rectangles and each sub-branch is shown as a smaller rectangle. The treemap chart displays categories by color and proximity and can easily show lots of data which would be difficult with other chart types.

Treemap charts are good for comparing proportions within the hierarchy, however, treemap charts aren’t great at showing hierarchical levels between the largest categories and each data point. A sunburst chart is a much better visual chart for showing that.

143. Sunburst Chart: The sunburst chart is ideal for displaying hierarchical data. Each level of the hierarchy is represented by one ring or circle with the innermost circle as the top of the hierarchy.

A sunburst chart without any hierarchical data (one level of categories), looks similar to a doughnut chart. However, a sunburst chart with multiple levels of categories shows how the outer rings relate to the inner rings.

The sunburst chart is most effective at showing how one ring is broken into its contributing pieces, while another type of hierarchical chart, the treemap chart, is ideal for comparing relative sizes.

144. Box and Whisker Chart: A box and whisker chart shows distribution of data into quartiles, highlighting the mean and outliers. The boxes may have lines extending vertically called “whiskers”. These lines indicate variability outside the upper and lower quartiles, and any point outside those lines or whiskers is considered an outlier.

Box and whisker charts are most commonly used in statistical analysis. For example, you could use a box and whisker chart to compare medical trial results or teachers’ test scores.

145 Funnel Chart: Funnel charts show values across multiple stages in a process. For example, you could use a funnel chart to show the number of sales prospects at each stage in a sales pipeline. Typically, the values decrease gradually, allowing the bars to resemble a funnel.

146. Donut Chart: Using Microsoft Excel, you can quickly turn your data into a doughnut chart, and then use the new formatting features to make that doughnut chart easier to read. For example, by adding a legend, data labels, and text boxes that point out what each ring of a doughnut chart represents, you can quickly understand the data that is plotted in the chart.

147. Histogram Chart: A histogram is a column chart that shows frequency data. However there is no gap in between the columns. The columns are not sorted in any order.

148. Two Axis chart: When the numbers in a chart vary widely from data series to data series, or when you have mixed types of data (price and volume), plot one or more data series on a secondary vertical (value) axis. The scale of the secondary vertical axis shows the values for the associated data series. A secondary axis works well in a chart that shows a combination of column and line charts. You can quickly show a chart like this by changing your chart to a combo chart.

149. Waterfall Chart: A waterfall chart shows a running total as values are added or subtracted. It’s useful for understanding how an initial value (for example, net income) is affected by a series of positive and negative values.

The columns are color coded so you can quickly tell positive from negative numbers. The initial and the final value columns often start on the horizontal axis, while the intermediate values are floating columns. Because of this “look”, waterfall charts are also called bridge charts.

150. Map Chart: You can use a map chart to compare values and show categories across geographical regions. Use it when you have geographical regions in your data, like countries/regions, states, counties or postal codes.

Notes on Charts:

While the column and pie charts are the favorite in the board room, other new types like the Funnel, Waterfall, Radar, Surface and Tree maps are pretty useful for detailed analysis.

I particularly use the 2 Axis charts, which allow you to measure 2 things at the same time in the same chart, and even the scale can be different.

For stock traders and financial analysts who love to see the highs, lows, the 25th quartile, 75% percentile and the median prices, a box you can’t go wrong with a box and whisker chart can be the go to chart.

Learn about the new chart types in Excel, and how to create 2 axis charts.

For more in-depth understanding of the different charts and their use, checkout these resources – site1 & site2.

151: Change Chart Type: On the Chart Design > Change Chart Type option allows you to change your mind, and try out another chart type. It is easy to change charts. Further, some data looks good on columns or bar charts. But when you have too many data points, a Scatter Plot may be better. Similarly, it is worth exploring the Waterfall charts, Funnel Charts and the TreeMap.

152. Format a Chart: You can format a chart by

• adding or removing the Legend,
• Display Data Labels
• Show X & Y Axis
• Remove Grid Lines
• Change the major and minor  axis intervals
• Change Units of display. Useful to show thousands or millions in a chart axis.
• Display a Data Table
• Add Error Bars to show data variability.

Error bars in Excel are graphical representations of the data variability. They show the precision of a measurement. The Error bars usually represent standard deviation. It will show the estimated standard deviation based on the sample data.

153. Change the Data Source: As data grows, you may want to change the data source and add extra column and rows. This is easily supported from the Chart Design > Select Data.

154. Beautifying an Excel Chart is a whole new ballgame. You have hundreds of WordArt Styles, Shape Styles, Arrangement options, Shape outlines and effects. Don’t be lost in the chart makeup, for the options are aplenty.

155. Pivot Charts is a special type of chart, which is linked to a Pivot Table. When the pivot table changes, or is filtered or sliced, the corresponding Pivot Chart will change too. Explore this after creating a Pivot Table. Useful for Management Charts and Reporting in a Dashboard too.

## Import & Clean Data in Excel

Before you can do any analysis or reporting, you need to bring in the data from somewhere or key it in yourself.

Excel can import data from Text Files, CSV files, Excel files, Access databases, SAP, Oracle, Microsoft SQL Server databases, Facebook, Google, Salesforce, Scrape the web, or import data from hundreds of online services.

156. Import Data into Excel: To invoke the importing function, visit Data > Get External Data.

Newer versions of Excel, starting Excel 2016, Excel 2019 and even Microsoft 365 invoke the Power Query editor automatically when you click on Data > Get Data, and pick a source.

It is easiest to load Text Files, CSV Files, Excel files. But you can also load data from the web. Loading & extracting tabular data from the web is a breeze. That’s why many stock traders get the latest currency exchange rates, current stock prices from the various stock markets live into Excel using this feature. It can also pick XML data or from data using a live data streamer (using the Data Streamer Add-in)

157. Clean Data Using Power Query: The wizard helps you to import and clean data. Power Query is a great add on to Excel that simplifies the importing and cleaning of data. Microsoft Power Query provides a powerful data import experience that encompasses many features. Power Query works with Analysis Services, Excel, and Power BI workbooks.

Previously we had to write cumbersome macros that were difficult to edit to make changes to the importing and data cleaning routines.

Power Query automatically records your steps, and you can go back and forth to edit the queries easily.

And tomorrow, next week or next month, when the data changes, you can automate the re-importing of additional new data on auto pilot.

158. Learn M Code: To customize it further, Power Query has a new language called M, which helps you to further gain advanced control over the queries execution and cleanup routines.

A core capability of Power Query is to filter and combine, that is, to mash-up data from one or more of a rich collection of supported data sources. Any such data mashup is expressed using the Power Query M Formula Language. It’s a functional, case sensitive language similar to F#.

Once the data is cleaned, it can be loaded into a Excel Table, or held in an Excel Data Model.

Keep in mind that Excel has a limit of just over a million records. So anything larger, it is better to store it in a data model, and do your reporting off the model.

Learn more about Power Query from Leila with this awesome video here, and from Mike Girvin’s ExcelisFun video here.

## Analyzing Data With Excel

The main draw of Excel is its ability to do some quick data analysis. So it is good to get the totals, or counts using simple formulas or pivot tables, but the ability to play What-If, analyze data statistically and Forecast based on predictions from the past data is what is the most valuable option. Have you attended Vinai’n Data Analysis With Excel MasterClass?

Most of the data analysis options are on the Data tab of Excel. Let’s explore these Data Analysis options in more detail.

159. Text to Columns: Allows you to convert text separated by specific characters into several columns. You can have fixed width or variable length data, but as long as it is separated by a fixed character, it is easy to split the data into several columns. Useful for splitting multiple cells into separate columns.

160. Flash Fill: Quickly fill data in a series. So if you want to generate numbers in a particular sequence, like Employee numbers, Part Numbers, or Months or Years, it can be done in a flash with Flash Fill. The series does not have to be continuous, and you can skip numbers too, as long as there is a pattern to follow.

161. Data Validation: Lots of time is wasted in correcting data entry errors, and fixing values that do not match particular criteria.

The Data Validation features helps to reduce or completely avoid invalid values from the file by nudging or forcing the user to key in correct values only.

You can opt between Information, Warning, or an Error to prompt for correct value submission. Useful error messages can add value while doing data entry.

Data Validation can pick the values to be shown in a drop down box. A quick nifty trick is that this dropdown can be set to show values dynamically. I learned it from Michael Rempel’s tutorial on using a dynamic list in a dropdown (works only with Office 365).

162. Remove Duplicate Rows: Remove Duplicates button makes it easy to get rid of Duplicates in a single click. If you are paranoid and want to see the duplicates before you delete them, check out the Conditional Formatting tool under Home, which can highlight duplicates, but does not delete them. With both tools, you can identify and get rid of dupes forever.

163. What If Analysis With Multiple Scenarios: The What-if analysis button hides three gems to analyze and understand your data – Goal Seek, Scenario Analysis & Data Tables.

By using What-If Analysis tools in Excel, you can use several different sets of values in one or more formulas to explore all the various results and compare them for a better decision making.

For example, you can do What-If Analysis to build two budgets that each assumes a certain level of revenue. Or, you can specify a result that you want a formula to produce, and then determine what sets of values will produce that result. Excel provides several different tools to help you perform the type of analysis that fits your needs. Let’s explore them in more detail.

164. Data Tables: To check the monthly installments for varying interest rates, you can use a one variable data table, which allows for you to change one value at a  time, while keeping the other values constant. Similarly, you can also create two variable data tables and change 2 things at the same time, while keeping other things the same.

165. Goal Seek:  Goal Seek is like a reverse engineering tool. You can feed your final formula, and ask a reverse question. For example, if I want to pay an installment of \$2000 per month, and interest rates stay stable, how much loan can I get for a period of 15 years.

Scenario Manager: While Goal Seek allows you to change 1 thing, and data tables allow you to change 2 things, Scenario Manager allows you to change any number of things. So you can really play what if.

If interest rates rise by x%, the demand declines by y%, the supply cost goes up by z%, and revenue dips to r, then what will be my net margin.

166. Forecast Sheet: The Forecast Sheet icon under Data is a new Feature on Microsoft 365. It allows you to analyze the past trends in the data, and then help you to forecast the future. Microsoft is trying to make forecasting easier, and this is a branch out from the hidden Analysis Toolpak, which can be a bit too much for non-geeks.

167. Outlining: Outlining group contains the buttons to group and ungroup data, so you can see subtotals, and grand totals. THis is a pretty old feature, and I actually prefer Pivot Tables more than using the grouping and ungrouping in this. But, the benefit of this feature is that you can see the subtotals and grand tolas within the data itself.

168. Grouping / Ungrouping creates multi level hierarchies so you can see the totals at page, group or sub group level.

169. Subtotal provides you with an option to sum, count, average, find the minimum or maximum of any columns, grouped by change in any particular text column. Pretty handy to see the average or counts or sums, whenever the country or division or the zone changes.

170. Slicing:  Slicers are available in multiple places to slice the Excel data in Tables, Pivot Tables, Pivot Charts or in Dashboards easily. You can slice by any type of category fields. Dates, Quarters, years, month are also available. Further, there is a special slicer for dates, called as a Timeline.

## Auditing Features in Excel

Doing a spell check, a grammar check, getting translation done and making sure that there are no mistakes are all part of auditing features. Some of these Auditing features of Excel are placed under the Review Tab, and others are in other places.

171. Spell Check: To perform a spelling check in Excel, there’s a shortcut – F7. It can bring up a dictionary of any known language in your PC, and you can do a spell check. You can change them automatically, and even add new words into the dictionary.

172. Thesaurus: Allows you to easily find synonyms of any keyword in Excel. This is the same feature that is available in Microsoft Word and Microsoft Outlook too.

173. Formula Auditing: This useful group of utilities on the Formula Tab allows you to find out where are the formulas that can impact a cell. You can check for error values, display formulas, and even evaluate formulas.

174. Trace Dependent: points an arrow to the cells that have a formula directly impacted by the value of the cell. Good to know which values would change whenever you change a particular cell. Useful when working on huge worksheets. If you press Trace Dependent a second time, it will further show the resulting formulas that are impacted. You can see multiple levels in this way.

175. Trace Precedent: Identifies cells that contribute to the value in a formula. If you are wondering where are the cells that contribute to the value of this resulting formula, use it. Pressing a second time shows the earlier

176. Remove Arrows: will remove the precedent or dependent arrows from the display. All the levels of arrows are removed in one click.

177. Evaluate Formula: For long and nested formulas, sometimes it is important to view only the result of a portion in a long formula. The Evaluate Formula button can come handy here. It can help you to evaluate a portion of a larger formula and show you the result instantly. Remember to click Restart, and undo the change before you exit out of this feature.

## Sharing Excel Data

178. Protection of a Workbook: The whole workbook can be protected. you can setup Read only access or Read and Edit Access. You can also protect certain sheets from being opened. And Excel worksheets can be hidden too.

Passwords can be set to allow open or modify access to the Excel file. These settings are under the Save > Tools > General Settings.

179. Protection of a Worksheet: A worksheet can be hidden, and you can protect if people can see the information on it or not. You can lock down the access selectively,  to disallow any changes to the formatting, or values, or inserting or deleting of data, rows, or columns.

180. Protecting a Cell Range with Range Passwords: Instead of hiding the entire sheet, you can protect a few cells only. You can either make the cells that need data entry to be open, and close other cells from prying eyes, or vice versa. For rates, or employee grades or salaries, you can setup a range password, which will ensure that only people with the password to the cell can see the hidden values, or modify them.

181. Hide Formulas in Excel Cells: Similar to protecting cells with range passwords, it is easy to protect the Excel formulas too. Only users with a password can unlock the cells and see the passwords. So other users won’t even see the formulas, for they can be hidden too.

182. Privacy Options in Microsoft Excel: With the privacy options, you can choose not to disclose your data to Microsoft, which they collect for Diagnostic purpose. Check it out under File > Options & Trust Centre Settings.

183. Linking to Multiple Worksheets & Workbooks: Linking data from multiple files can be a great way to consolidate the data quickly. You can also place hyperlink that take you to another cell in another file quickly.

## Page Layout in Excel

Excel’s page layout options can make your printouts, PDF files or on screen outputs seen as professional documents and reports.

You can tweak fonts, colors, themes, effects, margins, orientation, layout, size, print area, width, height, pages, scaling, views and much more from the Page Layout menu in Microsoft Excel.

184. Page Setup for Beautiful Prints: To do a complete Page Setup, you can click on the down arrow, hidden in the Page Setup group, or go to File > Print, and invoke the setup menu.

Choose Header, footer, margin, layout, pages, size and print area easily. Preview the changes before you print them.

185. Printing Arrangement: You can choose to print gridlines, row or column headings, print area, and collate the prints by pages, or by sets of prints easily.

186. PDF Outputs: You can choose to print to PDF, or save a report as a PDF to get professional looking Adobe PDF spreadsheets.

187. Publish: It is now possible to create and share rich visual reports and dashboards directly from the workbook data, using Power BI. You can upload your Excel workbook to Microsoft Power BI, or export the data only.

Once the data reaches Power BI, you can create beautiful charts, reports and visualizations, that can interact with each other, and be sliced in many ways. Further, data can be refreshed manually, or automatically, 48 times in a day. That’s like every 30 minutes.

Microsoft has also added the ability to process real time data streaming into Power BI visualizations.

## Customizing Excel

188. Customizing Toolbars: Standard toolbars are good, but you may not use all the buttons and it is often better to add your own options. The Quick Access Toolbar can be customized by adding or removing your favorite icons. Customize the Quick Access toolbar on Mac, or on PC here.

189. Customizing Options: Excel can be customized for a huge number of use cases. From performance to the layout, toolbars, formulas, data handling, languages, ease of access to advanced settings, everything can be customized.

A few useful things are to personalize Excel workbooks with your name, setting formulas to update automatically or manually, choice to use data models in pivot tables by default, enable or disable auto date grouping, proofing with a dictionary of your choice, auto saving every 10 minutes, and recover if lost.

For doing large scale data entry, you can change the direction of the cursor move, setup more than 2 decimal points by default (useful if you deal with currency exchange rates), and auto complete, auto fill options.

Customizing of toolbars, menus and enabling of Add-ins like PowerPivot, Data Analysis Toolpak are all done from the settings.

To learn more on the Option settings, check out Excel option hacks. Bill Jelen’s Microsoft Excel Inside Out book also contains details on such Excel options.

## Developer Controls For Building Dashboards

190. Enable the Developer Tab: By default, Excel does not enable the Developer Tab. You have to go to File > Options > Customize Ribbon, and check the Developer Tab to view the developer control and to write, record, edit or play macros.

191. Use Developer Controls: On the Developer tab, clicking on the Insert dropdown will show you a huge list of Form & ActiveX controls like the Combo button,
Drop downs, Radio Buttons, List boxes, Checkboxes, Buttons & Scroll Bars.

These can be extremely handy to create your own apps, extend Excel by creating your own Forms, Form processing using VBA, and can be used to create dashboards too.

Excel dashboards can bring in a lot of interactivity into reports, as the users love to interact with checkboxes, buttons and scrollers to change and view different options easily.

The developer controls are definitely worth exploring to build dynamic reports.

Learn to create dashboards using these videos, free resources and training programs –  Resource 1, Resource 2, Resource 3.

## Visual Basic for Applications to Extend Excel

Visual Basic for Applications (VBA) is the scripting language used to record or write Macros in Excel. With Macros, you can build automations that can be triggered by a button, or a change in selection in a check box.

192. Write VBA Code to Extend Excel: VBA scripting has allowed people to create utilities that can generate a Profit & Loss statement, or a Balance sheet from the keyed in data. Such simple scripts allow newbies to get more done, without having to learn programming, or multiple cumbersome steps.

193. Record Macros: Without writing any VBA, you can record the steps you want to automate, and the entire macro is recorded. You can give a shortcut key to the macro, and then play it any number of times.

194. Edit Macros: With VBA knowledge, it is easy to edit, debug and customize the recorded macro code. You can set breakpoints, pause execution, and examine variable in a VBA IDE to view the code execution step by step.

195. Assign Macros to Buttons and Controls: Create buttons using the Developer Contols, and assign macros to them. So upon a button click, the entire macro code can be run.

196. Store Macros in Personal Macro Workbook: Macros are saved in Excel with the extension of .xlsm. This Macro enabled Excel Workbook should be opened first before the macro can be played in this or any other open workbook. But when you save the macros in the Personal Macro Workbook (Each User gets one such file, which is placed in a hidden folder), it is possible to play the macros without opening the associated macro file. This file is open and available once Excel is up and running.

I learned VBA from Bill Jelen and from Reed Jacobson’s excellent VBA book. You can check out their books and courses online.

197. Data Analysis Toolpak: If you are keen to explore Statistical analysis using Correlation, Regression, Descriptive Statistics, and perform Analysis of Variance (ANOVA), T Test, F Test, or perform a Z Test, this amazing gem is like having a mini SPSS built within Excel. There are 19 different tools for analysis, which can help you check if variable are related or not, analyze the past, and forecast the future based on the trend analysis.

I do teach a Data Interpretation & Analysis Training in Singapore that covers a lot of Statistical Analysis Techniques using Excel. And if you are keen to study how to do statistical analysis online, you can check out link 1 , link 2 and link 3.

198. External Add-ins: You can add external add-ins that can extend the functionality of Excel. There are hundreds of Add-ins available from the Microsoft Office Store. Most of them are free. Visit Developer > Add-Ins. Click on My Add-ins to see what’s already available, and Store to see what you can install. There are categories for CRM, Data Analytics, Productivity, Maps, Sales & Marketing & several useful utilities too.

Some of the popular Add-ins are: Money in Excel, ASAP Utilities, Power Pivot, Ablebits Suite and Lucidchart (create flow charts). For more add-in choices, explore this site, and spreadsheeto.

199. Microsoft Power Pivot for Excel: allows you to go beyond the 1,048,576 rows limit of Excel, and load millions of rows and columns.

PowerPivot allows you to build a data model with multiple tables, which can come from different sources. You can then build relationships between them, and even create Measures using Data Analysis eXpressions (DAX) formulas.

200. Microsoft Power Map for Excel: Microsoft Power Map for Excel is a three-dimensional (3-D) data visualization tool that lets you look at information in new ways. A power map lets you discover insights you might not see in traditional two-dimensional (2-D) tables and charts. Learn to make a power map herehere.

201. Microsoft Data Streamer for Excel: Data Streamer empowers students with a simple way to move data from the physical world in-and-out of Excel’s powerful digital canvas. With a sensor, a microcontroller, and Excel, Data Streamer introduces students to the worlds of data science, and the internet of things (IoT).

Stream and analyze live data from a Arduino microcontroller into Excel with the Data Streamer add-in! Check out a detailed tutorial from Hackster.

## Conclusion

That’s quite a list.

To summarize, here are the most important Excel Features, Functions, Hidden Tricks & Tools you must learn in 2021 and beyond:

• Editing & Shortcuts
• Formatting
• Functions
• Tables
• Pivot Tables
• Charting
• Import & Clean Data
• Analyzing Data
• Sharing & Protection
• Page Layout & Setup
• Customizing Excel
• VBA
• Developer Controls

Now I’d like to hear from you:

Which Excel Feature or Function or Tools or Trick from this list was new to you?

Or maybe I missed something.

Either way, let me know by leaving a comment below.

Cheers,
Vinai Prakash

P.S: Thanks to Bill Gates & Microsoft for creating Excel. It is a life saver for most of us in the corporate world. I can’t imagine a world without Excel!

## How To Summarize Data in Excel: Top 10 Ways

You’ve got some data in Excel, and you want to summarize it quickly. There’s not much time, and your client or boss needs information right away.

Apply any of these Top 10 Techniques to Summarize Data Quickly with in-built Excel Functions and FeaturesYou’ll get instant results to satisfy most requirements fast.

Microsoft Excel has become the easiest way to analyze data quickly. Several Summary functions, Pivot Tables, What-If Analysis, and other powerful features & methods are available for your use within Microsoft Excel right out of the box. Learn them, and your data analysis will become a breeze.

## Here are the Top 10 Ways to Summarize Data in Excel Quickly

These data analysis & summarization tips will work in Microsoft Excel 365, Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007 & even Excel 2003. Further, they work on Excel for Windows as well as Microsoft Excel running on a Mac.

These Data Summarization Tips are listed in the order of the easiest to implement to the ones that need a bit more time. Some of the more complex data summarization methods will actually add more value to your data analysis.

1. Get The Data Ready For Summarization
2. Quick Summary With Auto Functions
3. Fast Analysis With Sort & Filter
4. Summarize Data With SubTotal Feature
5. Summarize Data With an Excel Table
6. Using Slicers to Summarize by different dimensions
7. Summarize With Excel Pivot Tables
8. Summarize Data With Excel Functions
9. Advanced Excel Functions for Summarizing Data
10. Summarize With Descriptive Statistics From Analysis Toolpak

You can apply the different ways to summarize data based on your familiarity with Excel.

The easiest methods of summarization are listed in the beginning.

And the Pivot Table technique is one of my favorite for a quick and dirty data summarization within Microsoft Excel. It always begins to give me numerous insights into the data.

We cover how to use the Pivot Table to Summarize Data in depth later in this article.

Let’s get started by exploring the different methods of summarizing data.

## 1. Get The Data Ready For Summarization

Before you begin your summarization, it is important to make sure that your original data is in a good shape.

Duplicate, a blank cell or missing values can often spoil your data summarization.

You need to make sure that the data range is correctly set up before you begin to analyze the data. Also, ensure there is no blank columns in between adjacent cells.

For each column, make sure that you have a short and unique column heading. Don’t leave any column without a heading, even though it may be obvious. Column Heading will make it easy to analyze data with any tool in Excel. This way the top row becomes the Header row.

### Remove any Duplicates.

Duplicate rows can often sneak in from the data capture sources. So whether you capture data from the Web, or SalesForce, SAP or load from Text or CSV files, the first thing is to clean up the duplicates. To remove duplicates, click within the data range, and go to the Data Menu.

### Data > Remove Duplicates.

That’s it, your data will now be cleaner.

### Get Rid of Blank Rows:

While blank rows make the data look more readable and easier, it is a bane for data analysis. We do not want blanks to sneak in and skew our averages and other statistical calculations. By sorting the data with the different column headings, the blanks will get separated either to the top or to the bottom. Then you can simply delete these rows if they do not contain any other data points.

### Don’t leave blank cells as blanks, specially if there is no value.

It is better to have a 0 than a blank value in any cell. For the text column, if the value is not known, it is better to have a NA (Not Available) showing up.

Having the data cleaned up is the first step in any data analysis. Now you can begin to apply the various data summarization methods.

## 2. Quick Summary With Auto Functions

The fastest way to summarize data is to calculate the Totals, count the number of entries, find out the average value, and figure out the highest and lowest values.

These 5 functions provide the vital stats of the data. These are the most basic and essential functions… just like a visit to a doctor starts with the nurse checking your vitals – height, weight and blood pressure.

These 5 numbers will provide a quick summary of your data. Here’s how to do this.

### Here’s How To Create a Summary Section on Top of Your Data

Calculate SUM: Click on the Autosum icon on the Home tab of Microsoft Office to activate the Sum function of Excel. Then select the data range of the column you want to summarize. Here’s an example:

Calculate COUNT: Click on the drop-down icon on the Autosum button on the Home tab of Microsoft Excel. Choose Count from the list. Then select the data range of the column you want to count. You can use the count function only for numeric columns like Salary, Sales, Quantity etc. using this function. So don’t try this on a text column like Country or Department.

Calculate AVERAGE: Click on the drop-down icon on the Autosum button on the Home tab of Microsoft Excel. Choose Average from the list. Then select the data range of the column you want to Average. You can only average the numeric columns like Quantity, Profit, ROI etc. using this function. Here’s an example:

Calculate Highest Values: Click on the drop-down icon on the Autosum button on the Home tab of Microsoft Excel. Choose Maximum from the list. Then select the data range of the column you want to choose for picking up the highest value. You can only pick numeric columns like Quantity, Profit, ROI etc. using this function. Here’s an example:

Calculate Lowest Values: Click on the drop-down icon on the Autosum button on the Home tab of Microsoft Excel. Choose Minimum from the list. Then select the data range of the column you want to choose for picking up the lowest value. You can only pick numeric columns like Quantity, Profit, ROI etc. using this function.

Calculating the Sum, Count, Average, Min & Max are the fastest ways to get started in your journey to view summary data quickly.

## 3. Fast Analysis With Sort & Filter

If you sort the data by any key column like Country, Department, Category, Product, Zone, Area etc., then it becomes super easy to analyze data.

To sort the data on any column, follow these steps. Go to

### Home > Sort & Filter > Sort A to Z or Sort Z to A.

This will sort the data in your chosen way (ascending or descending) for the column you are in.

Then click on the Filter icon. This will set up auto filters on top of all column headings. Now when you filter on any column, only values for that column will be visible. Unfiltered values are hidden, and visible data corresponds to the Filter selections.

After you have filtered for any particular selection, you can also highlight any numeric column, and see the summary being displayed at the bottom Excel bar.

At one time, you can see the Sum, Count, Average, Maximum & Minimum values from the selected range.

Now you can begin to summarize data for any selection, the way you want it.

## 4. Summarize Data With Subtotal Command:

If the data is already sorted, you are now ready to explore the Subtotal feature of Excel. This hidden gem has been languishing in Excel since the early 1990s, and very few people use it.

To add subtotals to your summary, first, clear all filters. And then Sort on the column that you want to add subtotal for.

The Subtotal feature is available on the Data tab of Excel.

### Go to Data > Subtotal.

Choose the function to perform (Sum, Count, average etc), for the numeric column, and group by the desired column.

As an example, to get the subtotals of the number of employees by each department, it should say: At each change in Department, Count, number of Employees.

Click OK, and you should see the subtotal rows from the data in Excel. In the end, you will also see the Grand Totals calculated.

You can clear the subtotals, and perform the subtotals again at any time. To clear the subtotals, go to Data > Subtotal > Remove All. The subtotals and the Grand total row are all removed completely.

## 5. Summarize Data With an Excel Table

Microsoft Excel 2007 brought a new feature called Tables, which are amazing at doing simple summarization from a table of data.

To begin, stay within the data range on the Excel sheet. Then click

### Home > Format as Table.

Select any colour you prefer, and click OK. Excel automatically recognizes whether the data selection has headers or not.

Now you have a new tab added to the Excel menu, at the end. It is called Table Design.

Select it, and check the Total Row checkbox. Now you have an additional total row added at the bottom of the data. Every column on the total row is a total calculator. Simply click on the dropdown showing in the total row cells, and choose an appropriate summarization – sum, count or average. Now that column will show a total sum or total count or total average of that column.

The great thing is that now if you filter the list, the totals will change dynamically. No need to call the Subtotal function every time. This technique of data summarization is better than the manual methods of subtotal.

Begin using the Table features if you haven’t done so already.

## 6. Using Slicers to Summarize by different dimensions

Beginning Excel 2010, Microsoft added the Slicers functionality which takes the Tables to greater heights.

Instead of filtering each column one by one, you can now create a slice of the data from the Excel spreadsheet at any time.

### Go to Table Design > Insert Slicer > Pick the column of your choice that you want to filter on.

Now you can click on any value in the slicer, and the data is instantly filtered. You can make multiple selections, by holding the control key.

And more than 1 slicer can be added, giving you multiple combinations of slices. The total row updates automatically as each selection is made. This is truly slice and dice of data, allowing you to summarize data in Excel spreadsheets just by using a mouse. No formulas or lengthy steps are involved.

## 7. Summarize With Excel Pivot Tables

Pivot tables have been in Excel for over 30 years. They are the most used feature of Excel, as it allows for business analysts & managers to manipulate and analyze data in countless better ways.

A pivot table is actually a summary table, which allows you to slice and dice the data by different dimensions.

Pivot tables are easy to get started with.

To create a pivot table, Stay within your dataset. Then go to Insert > Pivot Table. The entire dataset is selected. Click OK and a new pivot table is created in a new worksheet by default.

Click on the column that you want to summarize on, from the pivot table field list on the right side of the screen. In our example, we check the Department and Sales amount from the field list. Instantly, the total sales per department are calculated and populated in the pivot table on the left. Notice that Excel generates a unique list of departments, without any duplicates.

To visualize both the Sum & Count of the data points, you can drag one more copy of the Sales into the Values Area section. Then right-click on the value, and choose

### Summarize Values by > Count.

Similarly, you can change the calculation type, and pick up Average, Max, Min. This way, you can have all the vital statistics about your data summarized by each department.

Additionally, to see the percentage of sales done by each department,

Right Click on any numeric value in the pivot table, and choose Show Value As > % of Grand Total.

This will instantly calculate the % of contribution done by each department. The grand total will show 100% of the sales.

It is a good idea to sort the % of Grand Total value from highest to lowest by value, showing the highest values on the top.

You can then add a further selection in the rows or columns, to get a 3D view of your data. As you can see, Pivot Table is a powerful tool that can get the analysis done the fastest!

As you learn more about the Pivot Tables, you’ll realize that they can be used to summarize data from a single worksheet or multiple worksheets.

A pivot can even summarize data from multiple workbooks too. This is a must-learn feature of Microsoft Excel. You can attend an Advanced Excel Training in Singapore at Intellisoft, where I teach this class.

## 8. Summarize Data With Excel Functions

To get the most flexibility, you can actually write your own summary functions within Excel, by using the following formulas.

We have already covered the Autosum features of Excel which generate the Sum, Count & Average. Now we will look at how to write these functions manually.

To Sum a range of data, use =SUM(range) in the formula bar.

To Count a range of numeric data cells, use =COUNT(range). This generates a numeric count.

To count a range of alpha-numeric data cells, use =COUNTA(range). COUNTA can be used to count both numeric and non-numeric data.

To find the average of any data, use =AVERAGE(range).

## 9. Advanced Excel Functions for Summarizing Data

The real power of Excel functions is when you go beyond the basic summary functions and do some advanced data analysis.

You can use the following Excel formulas

Sum the data conditionally. For example, if we want to summarize the monthly sales only for a particular country, we can use the Excel Sumif function:

=SUMIF(country data range, select_country, monthly sales data range)

Similarly, to count the number of products sold by a country, we can count by using the COUNTIF function of Excel

=COUNTIF(country data range, select_country, monthly sales data range)

And average sales per country can be analyzed by using the following formula of AVERAGEIF.

=AVERAGEIF(country data range, select_country, monthly sales data range)

For multiple, if conditions, you can use the sumifs function to summarize data by your chosen selection. These functions can really cut down your data analysis time when you have large amounts of data to summarize.

## 10. Summarize With Descriptive Statistics From Analysis Toolpack

Finally, Microsoft Excel has the Data Analysis Toolpak, a hidden Statistical Analysis tool, that can calculate the Median, Standard Deviation, Variance, Analysis of variance (ANOVA), and much more in a single click.

To enable the Data Analysis feature in Excel, you must go to

### File > Options > Add Ins.

Then select the Data Analysis ToolPak if it is inactive. You might have to click on the Go button at the bottom. Choose the Toolpak, and click OK. This will add a Data Analysis button on the Data tab of Excel, at the end. Check it out. Once this button is enabled, it stays active, and you can use it subsequently anytime.

To use this Data Analysis ToolPak, go to

### Data Analysis > Descriptive Statistics.

Select the Entire numeric column that you want to analyze in the Input Range. Check the Labels in First Row checkbox if your data has a header.

Then check the output box radio button, and key in a cell address where you want the summary statistics to be generated. Check Summary Statistics, and click OK.

The full descriptive statistics are displayed instantly. This final result is a detailed Statistical Analysis of your data.

## Multiple Ways to Summarize Data in Excel – Conclusion

There are so many different ways to summarize data in Excel. Mastering them will improve your data analysis skills, and you will be on your way to huge success, by taking action on the insights gleaned from your data analysis.

Go ahead and try them out.

Each technique is a gem, and adds to your skills in Excel data analysis.

Cheers,
Vinai Prakash

About Vinai Prakash: Vinai is a prolific speaker, author, entrepreneur and coach on the topics of Data Analytics, Project Management, Advanced Excel Techniques, SQL, Python, Data Visualization with Power BI & Creating of Excel Dashboard and several other soft skills. He is one of the Best Trainers for Data Analytics Training and is highly sought after for his advice. Contact Vinai for your next Data Analysis Training.