ExcelTutorials

Free Excel Course Training – Beginner to Advanced

9 Mins read

If you are keen to learn advanced Excel and brush up on your Excel Knowledge this Excel Tutorial is just for you. Free Excel Course is for all learners with basic Excel knowledge.

To work with speed and streamline things with accuracy and speed it is very important to know some keyboard shortcuts along with the proper usage of Functions and Formulas. One should also be aware of the different types of facilities the software is providing. With this Free Excel Course you become Hero at your Office, so start your learning today.

Excel Vlookup formula – Picture Guidebook

Excel Vlookup Formula

Bored of downloading text heavy / copy-pasted eBooks?

If Yes, you will enjoy this picture-based guidebook on ‘Excel Vlookup Formulas’ – VLOOKUP, HLOOKUP, MATCH & INDEX.

Free Excel Course Training Online

Thus, to make it easy for you I have divided the FREE Excel Course into three parts, also you can directly navigate to a particular module by clicking on their name.

  • Module 1: Formula and Functions – Get insights into all the formulas and functions
  • Module 2: Pivot Tables – Get to learn Pivot Table and its usage functionalities in Excel
  • Module 3: Shortcuts – Get aware of most of the time-saving rescuer keyboard shortcuts

Module 1: Formula and Functions in Excel

Lesson 1: Vlookup Formula

In this Excel tutorial, an introduction of Vlookup is explained with the example. Vlookup function helps in building a new table of few values by looking up to the column of the main table.

Lesson 2: Vlookup True Arguments

In this Excel Tutorial, the arguments of true or false are discussed that explains that if the argument is false then we are looking for the exact match of the state value and if the argument is true then the nearby value can also be chosen.

Lesson 3: Formula Auditing Tricks

In this Excel Tutorial, the auditing of Excel formulas is explained. It is done by clicking on the Ribbon Tab in Excel called formulas and under that, there is an option of Formula Auditing.

Lesson 4: 3 Ways to Transpose Data

In this Excel Tutorial, 3 ways of how to transpose data are explained. Transpose is the term used when rows of the tables are changed to column and vice versa. Method one is by especially inserting manually each value. Method two is by using the Transpose function and method three is by using Pivot Table.

Lesson 5: Round Formula – Round up and Round Down

In this Excel Tutorial, Rounding is explained. Rounding of a numeric value is generally done to make the calculations easier. Rounding Up is done to the nearest specified ‘UP’ place and Rounding down is done to the nearest specified ‘Down’ place.

Excel Vlookup formula – Picture Guidebook

Excel Vlookup Formula

Bored of downloading text heavy / copy-pasted eBooks?

If Yes, you will enjoy this picture-based guidebook on ‘Excel Vlookup Formulas’ – VLOOKUP, HLOOKUP, MATCH & INDEX.

Lesson 6: Round Formula

In this Excel Tutorial, the importance and use of the Round Formula are explained. The round formula works well with negative values as well as positive values whereas MROUND formula works well only with positive formulas.

Lesson 7: Transpose in Excel

Transpose function will change the rows into column value and vice versa. In this video, transpose is performed along with some other functions to get the desired transpose.

Lesson 8: Excel error and solution

In this Excel video, the errors in the Excel sheet are removed with the help of a Go To Special function in excel. The errors here mentioned were in the Go To Special and then checking only on errors to remove the errors in the Excel Sheet.

Lesson 9: Power Formula

In this video, the Power Function is explained. When a number is raised to a certain power and you need to return its answer, the Power function is used. Multiplication of the same number multiple times can be achieved using the carat sign or by using a Power function.

Lesson 10: Match Formula

If we want to search the specific position of an item on the cell in a spreadsheet, Match formula is used. In this Excel Tutorial, the usage of the match formula is explained.

Lesson 11: Indirect Formula Part 1

In this video, the Indirect formula is explained. Indirect is used mainly when you need the value on the specified address in the file. Learn more Indirect Function in Excel.

Lesson 12: Indirect Formula Part 2

In this Excel Tutorial, the continuation of the Indirect Formula is continued. Indirect converts the text string into a valid reference when it is used.

Excel Vlookup formula – Picture Guidebook

Excel Vlookup Formula

Bored of downloading text heavy / copy-pasted eBooks?

If Yes, you will enjoy this picture-based guidebook on ‘Excel Vlookup Formulas’ – VLOOKUP, HLOOKUP, MATCH & INDEX.

Lesson 13: Go To Formula

In this Excel Tutorial, the usage of function Go To is explained. If you want to jump to a specific cell reference Go to a formula is used. The usage of Go To formula is explained in this video.

