Whether you are new to the world of Excel or just trying to improve your Excel skills, look no further.
Rank up your skill from novice to pro by following our guides and methodology in this Free Excel Training. Keep reading to explore the different lessons covered here!
As you finish these lessons catered for you, you are guaranteed to get accustomed to the ways of Excel. Be it swiftly finding the solution to your problem, or an overall increase in speed while working on Excel.
Complete every task with ease with this Free Excel Training. Just scroll down and start watching and learning!
1. Excel Basics
2. Excel Formulas & Functions
3. Formatting in Excel
4. Excel Tables
5. Excel Charts
In this article, we will be covering 75+ lessons in these 10 Modules for you to become more efficient at Excel!
1. Excel Basics
2. Excel Formulas & Functions
3. Formatting in Excel
4. Excel Tables
5. Excel Charts
6. Excel Pivot Tables
7. Excel Macros & VBA
8. Power Query
9. Power Pivot
10. Power BI
Let’s look at each of these modules one by one!
Excel Basics
Lesson 1: Getting started with Excel
In this Excel tutorial, I will cover the Basics of Excel that you need to know to get started with how to use Excel. The outline of the topics that will be covered are as follows:
- Opening an Excel Spreadsheet
- Understanding the Different Elements of an Excel Spreadsheet
- Entering Data in an Excel Spreadsheet
- Basic Calculations in an Excel Spreadsheet
- Saving an Excel Spreadsheet
For demonstration purposes, we have used Office 365 here!
Related Articles:
How to Use Excel for Dummies
141 Free Excel Templates and Spreadsheets
Lesson 2: 333 Excel Keyboard Shortcuts
Excel keyboard shortcuts are extremely useful as they can make you faster and more efficient while working on Excel.
In this video, we have compiled an exhaustive list for you so you can apply it to your Excel worksheet straight away:
Frequent Keyboard Shortcuts
Inside the Ribbon Shortcuts
Cell Navigation Shortcuts
Formatting Cell Shortcuts
Making Selection Shortcuts
Working with Formula Shortcuts
Ribbon Tab Shortcuts
Power Pivot Shortcuts
Function Key Shortcuts
Drag and Drop Shortcuts
Macros and VBA Shortcuts
Pivot Table Shortcuts
Power BI Shortcuts
Other Shortcuts
Below we have created a FREE downloadable PDF cheat sheet and a fully searchable list of the 333 Excel Shortcuts for both Windows and Mac for you!
DOWNLOAD THE 333 EXCEL SHORTCUTS PDF CHEAT SHEET
Excel Formulas & Functions
Lesson 3: MOST POPULAR Excel Formulas and Functions
Excel Formulas & Functions will allow you to quickly analyze your data in many forms and will increase your Excel skill exponentially.
After completing this tutorial, you will Master the Most POPULAR Excel Formulas and Functions: VLOOKUP, INDEX, MATCH, SUMIF, & IF in only 1 HOUR! You will also learn:
- A proven step-by-step guide to creating Excel Formulas in SECONDS!
- The limitations of using a VLOOKUP and why INDEX & MATCH is better!
- 3 TIPS that the Excel experts & MVPS use that will enhance your Formula knowledge!
Related Articles:
101 Advanced Excel Formulas & Functions Examples
Lesson 4: Learn VLOOKUP formula in 4 easy steps
Excel`s VLOOKUP function is arguably the most used function and the most talked-about function in Excel. It can be used to extract values from a table and use them in your custom reports and analysis.
In this tutorial, we will cover how to use VLOOKUP in Excel using 4 easy steps!
- Enter VLOOKUP formula
- Enter the value you are looking for
- Enter the table where you going
- Enter the column number containing the result
Watch this Tutorial to Master the VLOOKUP function and use it with confidence!
Related Articles:
VLOOKUP Approximate Match in Excel
VLOOKUP in Multiple Sheets
VLOOKUP in Multiple Columns at once
VLOOKUP using Multiple Criteria
Lesson 5: 6 brand new functions in Office 365
This is your one-stop-shop for learning all the new Excel formulas in Office 365:
- Filter Formula – Filter your table data with an Excel Formula
- Randarray Formula – Generate random numbers in an array
- Sequence Formula – Generate a series of numbers with an Excel Formula
- Sort Formula – Sorts a table based on a column and order specified
- Sortby Formula – Sorts a table based on the column(s) specified
- Unique Formula – Gets the unique values of a list
Watch this video lesson to learn the following formulas in detail!
Related Articles:
There are times when you need to get the number of characters within a cell in Excel. Thankfully this is very easy to do with Excel’s LEN (Length) formula!
You can use the LEN formula in Excel:
- To count all characters in a cell, including letters, numbers, special characters, and all spaces.
- As a data cleansing technique to find leading or trailing spaces or can be nested with other TEXT functions.
- To find out errors like additional spaces in data entry.
Lesson 7: FILTER Function
Did you know that you can now filter your table data with an Excel Formula? Watch this free Excel training to learn how.
- Filter is a new formula introduced in Office 365 released in 2018!
- It can be used to filter a table array based on conditions you specify and extract matching records.
- It is a dynamic function i.e. when the values in the data sources change the result from this formula will update automatically.
- It’s an ideal function for sorting and picking out data and can even be used to improve the presentation of your reports.
When you needed to replace a specific text in each word, and there is a pattern, Excel has just the formula for you.
Substitute Function can be used to :
- Clean Data by substituting any leading, trailing, or unwanted text.
- Replace every instance of old text with new text.
- Replace multiple texts at one time using the nested Substitute function.
Related Articles:
Remove Second Hyphen with Excel’s SUBSTITUTE Formula
Add Comma in Excel between Names
Count Text Occurrences with Excel’s SUBSTITUTE Formula
Lesson 9: CONCATENATE Function
Excel’s CONCATENATE functions join two or more text strings into one string. The item can be a text value, number, or cell reference.
- A helpful formula for extracting text from data.
- Join several cells to give a consolidated result, especially useful for combining names and other details.
- You can even combine concatenate with other functions for advanced results.
The MATCH function in Excel returns the position of an item within a list or a range of cells.
- It can find irregularities just as easily as it can extract data.
- The match function is a dynamic tool to help make your Excel work at optimum levels.
- Primarily combined with INDEX function to help user result value from a table based on horizontal and vertical criteria.
Lesson 11: ABSOLUTE and RELATIVE Function
Learn the differences between these two and how you can properly use them in Excel Formulas! You can easily compare the differences between Relative and Absolute References in Excel with this Online Excel Training.
- Use Absolute Reference – When you do not want cell reference to change when filling cells.
- Use Relative Reference – When you want cell reference to automatically adjust when filling cells.
Lesson 12: Left, Right & Mid Function
Ever wanted to get something on the left, middle, or right side of your text? You can with the LEFT, MID, and RIGHT text formulas in Excel.
- Three most useful and powerful text-based functions.
- Extracts a specific number of characters from the left, middle, or right of a text.
- Also, helpful in cases where different parts of text contain different information (like social security numbers).
Related Articles:
Excel Extract First Name From Full Name
Lesson 13: XLOOKUP – Outstanding Replacement of VLOOKUP
XLOOKUP is a versatile and outstanding replacement for the above-mentioned Excel functions. It allows you to quickly lookup values in a data set (just like VLOOKUP Excel) with additional flexibility and advantages.
- It can search the value both horizontally and vertically,
- Perform an exact or approximate match,
- Use wildcards,
- Return a custom text when no result is found,
- Doesn’t even have the restriction of the return array to be on the right of the lookup array.
Formatting in Excel
Lesson 14: Excel Fill Handle Tips
Excel knows to fill down/right when you are working with dates, days, months, years, and even quarters. That is very helpful and quick.
- Filling cells help automate the data entry process
- It optimizes data entry to save time
- Watch this learn excel online video to get tips on new ways to fill in your data.
Related Articles:
Copy The Cell Above In Excel
Lesson 15: 6 Ways to Merge Cells in Excel
While working in Excel, you will come across scenarios when you will have to merge cells or combine cells in Excel.
- Merging cells is a common tool for making data more presentable.
- It improves the aesthetics of any report
- Create titles for your reports or combine various cells into one.
Lesson 16: Convert Text to Numbers
In Microsoft Excel, when we do text transformations and extract numbers from text, they stay in the annoying text format! What if we want them to be treated as numbers right away so that we can perform mathematical operations? I have just the thing for you with 4 creative ways to convert text into numbers in Excel!
- Many data manipulating functions work on numeric data only.
- Converting text into numbers makes it easier to extract and analyze data
- Copying data from a PDF or similar sources need to necessarily be converted into the proper format, to be used correctly.
Lesson 17: Evaluate Function & Formulas
Excel provides the way to evaluate your formula, and break it down step by step so that you can understand it!
- Breaks down a formula for better understanding
- Makes deciphering formulas in Excel easier
- Shows the working of each step, making it helpful in following the process of complex formulas.
Lesson 18: Convert Text to Column
If you have a data set with text consisting of names and email addresses that are wrapped inside a parenthesis, then you can use the Text to Columns feature in Excel to take out the email addresses and put them in a separate column
- Separates a column having multiple data
- Makes sorting and arranging data easier
- Makes the data more manageable and presentable
Lesson 19: Clean & Extract Data using Excel
You will learn the different ways to clean and extract data using Excel formulas and Excel’s built-in analytical tools.
- Excel provides many functions to clean up your data and extract precisely what you are looking for
- A clean data sheet is better for presentation and analysis
Related Articles:
Top Excel Data Cleansing Techniques
When you have duplicates values within your data table there is a quick and easy way to remove those values.
- An Excel Worksheet can have multiple duplicates that can be hard to find
- Having Excel remove your duplicates saves valuable time
- Removing duplicates makes the data more reliable and accurate
Related Articles:
How to Find Duplicates in Excel
How to Highlight Unique Values in Excel
The Microsoft Excel Flash Fill feature was introduced in Excel 2013 and it is AWESOME!
- It allows you to transform a column of messy data into clean and workable data so you can do your Excel analysis with ease.
- It is an extremely fast way to fill sequential data
- It takes pattern detected in previous data and auto-fill the row/column up to the point desired
Related Articles:
Extract Numbers Using Flash Fill In Excel
Convert Values to Dates Using Flash Fill In Excel
Add Hyphens To Serial Numbers Using Excel Flash Fill
Fix Incorrect Formatting Using Flash Fill In Excel
Lesson 22: Custom Numbers – Thousands & Millions
Large numbers in Excel can be formatted so they can be shown in “Thousands” or “Millions”. You can even add some text in your cells by entering any word within the quotation marks “your word”.
- Helps present data in different numeric formats, as according to the location where the data is to be presented
- Makes data more familiar and easy to understand
Related Articles:
Create Custom Symbols in Excel based on Numbers
Custom Number Formats in Excel
When your Excel workbook has formulas that are linked to other workbooks/cells that have been deleted, you will get a #REF! error.
Watch this Free Excel Training to learn how to clear the #REF! error!
- This helps clear out unwanted data
- It makes the data look more accurate and free from error.
- It removes invalid or unavailable information from the datasheet
Related Articles:
IFERROR Function
Replace the #N/A Error in Excel
Custom date formats in Excel allow you to display only certain parts of the date.
- Understand how to create a custom date format using the Format Cells option in Excel
- Helps in making data familiar to the region it is being presented in
- Can make the data easier to read and understand
Related Articles:
DATE Formula in Excel
How to Change & Convert UK Dates to US in Excel
4. Excel Tables
Lesson 25: How to Insert an Excel Table
You should start using Excel Tables asap regardless of the size of your data set, as their benefits are HUUUGE.
- Tables in Excel are one of the most versatile tools at our disposal.
- Tables have numerous advantages like autofill, duplicate removing, sorting, filtering, etc.
- They are even dynamic, as, in a reference in any chart, report or formula gets updated automatically when the table is modified.
Lesson 26: Excel Table Headers Show in Columns
Watch this Free Excel Tutorial to learn how to show Excel Table headers in Columns.
- Excel tables have many functions that provide ease while working on Excel.
- One such feature is how the headers of each column are still available at the top, even when we scroll down and out of focus from the header’s original position.
- It removes the limited functionality of Excel Lists.
Lesson 27: Filter & Search in Excel Tables
The Search box within the Filter button is powerful if you know how to use it to your advantage. Make sure to view this video to learn how.
- Allows you to find specific text by typing in a few letters only.
- Data presentation and interpretation can be easily done with this Excel tables feature.
- Unique values or a group of common values can be conveniently found in the table via these features.
Lesson 28: Add Drop Down Menu In Your Excel Table
You can learn how to add a dropdown menu in your Excel Table after watching this Excel Video!
- Allows users to select a value from a list instead of typing it.
- It can make the entry of repetitive data easier and error-free.
- You can implement data validation features in tables.
Related Articles:
Dynamic Data List using Excel Tables
Drop Down List with Data Validation
Lesson 29: Autofill Feature in an Excel Table
Learn the benefits of Autofill Feature in Excel Table by viewing this tutorial.
- It can copy your formulas all the way down.
- This cuts down the tediousness of copy-pasting formulas.
- As you add extra rows to your Excel Table, the formula fills in the extra rows added.
Lesson 30: Remove Duplicates in an Excel Table
There is a quick and easy way to Excel Remove Duplicates from Table!
- Use Excel Tables to eliminate the major hurdle to find and remove duplicates in Excel.
- You can remove duplicates from the existing table or highlight duplicates or copy a list of unique values to a new location.
Lesson 31: Excel Table – Styles
Excel Tables can be formatted using any of the pre-built styles available in Excel.
- Data can be better interpreted when presented in an attractive way.
- Data tables are in general more organized and clean, making it easier to read data.
- Tables in Excel have many different styles and themes to make data presentation more attractive.
Related Articles:
Extra Excel Table Styles
Lesson 32: Excel Table Slicers
Discover the power of Table Slicers by following this Free Excel Training!
- A new feature was added in Excel 2010 called Slicers.
- Slicers allow us to filter data in an interactive manner.
- Slicers implemented in tables add to the versatility of tables.
Related Articles:
All About Excel Table Slicers
5. Excel Charts
Lesson 33: How to Make a Graph in Excel – The Definitive Guide!
Most organizations find it challenging to create reports or make decisions by analyzing huge amounts of data spread across various Excel spreadsheets. This is where Excel Charts and Graphs come into action!
- Graphs can be used to convert a plethora of rows and columns in Excel into simple charts that are easy to evaluate.
- Any raw data can be better understood in a graphical form.
- Data is precise and cleaner in a graphical representation.
Lesson 34: Sparklines in Excel
Sparklines in Excel is a tiny chart in a worksheet cell that provides a visual representation of data. Learn all about the different ways of utilizing Excel Sparklines in this Free Excel Training!
- A new feature introduced in Excel 2010.
- It can add a small dynamic spark to your database by showing trends, forecasts, adding low and high point, etc
- It can easily be placed beside your data cell for convenient understanding
Related Articles:
Win/Loss Sparklines
Add High & Low Points in an Excel Sparkline
In-Cell Bar Charts with the REPT Function
Lesson 35: How to Add Trendlines
In this Excel Graphs and Charts Tutorial, we will show you how to Add Trendlines to your Excel Charts! With Excel Charts, it is very easy to create Trendlines for your data.
- Trendlines show which direction the trend of your data is going, and gives you the trajectory as well.
- It represents data in a line showing peaks and drops.
- It makes decision-making and analysis easier.
Related Articles:
Add Chart Marker Options in Excel
Adding Data Labels To An Excel Chart
Lesson 36: 100% Stacked Column Chart
You can easily create a 100% Stacked Column Chart in Excel with this tutorial.
- It is an extension of the Stacked Column chart.
- It can compare the percentage that each value contributes to a Total.
- It is convenient for showing positive and negative variables in a single graph too.
Related Articles:
100% Stacked Bar Chart
In this Excel Graphs and Charts Tutorial, we will show you how to create a Waterfall Chart!
- Waterfall Charts are one of the many new Charts available only in Excel 2016.
- They are very popular in the current corporate environment as they graphically show the positive and negative movements within your Monthly Net Profit or Cash Flow.
- It shows the movement of data from an initial value to the current value.
Related Articles:
Excel Sunburst Charts
Excel Radar Chart For Performance Reviews
In Excel 2013 and onwards, it has been made much easier to create benchmark charts with the new Combo charts!
- Show your actual sales versus your benchmark in a graphical way, highlighting the strong v weak months.
- They are extremely helpful to visualize forecasted data and actual data variations
- It is very helpful in presenting budgeted sales over actual sales
Related Articles:
Benchmark Chart in Excel 2010
Bubble Chart is extremely useful to graphically represent three dimensions of data and show the relationship between them. Watch this video tutorial to learn how to create them.
- It is an extension of the xy scatter chart and represents three variables at once.
- The first 2 variables are visualized as coordinates, the third as the size of the bubble.
- It can be used to convey a lot of information at once.
Related Articles:
Create a Box and Whisker Excel 2016
Draw a Logo with a Scatter Chart in Excel
In this Excel Graphs and Charts Tutorial, we will show you how to create a Project Milestone Chart in Excel.
- It is helpful in showing a timeline with different stops displaying key milestones or major achievements.
- It helps make project management easier.
- It is useful in conveying information about goals and timelines to everybody.
Related Articles:
Stock Line Chart Using Excel
Candlestick Chart Using Excel
You can easily create a Logarithmic Scale in Excel with this Excel Graphs and Charts Tutorial.
- It evens out a very skewed column chart.
- It is helpful in showing data that has some absurdly different isolated quantities.
- It gives a more presentable chart, clearly showing differences in smaller/larger values.
Lesson 42: Clustered Bar Chart
Clustered Bar Chart in Excel is used to display more than one series of data in clustered horizontal columns or bars.
- It can be used for year-on-year comparison.
- It is used to compare values across different categories or time periods.
- If category names (eg. products, business names, etc.) are way too long, then the Clustered Bar chart is the one for you.
Related Articles:
Clustered Column Chart
How to Create Overlay Charts in Excel
6. Excel Pivot Tables
Lesson 43: The Ultimate Excel Pivot Table Guide
Use PivotTable in your daily job to create management reports and dashboards. Watch this Video to venture into the unknown of Pivot Tables.
- This is a complete pivot table guide for beginners and advanced users.
- Pivot tables and dashboards make your data more presentable and easier to interpret.
- Analyze more than 1 million rows of data with just a few mouse clicks.
Related Articles:
50 Things You Can Do With Excel Pivot Table
Learn the Power of Excel Pivot Tables!
101 Excel Pivot Tables Examples
5 Speedy Steps To An Excel Pivot Table!
10 Excel Pivot Table Tips
In this Excel Pivot Table tutorial, you will learn how to insert a Pivot Table in Excel.
- Pivot tables are a very powerful tool in data arrangement.
- Being an expert pivot table, you can simplify and optimize many work processes.
- A well-presented pivot table can be instrumental in properly conveying information.
Related Articles:
Insert Blank Rows In a Pivot Table
Refresh a Pivot Table
Lesson 45: Group by Quarters & Years
Pivot table is a dynamic tool that can be helpful in grouping dates on a quarterly and yearly basis.
- It helps in trend analysis and comparisons of current data with historical data.
- This is a quick and easy way to group dates and it reduces the risks of making any errors.
- It makes updating the report easily with any new additional data
Related Articles:
Group Sales by Weeks Starting on a Monday
Group By Custom Dates With Excel Pivot Tables
Group By Time With Excel Pivot Tables
Errors When Grouping By Dates With Excel Pivot Tables
Lesson 46: Slicers & PivotCharts
Watch this Free Excel Training to start creating some awesome interactive analytical reports in just a couple of steps.
- Adding Charts and Slicers can make the Pivot Table more dynamic.
- It can make data easier to comprehend and assimilate.
- It is a convenient way to create interactive and useful dashboards.
Related Articles:
The Ultimate Guide to Excel Pivot Table Slicers
Pivot Charts
Copy a Pivot Chart
Expand and Collapse Fields in Pivot Chart in Excel
Add Columns to Pivot Table Slicer Buttons
Lesson 47: Frequency Distribution in Pivot Table
With Excel Pivot Tables you can do a lot of stuff with your data! But did you know that you can even create a Frequency Distribution Table?
I’ll show you how easy it is to create your own Frequency Distribution Chart in this Excel Training Online!
- Frequency distribution is an age-old tool in data analysis and you can use pivot tables to execute them.
- It is great to group data into convenient intervals and get information accordingly.
- Implementing frequency distribution in pivot tables can make your data more meaningful.
Lesson 48: Sort Option in Pivot Table
The Sorting Option is the most powerful feature within Excel Pivot Table. We will learn how to sort your data quickly.
- It allows you to analyze your data in many different ways, all with a press of a button.
- Sorting is one of the most common tools to read data easily.
- Sorting helps users see only the relevant data for analysis.
Related Articles:
Sort an Item Row (Left to Right) With Excel Pivot Tables
Sort Largest to Smallest Grand Totals With Excel Pivot Tables
Sort an Excel Pivot Table Manually
Pivot Table Filter: Top 5 Customers
Filter a Pivot Table by Dates
Lesson 49: Pivot Table Conditional Formatting
Want to know How to Conditionally Format Cells based on their Value in Excel Pivot Tables? Click on this video to view Free Excel Training!
- It is the foremost thing that one undertakes to clean up data and make it more presentable.
- Formatting in a Pivot Table can allow a user to highlight key data in a split second.
- Allows you to add some color to your analysis.
Related Articles:
Color Scales in a Pivot Table
Conditionally Format a Pivot Table With Data Bars
Icon Sets In A Pivot Table
Conditionally Format Blank Cells in Pivot Table
Lesson 50: GETPIVOTDATA Function
View this video on how to create a formula that extracts data stored in a Pivot Table – GETPIVOTDATA!
- This formula can help you create ad-hoc reports outside the Pivot Table.
- These reports will be dynamic, ie, as the pivot table is updated, the report will also be refreshed.
- Additional columns/rows can be added to such reports for more data presentation in specialized fields/cases.
Lesson 51: Calculated Fields in Pivot Table
Pivot Table Calculated Fields allow you to do mathematical calculations with your Field List. Watch this video to learn how!
- You can create your own formula using Calculated Field.
- You can use any of the Excel mathematical equations, like /*+-%.
- It lets you add more power to Pivot Table.
Related Articles:
Pivot Table Calculated Item
Lesson 52: Subtotals in Pivot Table
Read all about the different ways you can use the Subtotal Function in Excel!
- It has the ability to return a SUM, AVERAGE, COUNT, COUNTA, MAX, or MIN from your data.
- It can be used to break a long data list and get information about different groups of data.
- It can be helpful in analyzing different groups of data like regions, centers, products, and more.
Lesson 53: Create Interactive Dashboards
In this Interactive Excel Dashboard tutorial, you will learn how to use & create an Interactive Excel Dashboard in Excel 2010, Excel 2013, Excel 2016, Excel 2019, or Office 365 – for Beginners or Advanced users.
- Dashboards are a great way of aesthetically presenting data.
- It can also aid in the assimilation of information that the data is trying to provide
- It can be used to display the required data through ways of sorting, slicers, formatting, etc.
- Dashboards are also dynamic, meaning they will update as the source data is being edited
Related Articles:
Ultimate Dashboard Tools for Excel
7. Excel Macros & VBA
Lesson 54: How to Use Macro Recorder in Excel
You can automate some steps in Excel using Macro Recorder. Let’s learn how to use it!
- It may be cumbersome to code the exact macro – especially as a VBA beginner.
- Using a Macro Recorder can be easy and can save time as well.
Related Articles:
Excel Macro: Activate the Developer Tab
Macro Recorder: Recording Your First Excel Macro
How to Add a Macro to the Quick Access Toolbar Ribbon in Excel
How to Insert Numbers Using Macros in Excel
Lesson 55: Create Macro Buttons
Do you know that you can automate your tasks in Excel with a couple of mouse clicks? Watch this Free Excel Training to learn about it!
- Macros are a great way to automate basic data formatting and entry.
- It can be designed to handle complex tasks as well.
- Having a macro button makes it much easier to access the function of macros.
- A Macro Button also helps make the data set more interactive and user-friendly.
Related Articles:
Sort All Worksheets By Name Using Macros In Excel
Unhide All Worksheets Using Macros In Excel
Lesson 56: Insert Multiple Rows using Macros
Want to insert multiple rows in Excel with a click of a button?
- Inserting additional rows in a table is a regular occurrence, which can be tackled using macros.
- This Macro will ask for the number of rows to be inserted in a dialog box.
- It will insert the mentioned rows all at the same time.
Related Articles:
How to Insert Multiple Columns Using Macros in Excel
7 Quick Ways to Convert Excel to PDF & PDF to Excel
Lesson 57: How to Autofit Columns
Did you know you can autofit columns using Macros in Excel? This Excel Tutorial shows you just that:
- A worksheet having uneven column widths may look unattractive and resizing each column individually can be a huge task.
- Macros can help autofit column width to fit their contents with a single click.
- When column widths are adjusted, it makes the sheet more presentable as well as easier to read.
Related Articles:
How to Autofit Rows Using Macros in Excel
Lesson 58: Convert Formulas into Values Using Macros
In this Free Excel Training, I will cover the tutorial on how to convert formulas to values using a macro:
- It is done to provide the cell with a constant value.
- The cell will not change its value when the arguments in the formula change.
- Using macros, this function can be easily performed in a single click instead of repeatedly using the paste special functionality.
Related Articles:
Reverse Text Function Using Macros In Excel
Add a Degree Symbol to Selection Using Macros In Excel
Remove Characters at the Start Using Macros In Excel
Remove Extra Spaces from Selection Using Macros In Excel
Protect All Cells With Formulas Using Macros In Excel
Lesson 59: Convert Chart into Image Using Macros
You can use Excel Macros to convert your selected chart into an image! Watch the step-by-step tutorial in this Learn Excel Free Tutorial.
- An image is a versatile format to share information as it is more compatible.
- An image locks the details of a chart from further change, hence curbing manipulation and promoting alteration.
- Using Excel macros, you can easily convert charts to images.
Related Articles:
Add Chart Title Using Macros In Excel
Change Chart Type Using Macros In Excel
Resize All Charts Using Macros In Excel
8. Power Query
Lesson 60: How to Install Power Query in Excel
You can easily install Power Query in Excel 2010 with this tutorial.
- Power Query is a new feature added in Excel 2013 and has gained fame due to its usefulness, making it back compatible with Excel 2010 as well.
- It lets you interact with data as you want, providing flexibility in data management.
- It allows you to set up a query once and then reuse it with a simple refresh. It’s also pretty powerful
Related Articles:
Install Power Query With Excel 2013
Lesson 61: Introduction to Power Query
Want to understand what Power Query is all about? You can easily use Power Query (Get & Transform Data) in Microsoft Excel with this tutorial.
- Power Query is a powerful tool introduced in Excel 2013 that can easily transform data.
- Tables and data sheets imported from various locations often have undesirable additions to then that needs to be cleaned for proper data management.
- Power Query helps clean data and transform it into a more workable position. These steps can be applied to any data set in a single click, which can save your precious time.
Related Articles:
50 Things You Can Do With Excel Power Query
Lesson 62: Consolidate Multiple Excel Sheets Using Power Query Append
- Workbooks have similar data sets in different sheets that would be more efficient if they were consolidated.
- Such operations can be done using a variety of functions but the optimum way is to perform it via a power query.
- It ensures correct input of data is entered without tampering with the original data, hence giving you more flexibility
Lesson 63: Split the Date Using Power Query
- Power Query is one of the most powerful transformational tools.
- It can easily split your date column into a day, month, or year.
- Doing this operation via Power Query saves you a lot of time and can be applied to other sets of data with a single click.
Related Articles:
Split the Time Using Power Query or Get & Transform
Rename a Column Using Power Query or Get & Transform
Lesson 64: Introduction, Parsing Text & Inner Join
- Power Query is a dynamic tool that can help you in parsing text and analyzing it.
- The text to column function can be easily and efficiently performed using Power Query. You can easily use custom delimiters to separate columns.
- It is useful in extracting data that may be common between different data sets using advanced functions like inner join.
Related Articles:
Format Text Using Power Query or Get & Transform
Lesson 65: Unstack & Merge Data Using Power Query
- Copying data from other sources can give unattractive and incomprehensible data.
- Power Query can help install this data and present it in a clean format, making it easier to read, interpret and manipulate
- You can even merge tables easily, choosing the specific columns that you want to merge without interfering with the original data.
Related Articles:
Remove Duplicates Using Power Query or Get & Transform
- Pivot tables are amazing tools to present and analyze data. But sometimes, you just need to unpivot the data.
- Unpivot will rotate the table by transforming columns into rows.
- You can easily convert the crosstab format of a table into a tabular format using Power Query.
Lesson 67: Remove Top, Bottom, and Alternate Rows
- Power Query is a great tool to update and cleanse data.
- It can be used to remove rows – top n rows, bottom n rows, or alternate rows in the data.
- It can be better to present data by removing rows that are unwanted and make data messy.
Related Articles:
Merge Columns Using Power Query
Create Index Columns Using Power Query
Lesson 68: Copy and Paste Queries Across Workbooks Using Power Query
- Applying multiple copy and paste functions across your workbook can be tiresome and time-wasting
- You easily copy-paste queries across your multiple workbooks and save tons of time.
- Learn how to reuse queries in Power Query or Get & Transform with just a few easy steps.
Related Articles:
Split a Query into Two Parts in Power Query
Comment In Query Steps In Power Query
Delete Steps Until End In Power Query
9. Power Pivot
Lesson 69: Enabling Power Pivot in 2013
- Power Pivot is a strong tool to accumulate data from different sources for analysis.
- Power Pivot gives you the power to work with large sets of data that are over 1 million rows!!!
- It is a great way to handle a large volume of data and analyze them all in a beautiful pivot table.
Watch this Free Excel Training to learn how to enable this powerful feature in Excel 2013.
Related Articles:
Enabling Power Pivot in 2016
Installing Power Pivot in Excel 2010
Lesson 70: Link Tables in Power Pivot
- In data management, relationships between different tables are the key to getting consolidated data.
- Power Pivot helps create a common relationship between two tables to extract common from both tables.
- It is a great tool for analyzing data when you are working in a workbook that contains multiple tables.
Related Articles:
Using Excel Tables in Power Pivot
Lesson 71: Importing Excel Workbooks in Power Pivot
- Power Pivot is a dynamic tool that helps handle a large volume of data with ease.
- It can import data from various different sources like Access, Oracle, text files, Excel files, SQL server, etc.
- It has high flexibility in data importing and an easy interface for managing data.
Lesson 72: Using the Diagram View in Power Pivot
- Power Pivot is helpful in managing a large volume of data and it’s a powerful tool in handling even a million rows/
- Due to the large database that it attracts, it can be overwhelming to keep track of all the data and the different relationships between your data tables.
- Power Pivot provides a diagram view option so that the entire data can be viewed at a bird’s eye level for easy understanding.
Lesson 73: COUNTROWS in Power Pivot
- Power Pivot acts as a strong measure in collecting, controlling, and analyzing large chunks of data.
- One of its powerful functions is known as measures, which are basically calculated fields(as known in Excel 2013).
- Watch this Free Excel Training to learn how easy it is to use the COUNTROWS function in your Measure.
Lesson 74: Measures Vs Calculated Column in Power Pivot
- In Excel Power Pivot, two of the more powerful feature are Measures and Calculated Columns.
- Measures are DAX formulas that can be added to Pivot Table and Calculated Columns are DAX formulas that you can add to each row.
- In this Free Excel Training, we will elaborate on the differences between measures and calculated columns.
Related Articles:
Using Measures in Power Pivot
10. Power BI
Lesson 75: Introduction to Power BI
How to understand the Power BI – Overview! You can easily learn the overview of Excel Power BI with this Free Excel Training.
- Power BI allows you to get data from almost any source.
- It can be used to create data relationships and display attractive Dashboards.
- You can easily share these Power BI reports with your colleagues.
Lesson 76: High-Level Flow of Power BI
To get a better understanding of Power BI, please go through this ultimate guide on Power BI flow.
- Start with Power Query, i.e. load data from various sources and clean it.
- Then Power Pivot – Create Data Models by establishing relationships between tables.
- Next Power View – Create visualization to show trends in your data model
- Lastly, Publish it on the Power BI website so that it can be viewed and shared by colleagues.
Make sure to download our FREE PDF on the 333 Excel keyboard Shortcuts here:
John Michaloudis is a former accountant and finance analyst at General Electric, a Microsoft MVP since 2020, an Amazon #1 bestselling author of 4 Microsoft Excel books and teacher of Microsoft Excel & Office over at his flagship MyExcelOnline Academy Online Course.
30 Days - Full Access 
