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
How to Use Relative & Absolute Referencing To Your Advantage
Begin Using Range Names & Get Rid of Absolute Referencing
Master IF Function For Quick Decision Making
Conditionally SUM a few rows only, based on a criterion with SUMIF
Count only a few cells, based on any logic you can think of, by using the COUNTIF function
Go Beyond Formatting Tables to Actually using them For amazing Insights
Stop using Average. Use the Median Function to get a better sense of your data.
Use Special Data Types built into Excel to get Demographic, Weather, and other useful information
Learn to Combine Cells to Make Better Looking Reports
The Amazing Flash Fill can Do wonders to save you time
Learn some Date Magic in Excel
Find the difference between any 2 dates, with or without counting the Weekends – your choice!
Convert any data from Centigrade to Fahrenheit, or centimetres to inches. There are hundreds of free conversions built into Excel for you.
Create Beautiful Printouts & Reports With Beautiful Printing Options within Excel
Move around easily by Freezing or Splitting Sheets and Views easily
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.
Hide or Show Grid Lines to make Excel look like Word or PowerPoint. Plus it makes better output and a pleasing view too.
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.
Use Color To Enhance your experience working with multiple sheets, and create a livelier Workbook that looks amazing.
Before you analyze anything, remember to Remove Duplicates in a single click
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.
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.
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.
I’d love to get your comments on these tips. Or if I missed any of your favourite tips, do let me know. Your comments and suggestions help me to get ideas for future videos and articles.
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.
A Pivot Table is Created
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
A refresh of the pivot table is necessary when the underlying data changes,
A source data value is corrected or updated at a subsequent time,
Additional Rows are added in the raw pivot table data source,
Existing Rows are deleted in the original data used as a source for the Pivot Table, or
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.
Refresh Pivot Table Using Right Click
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.
Change Data Source in the Excel Pivot Table.
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.
Pivot Table Refresh under Analyze Tab
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.
b. If your pivot table is based on a Table, like Pricing_Table, Table1, and has been converted into a Table, the job is much easier.
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.
Go To Analyze Tab, and select the Pivot Table Options
To refresh the pivot table automatically upon opening the Excel file, go to the Pivot Table Analyze tab, and look for Pivot Table Options.
Refresh Pivot Table Automatically upon opening the Excel File
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.
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:
Right-Click inside the Pivot Table & Choose Refresh.
Redefine the range of the source data by clicking on the Change Data Source button.
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 Pivot table in Excel 2013:
First, make the Pivot Table.
Right-Click inside any single Pivot Table & Choose Refresh.
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.
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.
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.
Then Click Insert > Pivot Table. Click OKto select the entire data.
A new pivot table is created in a new Sheet in Excel 2007.
Check the Category columns to go into Rows. Check the Numeric values you want to summarize in the Values Area.
Then go to the pivot table by clicking anywhere inside the Pivot.
Now to Refresh the Pivot Table created in Excel 2007.
Right-Click inside the Pivot Table & Choose Refresh. The data in the pivot will be refreshed.
Redefine the range of the source data by clicking on the Change Data Source button from the Pivot Table Options Tab of Excel 2007.
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 Refreshon the Pivot Table Options Tab.
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:
Right-Click inside the Pivot Table & Choose Refresh.
Redefine the range of the source data by clicking on the Change Data Source button.
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.
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!
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.
Get The Data Ready For Summarization
Quick Summary With Auto Functions
Fast Analysis With Sort & Filter
Summarize Data With SubTotal Feature
Summarize Data With an Excel Table
Using Slicers to Summarize by different dimensions
Summarize With Excel Pivot Tables
Summarize Data With Excel Functions
Advanced Excel Functions for Summarizing Data
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.
Ensure Proper Column Headings.
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.
Using Subtotal to Summarize Data in Excel
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.
Results of Performing a Subtotal in Excel
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.
Results of Total Row in an Excel Table
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.
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.
Descriptive Statistics Result from Analysis Toolpak
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.
Advanced Excel skills are required in most managerial & analyst roles. Yet most people have no clue of how to learn Advanced Excel fast. They don’t know the key features of Microsoft Excel that can help them save time and money.
It’s about time you pick the Most Important & Useful features of Excel quickly. This post coveres the following topics in detail.
What is Advanced Excel,
Why should you learn it,
How to learn Advanced Excel Fast!
Welcome To This Short Guide to Being an Expert in Advanced Excel Skills
Every organization uses Microsoft Excel in their day to day work. Most employees & managers know Excel to some extent. And they are able to survive the day by doing things in one way or another… often the long and inefficient way.
Learning to use Microsoft Excel well goes beyond the basics. That’s where the Advanced Excel skills come in handy.
Microsoft has bundled in hundreds of useful functions and features, that can do wonders, save a lot of time, and improve your efficiency & productivity.
Each new version of Excel is packed with ever richer functionality, and provides more ways to use Excel to its utmost at any workplace.
Microsoft is striving to add those features that can simplify complex things, and make it easier to do data entry, perform computations, and even analyze data & present the insights into actionable information useful for clients and colleagues or the management in nicely created reports and dashboards.
So how come very few people are familiar with these Advanced Excel Features & Functions?
Do You Know These Advanced Excel Tricks?
Partly because Microsoft is a software company, and not so much an education company. They add new Excel formulas, features, shortcuts, buttons, charts types, and options, but Microsoft doesn’t spend the time in educating everyone about these new enhancements in the time they deserve.
Microsoft simply blog about it, updates the documentation, and then wait for you to figure it how somehow. Most greatly useful features languish, forgotten in the documentation, hardly ever used…
Also, partly to blame is our education system, which does not start teaching us the key Excel skills that are essential in the job. Almost every student now works on a laptop or a tablet, using documents & spreadsheets for every report, presentation or assignment they do. But our schools often leave you to figure how to be productive with these basic tools.
Very few schools or colleges have mandatory Excel or Word Training. It’s no wonder that when a fresh graduate joins the workforce, they often take ages to do simple things, stumbling and faltering along their journey to do even basic things in Excel, let alone the Advanced Excel Techniques that are required to be productive.
Why Should I learn Advanced Excel?
That’s a common question for people using Excel at workplace. If you don’t even know what Excel can do, why will you be interested in learning it. You need to see the features to believe it, and to see your own blind spots.
Do You Know These Advanced Excel Tricks?
Increased Productivity With Advanced Excel Tips & Tricks
If you care about getting the job done faster, without any errors, then it is in your interest to improve your competence in Excel. You don’t have to learn all the 500 plus functions to master Excel. In fact, you can already be more productive if we can know and use more than 10-15% of the Advanced Excel features and functions we have listed below.
Better Job Prospects With Better Excel Skills
New job prospects & Career switch options also open up for those who can manipulate and juggle data easily in Excel. Many higher end analyst jobs in the financial and accounting, sales, marketing, management & consultancy areas require good analytical & decision making skills.
Better Presentations With Excel Charts, Reports & Dashboards
Plus, With Advanced Excel Charts & Reporting features, you can be a star in the boardroom too. Most client presentations will need some amount of data and analysis to be presented, which can easily be analyzed and tabulated in Microsoft Excel, provided you know how to do it quickly.
So now you know the key reasons why you should learn Advanced Excel, you may be wondering, what are the key features of Excel that can considered as “Advanced“.
What Really Are Advanced Excel Skills?
Knowledge of multiple useful features and functions, plus the ability to use them at short notice is what we can call as Advanced Excel skills. To name a few, you must be able to know and perform the following things in Excel well.
A Listing of Key Excel Skills You Must Have
FASTER SETUP & DATA ENTRY WITHIN EXCEL
Setup Columns Quickly using Auto fill options. For example, you can fill Months or Quarters easily by filling in just the first value. Similarly, you can generate sequence numbers from any starting point to any ending number.
Do Quicker Data Entry by using Auto complete of repeating values as Excel picks up the filling values using pattern recognition
Generate Sequence Numbers quickly With Auto Fill & FlashFill options
LOADING EXTERNAL FILES & DATA
You must be able to Bring External data into Excel from any kind of source – be it Text files, CSV files, XML, Web Data or Database files. Plus, Excel now makes it easier to bring in data from the Cloud Apps like SalesForce, Zendesk, QuickBooks & over 200 app integrations, from PowerQuery, now built into Excel, from version 2013 onwards.
All is not good just by loading the data. You will have to clean & de-duplicate it. Fill in blanks, handle null or missing values, and then fix the dates to become useable. You’ve often got to convert dates formatted as YYYYMMDD or DDMMYY into something that’s more humane – DD-MMM-YY or MM/DD/YY. Obviously the actual settings will depend on your country, regional settings & preferences. But it is often required. This requires you to use PowerQuery, or use Text functions to extract the date, month or year from strangely formatted dates.
EASIER DATA FORMATTING
After cleaning the data comes the job of making it easier to read and identify the key data points.
Here comes the Data Formatting options. Formatting Data makes it easier to read and present data. With Conditional formatting options, it is easier to highlight data based on any simple or complex condition or criteria. By highlighting data, you can make it easy to the winners and losers & spot issues and errors. Highlight the highest values, lowest values, values between a range, values outside a range, or set up your own rules, based on calculations.
LEVERAGE ON IN-BUILT EXCEL FUNCTIONS
Good knowledge of Advanced Excel Functions is essential to get more mileage out of Excel. Microsoft’s Excel functions are divided into multiple categories:
Categories of Microsoft Excel Functions
Lookup Functions like VLookup, Index, Match, Offset, Indirect allow you to find anything from within Excel tables and Master data. Pick the employees name based on Code, or find out who secured the highest or lowest sales numbers.
Statistical Functions like Median, Mode, Standard Deviation, Variance allow you to analyze data statistically. You can find out the median, standard deviation or variance, allowing you better insights into the data as to what happened, why it happened, and what is most likely going to happen.
Analysis Functions like Correlation & Regression, Trend Analysis & Forecasting further the statistical functions into forecasting, and allowing you to make better projections, and better decisions based on the happening trends.
Excel Formula Groups
Logical functions like If, Sumif, Countif, Iferror allow you do things conditionally – check if a condition is met, add or count based on conditions being met or not met, and even check and handle errors from happening.
Date & Time functions for finding todays date, time, difference between dates, hours, year, month, days, weekdays. This allows you to do time based calculations, buckets of date ranges, and even create ageing analysis based on range values.
Database functions that treat the entire data set as a database, and allow you to aggregate results using Dsum, DAverage, Dmax, Dstdev. This can be useful when working with big data
Text Functions to extract, clean and manipulate data – Left, Right, Mid, Char, Len, Fixed, Trim, TextJoin help in fixing erroneous data, and picking certain batch codes, lot numbers, region or product codes from within serial numbers.
Financial Functions like PV, NPV, PMT, IRR, Accrued Interest, Future Value, Mirr etc. are useful for analyzing the current, present and future value of things. Interest calculation, accruals, monthly installments, interest rates etc. can be easily worked out by using these advanced Financial functions of Excel.
PIVOT TABLES FOR QUICK ANALYSIS
Use the Pivot Table feature of Microsoft Excel, which is the fastest way to get some high level summary from your data set. Pivots allow you to slice and dice the data in numerous ways, and analyze it using different dimensions easily, without writing any formulas or macros. A pivot table is the first thing people turn to when they want some quick analysis or summary on the data.
With the help of Slicers, Timelines, Report Filter Pages, you can look at the same data in multiple ways, multiple dimensions, and in multiple filter flavors.
These are all great ways to analyze information quickly with Excel – a strong reason to learn Advanced Pivot Table techniques of Microsoft Excel.
Use Pivot Tables For Quicker Analysis of Data
In fact, knowledge of Pivot tables is often tested in interview questions for jobs requiring a good amount of business analytics. Even in days where most companies world class ERP software with hundreds of canned reports, often raw data is extracted from these ERP packages and combined with external market data, manual forecasts and then analyzed using Pivots.
Competence in Advanced Pivot Table analysis techniques are are must if you want to get into business analytics. Strangely, for the amazing things pivots can do, they are surprisingly easy to master. I often see managers and senior executives surprised at the simplicity, and lament that they missed out on this easy feature for the past several years, relying on junior executives to churn out the reports.
No harm in getting the ground staff to run the reports, but sometimes they do not have the acumen or sensory acuity of understanding the big picture. The juniors often report the obvious, without being able to get that helicopter view of the data.
Pivots are the easy, low hanging fruit that you should begin with, for it brings the biggest bang for the buck. You can easily master it in an afternoon, or in a pivot table masterclass and win an edge with this winning Advanced Excel trick up your sleeve.
CHARTS TO VISUALIZE INFORMATION
Excel Dashboards To Visualize Information Quickly
Create Charts from raw or summarized data to visualize the information quickly. Multiple columns and thousands of rows make it very difficult to see the big picture and spot a trend.
How To Convert Excel Data into Charts
A visual is worth a thousand words. An Excel chart can depict the past sales of many months or quarters, returned products or problem tickets created/solved each month, and it becomes much easier to spot a trend by looking at a high level chart more than by looking at a sea of rows and columns
There are multiple kinds of charts in Excel that can make boring data look stunning. Choose from Bar & Column charts, Pie & Donuts charts, Waterfall charts or Line Charts. You can also create combination charts showing column and lines at the same time, allowing you to measure 2 different metrics at one time.
Excel charts are easy to master, and there’s a lot to choose from. You can easily format them, add legend, titles, colors, and just about tweak every aspect with a mouse click. Mastering such Excel Charting tips can take you far in the boardroom, with better looking charts & visuals.
MACROS IN EXCEL TO RECORD & AUTOMATE STEPS
Macros are the one Advanced Excel Feature that allows you to extend Microsoft’s products and take them to newer heights. You can create your own functions & automations in Excel to perform multiple steps in a short time, at a single button click.
Macros are heavily used in Banks, financial institution, and in accounts departments of almost each and every company. They are the staple of the data enthusiasts who like to do things just once.
Excel VBA Macros allow you to load new data automatically each month, collate and tabulate multiple sheets & multiple workbooks, and create reports & charts automatically for each new period.
Macros are recorded or written and edited in a special language created by Microsoft – Visual Basic for Applications (VBA) in short.
While learning VBA may take some time, this is the secret weapon that separates the wizards of Excel from the amateurs. Once an Excel macro is written and tested, it can easily be deployed to the masses. Your colleagues and users needn’t know the complexities or the logic of how things are done.
Macros Automate Routines & Execute Faster
For example, you could create your own custom Financial Accounting Software, just by using Excel. Using Forms, you can get the users to key in the sales, expenses, and generate invoices or receipts at a click. And a Cash Flow Statement, a Profit & Loss Statement, or a Balance Sheet could be generated at any time, collating all the data keyed in so far.
This allows the users to get more done with Excel, and everyone doesn’t have to learn the technicalities of generating such reports at any time.
VBA Macro writing and editing skills are considered Advanced, because it requires you to learn the specific way Excel refers objects like workbooks, sheets, rows and columns. VBA is a full blown programming language – allowing you to write loops, conditions, procedures, functions, and tag them to buttons, mouse movements etc.
This one advanced Excel skills can make you indispensable in the whole department. I have known several people who have a clout in the company because of their deep knowledge of the system, and that they have written the backend systems that the company uses in its day to day operations. Such deep knowledge is always in demand.
SHARING & PROTECTION OF DATA
Today Microsoft Excel is improved and enhanced to allow multiple colleagues and friends to work together on the same file. You can track changes of who did what, and you can even protect the information in such a way that only those authorized to see or edit can do so, protecting information from prying eyes.
Sharing & Protection Options in Excel
With Advanced Data Protection techniques in Microsoft Excel, you can hide sheets, write protect them to make them view only, or allow only certain rows, columns or cells to be editable. This gives a tremendous advantage while working with multiple people and multiple sheets.
With the integration of OneDrive, you can truly collaborate with your team, having multiple edits and track changes as they happen. It is much easier to edit, pick the changes you like, or remove/revoke changes that you do not approve of.
Share & Protect Data Accurately With Amazing options
If you haven’t visited the Review menu of Excel, you’d be surprised with the multiple options available under the hood, that allow for Collaboration, Sharing, Editing & Protection of Documents.
SIMPLE, COMPLEX, ADVANCED TEMPLATES
Almost everything you do in Excel has been done before. So if you are making a calendar, or a cash flow report, a monthly report, attendance report, Result of Students, Invoices or Statement of accounts, Expense claims or Employee Leave forms for the team, there is a ready made template to do so.
Not just one, you have hundreds of templates to choose from. These ready made templates are available to any Microsoft Office user to use and save time.
Save Time With Sophisticated Excel Templates
On top of this, you can create your own company wide or departmental templates, that can be used month after month, quarter after quarter without any changes. Consolidation becomes a breeze if you all use the same template.
Plus, tracking & merging of multiple changes can be done with the hidden Track & Merge option. You can’t find this button in the standard toolbar, and need to enable it separately. A golden gem of a function. Even seasoned pros have been unaware of this super cool advanced excel functionality.
Print Beautiful Reports & Charts With Advanced Excel Techniques of Page Setup
With Advanced Page setup, you can decide what you want printed and what not. You can add headers, footers, page numbers, logos etc. straight out of the box. But Excel goes beyond this into giving you fine control over the rows, columns, and area that will be printed.
Excel Page Setup Options
You can control whether you want to print grid lines, draft copy or are you printing the final copy. The current date or time can be printed too, along with a lot of meta data – file names, sheet names, page numbers etc. provide you with a fine control. Printing order – collated or by page, and auto fit to handle orphan printing of some columns can be a real paper saver.
Trees will love you for learning and using the page setup options within Excel well.
ADVANCED SORTING & FILTERING
Almost everyone figures out how to sort data on any column – in either ascending or descending order. But Excel allows you to perform custom formatting – based on your values, and your defined order.
Sorting for multiple, unlimited levels is a boon too. This breaks the limited 3 level sorting of previous versions, allowing you to sort as many levels as you please.
Filters have improved much in the past 10 years. Now you can easily filter the Top 10 or Bottom 10 values, filter by color, filter by values, and even filter by specific text or dates. There filter feature helps you to actually define the period of data or values that you want to focus on, and eliminate the rest. Master this simple feature, and get to your important data points quickly.
Filtering the values to focus on at any given time removes clutter and makes it easy to visualize information in Excel.
FORMATTING DATA INTO TABLES – A SUPER SIMPLE WAY TO ADD MAMMOTH FUNCTIONALITY, For Free!
Microsoft added the functionality to treat data as a table in Excel 2007. But the name they gave to the button that begins this functionality is a showstopper.
Format as Table in Excel
When you look at “Format As Table”, all you’ll see is multiple coloured data tables. And many an Excel enthusiasts pull away, thinking its just colors… They fail to learn the mammoth hidden functionality of Excel beneath this mis-labeled button.
But once you go over this hump, you are on your way to explore gold with Formatted Tables.
Since 2007, Excel Tables have come a long way. Now they have smart range names, auto fill and auto spill ranges, and use range names in calculations. There are several magic cells in Excel Tables, that can perform additional tasks too.
Becoming adept at using Table features of Excel will speed up your analysis.
Further, tables can be filtered, sorted, sliced. For date based data, you can add a Timeline, which is a slicer based of dates, but much better.
There you have it… These are just some of the advanced features of Excel. Learning about additional things like Custom Formatting, Range Naming, Working With multiple Worksheets, or combining data from multiple workbooks, consolidation, What-if analysis, Scenario Manager, Data Tables, Data Validation etc. will take your Advanced Excel knowledge to a much higher level. There is simply too much functionality to talk about in one article.
Suffice is to say that if you want to get done more, cheaper & faster, then learn some of these Advanced Excel Features, pronto!
How long does it take to learn excel?
There is no simple answer when you are beginning to learn a new skills. To learn Advanced Excel tricks is going to be the same too. It also depends on your interest, commitment, and the amount of time you are wiling to spend in learning it.
I would say that learning Advanced Excel tips and tricks is more of a journey.
You learn some concept, begin to apply it, and then learn some more. While learning, you will come across new concepts, see new problems, and seek newer ways to handle these challenges. This step by step approach will open your eyes, develop a keener sense of Excel capability, and develop your Excel muscle step by step, day by day!
I have been a student of Microsoft Excel for the past 30 years. And still I find new things, and new ways of doing the same things. It has been a fun and exciting journey and I love challenges in Excel.
Every once in a while, someone will send me a long and complex looking formula, and dissecting it, understanding it, and learning from it makes us all better. Helping others with their Excel has been one good way that has helped me grow my Excel competence.
Can you teach yourself Excel?
Yes, of course you can teach yourself Excel. And you can even learn Excel at home. All the same, I would recommend a step by step approach in self-learning of Advanced Excel . Based on your interest, it is safe to divide Excel Training into a few sections.
First begin with understanding Range Names, Conditional Formatting, Tables and Pivot Tables.
Then pick up more complex Logical & Lookup Functions to delve deeper.
After this, you can then focus on Financial or Statistical or Date Functions based on interest or usage within your organization.
No point in learning Excel for the sake of learning. You must apply it first. So find a challenging situation within your company or department, and seek to build a solution to fix it is a good way to get started in building your Excel muscles.
It is safe to say that if you begin learning Excel techniques by using this method, in 2-3 months you will see a big improvement in your understanding of Excel.
And in 6 months time you can be at a pretty advanced level in your Excel usage and your added competence will give you more confidence within your organization.
How Can I Learn Advanced Excel Faster?
If you find this route of self-learning difficult or too long, it may be better to develop and learn advanced excel skills in a more systematic and methodical manner.
I would recommend going for a formal training on Microsoft Excel. Based on your level, and interest, you can choose an Advanced Excel Training in your city or suburb. This is the best way to learn advanced Excel.
Most Advanced Excel courses are 2-3 days long, depending on their coverage.
Make sure you join a workshop where lots of exercises and hands-on is provided, and not just a demo of Excel functionality.
It’s because we all learn better by doing it ourselves, rather than just watching someone else do it.
Plus, doing the exercises yourself will expose you to the common pitfalls and mistakes, which can then be rectified with the trainer/facilitator, and with worked examples and samples, you will gain a better understanding of the topics.
Since 2003, ExcelChamp & Intellisoft Systems has been providing short courses on Excel at all levels:-
We also have Excel training for creating management charts and reports – called the Excel Dashboard MasterClass. For those looking to automate Excel, you may choose to enroll in the 3 Day Practical, hands-on, VBA Macro Programming workshop.
What is Advanced Excel Training?
A short Excel training of 2-3 days will cover the key concepts. In such a formal Excel training program, the notes, handouts, exercises & sample examples are readily available for you to begin using immediately.
I personally find learning anything in a short course to be more beneficial. It covers the concepts quickly, and then I can focus on the details based on my interest areas.
Plus the best thing for a formal training is that we have a trainer or facilitator available to ask questions along the way.
Learning in a sheltered environment is better as newbies often stop when they stumble upon initial concepts and often give up completely.
What are the Topics in Advanced Excel Training?
Make sure your chosen Excel training at least covers the most important topics, at the very least. Our 2 day Advanced Excel course in Singapore covers all these, and much more, with practical examples and exercises.
If you are looking for the Best Excel training, look at some key things to consider, like time, speed, convenience & availability. If you can attend classroom training, I’d absolutely recommend it.
At ExcelChamp.Net, we provide both Classroom and e-learning via Zoom classes for Advanced Excel. You can choose from several dates available. These are extremely popular, and we have over 20+ years of running Excel classes.
All of our trainers come with years of industry experience. They have a passion for training and sharing their tips and tricks of Excel with you. You’d absolutely love our best advanced excel training course.
How Do I Get Excel Certified?
Most Advanced Excel courses will come with a certificate of Attendance. This is sufficient for most people, for real competence in Excel is more important that a certificate. Intellisoft offers such certificate of attendance for all of its 2 day Excel courses & workshops in Singapore.
To boost your resume & build your LinkedIn profile, a well recognized official certification in Excel is required!
There are 2 major certifications you can choose from
Microsoft Certified Professional (MCP) in several Microsoft technologies. For Excel, you can go for the Microsoft Office Specialist (MOS) certification. There are 2 levels – Associate and an Expert level. Better to go for the MOS Excel Associate level first, which is an easier Excel Exam. Then opt for the Expert MOS certificate in Excel. Beware that Microsoft certifications are pretty expensive.
Another option is to go for the extremely popular certification from the International Computer Driving License (ICDL Foundation). In Asia, this certificate is available at the Foundation and Advanced levels.
At Intellisoft Training, we are the official partners with Microsoft, and ICDL Asia, and are authorized to administer both the certifications in Excel. You can choose the Microsoft one, or the ICDL one, based on your preference.
The ICDL Certification is widely recognized by the Singapore government ministries. It is a tad cheaper in terms of the exam assessment fee too.
Plus, the Singapore government subsidizes the Advanced Excel Training Fee & Certification fee, allowing permanent residents and Singapore citizens to get certified in Advanced Excel skills. It is considered an Essential skill for office use, and is considered a must have for all office executives, analysts & managers. Do contact us for more information on this.
Next Steps: For Enhancing Your Spreadsheet Skills With Advanced Excel Training
With so much demand for Advanced Excel skills, so rich & useful functionality in Excel, and an easy path to victory with Excel, what are you waiting for. Grab the next chance to explore Excel in greater depths. Enroll in a classroom training, an e-learning training, or attend a Zoom class. Whatever it takes, just get started, right away.
Excel is the secret Swiss Army knife in your data analysis toolbox.
Just imagine, how far you can go with a proper, formal Advanced Excel Training! The opportunities are limitless, and so is your future!
Cheers, Vinai Prakash Founder & Master Trainer of ExcelChamp.Net
Enter your name and email & Get the weekly newsletter... it's FREE!
Learn Simple Tricks To Be an Excel Expert!
BONUS: Get 30 Free Tips & Excel Shortcuts eBook Free!
Your information will *never* be shared or sold to a 3rd party.
You can use the following Excel formulas