Lesson 14: Match with Vlookup

This function is used to create reports, dashboards and a lot more. In Excel Tutorial, the working of MATCH function with Vlookup is explained. It states that Match in Vlookup helps in returning the value concerning numbers.

Lesson 15: Autofill

In this video, the Autofill function is explained concerning Months, Dates and numbers without typing. If the first two cells are filled then the AutoFill function automatically fills the desired values in ret of the selected cells.

Lesson 16: Logic Functions

In this Excel Tutorial, Logic functions are explained in detail with the help of examples. Logic functions are used in Excel when you want to compare more than one comparison in your formula or want to test more than one condition.

Lesson 17: Vlookup with true Excel

In this video, Vlookup is used with true. If Vlookup with true means that a close match will be returned if the exact value is not found.

Lesson 18: Vlookup Vs. Match Vs. Indirect Formula

In this Excel Tutorial, the concept of using Vlookup with Match and indirect is used together to get the answer with a short formula rather than the application of Long formulas.

Lesson 19: Insert Blank row

If you want to insert a blank row in-between data then in this Excel Tutorial it is explained in a perfect way to do it rather than doing it manually multiple times just create a dummy serial number and then go to the Data tab and sort it according to the dummy serial number. This will give us a blank row inserted after each entry.

Lesson 20: Excel vs Word Find and Replace

In this video, it is stated that how you can Data Clean in Excel and group it using word and carry out the operations there and then come back to Excel to paste the output there.

Pivot Tables – Guidebook for Pros

15 Pivot Table tricks to make your Excel data analysis smarter! 5,600+ downloads. Most Popular Tricks are #3, #7 & #12

Lesson 21: ISNUMBER, ISTEXT, ISBLANK, ISERROR, ISFORMULA

In this video, the use of is function is specified. If the cell contains a number and ISNUMBER is applied to that cell then it returns the value true or else false. The same applies to ISTEXT, ISBLANK, ISERROR, ISFORMULA that returns true if the value in that cell is text, blank, error, and formula respectively.

Lesson 22: Weekday, Workday formula

In this video, the explanation of workday and weekday is explained. The representation of the nearest N working days in the past and future is done by a Workday function. Weekday function takes a date and returns the value between 1-7 to state the day of the week.

Lesson 23: Calculate Home Loan EMI

In this video, we are going to find out the PMT formula for any loan which you would like to take in the future or taken in the past or simply can be called to calculate the EMI of the loan using excel. The next is Goal Seek is used to back-calculate a desired certain number.

Lesson 24: Difference Between Vlookup vs. Hlookup

In this video, the difference between Vlookup and Hlookup is explained. Vlookup finds the data in the left-most column while Hlookup finds the data in the bottommost rows.

Lesson 25: Excel Subtotal 9 vs. 109 series

In this video, the Subtotal Function is explained. Subtotal value returns the aggregate value of the numbers that are supplied to it.

Pivot Tables – Guidebook for Pros

15 Pivot Table tricks to make your Excel data analysis smarter! 5,600+ downloads. Most Popular Tricks are #3, #7 & #12

Lesson 26: How to use the EOMONTH function

In this video, the EOMONTH function is explained. EOMONTH function is used to calculate the last day of the month. It adds some specified number of months to get the serial date.

Lesson 27: Goal Seek function

In this video, the working of Goal Seek function is used. If you the result of some formula then you can type the result to get that specific formula by using Goal seek function.

Lesson 28: IRR & NPV calculation

In this video, IRR and NPV calculations are explained. IRR is the Internal Rate of Return and NPV is the Net Present Value is used when we are selecting investments based on returns.

Lesson 29: Building Schedule for Cash Surplus and Cash Overdraft

If you want to view to check or see the existing business schedule templates in Excel. In this video, the function of the building schedule for Cash Surplus and Cash Overdraft is explained.

Module 2: Pivot Table in Excel

Lesson 30: Pivot Table Slicer vs Report Filter

The slicer is a filter that can be applied to the visually appealing dialog box. In this video, Slicer and Report table functionality and differences are explained with an example. Slicers are a type of report filter but very different than them. Slicers require complex code to design while the Report filter can be built by simple VBA. Slicers can work on many tables simultaneously by pivot tables while the report filters cannot work on different tables at the same time.

Lesson 31: Excel Pivot Table Report Filter

In this video, report filters, its meaning, and application on Pivot tables are explained. You can generate multiple reports from the Pivot table.

Pivot Tables – Guidebook for Pros

15 Pivot Table tricks to make your Excel data analysis smarter! 5,600+ downloads. Most Popular Tricks are #3, #7 & #12

Lesson 32: Pivot table Example

In this video, a Pivot table is created using 1.6M sales data. It explains how to perform a sales data analysis using a Pivot table.

Lesson 33: Why should you learn Pivot Table

In this video, the wonderful works of the Pivot table are defined. It is stated with the help of an example how the Pivot Table is used as a Data Summarization tool. It is also used or data processing. Pivot tables are also used to sort, recognize, group, total, count average, etc. that are stored in the database.

Module 3: Excel Shortcuts

Lesson 34: 20+ Excel Shortcuts in three minutes

In this video, most of the Excel shortcuts are discussed to work easily. If you want to open the argument dialog box after writing a formula you just have to press Ctrl-A to open it automatically. Ctrl is used to view the back formulas used.

Lesson 35: Excel Shortcuts on Rows and Columns

In this video, it is explained How to Insert Rows and Columns using shortcut keys. To insert a column, you will have to first press Alt, then I, then C, one after other to quickly insert a column. Similarly, to insert a row you have to press Alt, I, and R one after other to insert a row. Ctrl + Spacebar selects the entire column. Alt + Spacebar selects or includes the entire row.

Lesson 36: Excel Shortcuts which you will never Know

In this video, some of the great Excel Shortcuts are mentioned which you will be hard knowing. The shortcut keys for paste special value are used. One way is to press Alt, E, S, V and Enter one by one. The other way is to press Ctrl+Alt+V together and then press V and Enter.

PDF GUIDE BOOKS

  • Download Excel Shortcuts for Windows & Mac
  • Basic to Advanced Excel Formulas PDF Guide

This Book will help you to get Excel Skills & Boost your career.

Lesson 37: Little Known Excel tricks on Navigation

In this video, the navigation shortcut keys are explained. Ctrl + PgDn takes you to the next sheet. Ctrl + Pg Up takes you to the previous sheet. Ctrl-A selects the entire worksheet, in the same way, other navigations shortcuts are also mentioned.

Lesson 38: Excel Shortcuts that will change your Life

In this video, the shortcut keys of formatting cells will be explained. Ctrl 1 activates the format cell option. Ctrl key enters today’s date. Ctrl + shift + 3 changes the date format. Ctrl F2 print Preview, Ctrl F1 displays or hides the ribbon.

Lesson 39: Shortcut Keys-Part 1

In this video, the shortcut keys are used mostly while working with formulas. Some of the keys are F4 which repeats the command, and also for cell referencing, F2 to begin editing the active cell, and many more.

PDF GUIDE BOOKS

  • Download Excel Shortcuts for Windows & Mac
  • Basic to Advanced Excel Formulas PDF Guide

This Book will help you to get Excel Skills & Boost your career.

Lesson 40: Shortcut Keys- Part 2

In this video, there is one universal shortcut is stated. Alt is the universal shortcut. Once you press and release the Alt command, numbering or the alphabets will appear on the ribbon tab. You can press whichever alphabet or number of the command you like to perform.

Lesson 41: Most Popular Excel shortcuts

You can fill the blank cells with go to special command with Ctrl + Enter.

Lesson 42: How to fill blank cells using Data

In this Excel Tutorial, a shortcut is shown by pressing Ctrl + G or Ctrl + Enter to fill the blank cells using data.
Thus, in this way, with the help of videos and dividing the FREE Excel course into mainly three parts, you can easily get a hold on Excel. I would suggest to go one at a time for learning and then practice it in your work and then move to another topic. Once you practice the topic that you have learned it will be easy for you to remember the formulas, functions, and usage of topics. We hope you like our Free Excel Course Training.

PDF GUIDE BOOKS

  • Download Excel Shortcuts for Windows & Mac
  • Basic to Advanced Excel Formulas PDF Guide

This Book will help you to get Excel Skills & Boost your career.

Related posts
AlteryxTutorials

Alteryx Hotkeys - Alteryx Keyboard Shortcuts

2 Mins read
Top 50+ Alteryx Shortcuts for Windows. Alteryx is popularly known as a Self-Service Analytics tool. Business users can build their data workflows…
AlteryxTutorials

Step By Step Guide to Learn Alteryx

6 Mins read
Alteryx Learning Path: The growth in technology has resulted in growth in understanding. In today’s world, humans – fellow businessmen know the…
Excel VBA CodeTutorials

VBA Code to Clean the Date Format

1 Mins read
When it is useful? Most of the time the most annoying problem is when the data is taken from ERP or other…