**Welcome to Advance Excel Education **

Our easy to follow tutorial (Introduction, Basics, Functions, Data Analysis and VBA) teaches you how to use Excel.

Below you can find a complete overview. Want to learn much more about Excel? You can find related examples and features

(More than 301 Examples) on the right side of each chapter. We keep it easy!

I keep coming back to your examples. Your website is extremely helpful.

This section is for users with no knowledge of Excel.

Microsoft Excel is one of the most used software applications of all time. Hundreds of millions of people around the world

use Microsoft Excel. You can use Excel to enter all sorts of data and perform financial, mathematical or statistical

calculations.

1 **Range:** A range in Excel is a collection of two or more cells. This chapter gives an overview of some very important range

operations.

2 **Formulas and Functions:** A formula is an expression which calculates the value of a cell. Functions are predefined

formulas and are already available in Excel.

This section explains the basics of Excel.

1 Ribbon: Excel selects the ribbon's Home tab when you open it. Learn how to minimize and customize the ribbon.

2 Workbook: A workbook is another word for your Excel file. Excel automatically creates a blank workbook when you open it.

3 Worksheets: A worksheet is a collection of cells where you keep and manipulate the data. By default, each Excel workbook

contains three worksheets.

4 Format Cells: When we format cells in Excel, we change the appearance of a number without changing the number itself.

5 Find & Select: Learn how to use Excel's Find, Replace and Go To Special feature.

6 Templates: Instead of creating an Excel workbook from scratch, you can create a workbook based on a template. There are

many free templates available, waiting to be used.

7 Data Validation: Use data validation in Excel to make sure that users enter certain values into a cell.

8 Keyboard Shortcuts: Keyboard shortcuts allow you to do things with your keyboard instead of your mouse to increase your

speed.

9 Print: This chapter teaches you how to print a worksheet and how to change some important print settings in Excel.

10 Share: Learn how to share Excel data with Word documents and other files.

11 Protect: Encrypt an Excel file with a password so that it requires a password to open it.

Functions

Discover how functions in Excel help you save time. If you are new to functions in Excel, we recommend you to read our

introduction to Formulas and Functions first.

1 Count and Sum: The most used functions in Excel are the functions that count and sum. You can count and sum based on

one criteria or multiple criteria.

2 Logical: Learn how to use Excel's logical functions such as the IF, AND and OR function.

3 Cell References: Cell references in Excel are very important. Understand the difference between relative, absolute and

mixed reference, and you are on your way to success.

4 Date & Time: To enter a date in Excel, use the "/" or "-" characters. To enter a time, use the ":" (colon). You can

also enter a date and a time in one cell.

5 Text: Excel has many functions to offer when it comes to manipulating text strings.

6 Lookup & Reference: Learn all about Excel's lookup & reference functions such as the VLOOKUP, HLOOKUP, MATCH, INDEX and

CHOOSE function.

7 Financial: This chapter illustrates Excel's most popular financial functions.

8 Statistical: An overview of some very useful statistical functions in Excel.

9 Round: This chapter illustrates three functions to round numbers in Excel. The ROUND, ROUNDUP and ROUNDDOWN function.

10 Formula Errors: This chapter teaches you how to deal with some common formula errors in Excel.

11 Array Formulas: This chapter helps you understand array formulas in Excel. Single cell array formulas perform multiple

calculations in one cell.

Data Analysis

This section illustrates the powerful features Excel has to offer to analyze data.

1 Sort: You can sort your Excel data on one column or multiple columns. You can sort in ascending or descending order.

2 Filter: Filter your Excel data if you only want to display records that meet certain criteria.

3 Conditonal Formatting: Conditional formatting in Excel enables you to highlight cells with a certain color, depending on

the cell's value.

4 Charts: A simple Excel chart can say more than a sheet full of numbers. As you'll see, creating charts is very easy.

5 Pivot Tables: Pivot tables are one of Excel's most powerful features. A pivot table allows you to extract the significance

from a large, detailed data set.

6 Tables: Tables allow you to analyze your data in Excel quickly and easily.

7 What-If Analysis: What-If Analysis in Excel allows you to try out different values (scenarios) for formulas.

8 Solver: Excel includes a tool called solver that uses techniques from the operations research to find optimal solutions

for all kind of decision problems.

9 Analysis ToolPak: The Analysis ToolPak is an Excel add-in program that provides data analysis tools for financial,

statistical and engineering data analysis.

Visual Basic Applications

Excel VBA (Visual Basic for Applications) is the name of the programming language of Excel.

1 Create a Macro: With Excel VBA you can automate tasks in Excel by writing so called macros. In this chapter, learn how

to create a simple macro.

2 MsgBox: The MsgBox is a dialog box in Excel VBA you can use to inform the users of your program.

3 Workbook and Worksheet Object: Learn more about the Workbook and Worksheet object in Excel VBA.

4 Range Object: The Range object, which is the representation of a cell (or cells) on your worksheet, is the most important

object of Excel VBA.

5 Variables: This chapter teaches you how to declare, initialize and display a variable in Excel VBA.

6 If Then Statement: Use the If Then statement in Excel VBA to execute code lines if a specific condition is met.

7 Loop: Looping is one of the most powerful programming techniques. A loop in Excel VBA enables you to loop through a range

of cells with just a few codes lines.

8 Macro Errors: This chapter teaches you how to deal with macro errors in Excel.

9 String Manipulation: In this chapter you find the most important functions to manipulate strings in Excel VBA.

10 Date and Time: Learn how to work with dates and times in Excel VBA.

11 Events: Events are actions performed by users which trigger Excel VBA to execute code.

12 Array: An array is a group of variables. In Excel VBA, you can refer to a specific variable (element) of an array by

using the array name and the index number.

13 Function and Sub: In Excel VBA, a function can return a value while a sub cannot.

14 Application Object: The mother of all objects is Excel itself. We call it the Application object. The application object

gives access to a lot of Excel related options.

15 ActiveX Controls: Learn how to create ActiveX controls such as command buttons, text boxes, list boxes etc.

16 Userform: This chapter teaches you how to create an Excel VBA Userform.

More than 301 Examples

Are you looking for easy to follow Excel examples? Are you looking for clear explanations that help you master many more

Excel features quickly and easily? You can find related examples and features on the right side of each chapter.

Tutorials

Formula Basics

How to enter a formula ,How to apply a formula to an entire column ,Relative and Absolute cell referencing

If Functions and Formulas ,Conditional Expressions ,Introduction to the IF function ,Summing and Counting Functions and

Formulas ,COUNT Function ,MAX Function ,MIN Function ,SUM Function ,Advanced Excel Formulas ,VLOOKUP Function and formulas

Introduction to Date functions ,Linking Data between sheets and workbooks ,Other Useful Stuff ,Conditional formatting -

Introduction ,Pivot Tables - Introduction ,Excel's Evaluate formula tool ,Conditional ,Create conditional formulas

Check if a number is greater than or less than another number ,Display or hide zero values ,Hide error values and error

indicators in a cell ,Lookup ,Look up values in a range ,Date and Time ,Add dates ,Add times ,Calculate the difference

between two dates ,Calculate the difference between two times ,Count days before a date ,Show dates as the day of the week

Insert the current date and time in a cell ,Insert Julian dates ,Financial ,Calculate a running balance

Calculate a compound annual growth rate (CAGR) ,Statistical ,Calculate the average of numbers ,Calculate the median of a

group of numbers ,Calculate the mode of a group of numbers ,Math ,Add numbers ,Subtract numbers ,Multiply numbers

Divide numbers ,Calculate percentages ,Round a number ,Raise a number to a power ,Calculate the smallest or largest number

in a range ,Calculate the factorial of a number ,Create a multiplication table ,Counting ,Count cells that contain numbers

Count nonblank cells ,Count how often a value occurs ,Count occurrences of values or unique values in a data range

Count numbers greater than or less than a number ,Calculate a running total ,Count all of the cells in a range

Count the number of words in a cell or range ,Conversion ,Convert times ,Convert dates stored as text to dates

Convert numbers stored as text to numbers ,Convert measurements ,Convert numbers to different number systems

Convert Arabic to Roman numerals ,Text ,Change the case of text ,Check if a cell contains text ,Compare cell contents

Combine text and numbers ,Combine text with a date or time ,Combine first and last names ,Combine two or more columns by

using a function ,Repeat a character in a cell ,Display only the last four digits of identification numbers

Remove spaces from the beginning and end of a cell ,Remove characters from text ,Insert the current Excel file name

in a cell ,Split names by using Convert Text to Columns ,Split text among columns by using functions ,

Excel Formulas: 10 Formulas That Helped Me Keep My Job

Don’t waste any more hours in Microsoft Excel doing things manually. There are many ways to use Excel formulas to

decrease the amount of time you spend in Excel and increase the accuracy of your data and your reports.

Excel Formulas You Should Definitely Know:

1. SUM Formula: =SUM(5, 5) or =SUM(A1, B1) or =SUM(A1:B5)

The SUM formula does exactly what you would expect. It allows you to add 2 or more numbers together. You can use cell

references as well in this formula.

The above shows you different examples. You can have numbers in there separated by commas and it will add them together

for you, you can have cell references and as long as there are numbers in those cells it will add them together for you,

or you can have a range of cells with a colon in between the 2 cells, and it will add the numbers in all the cells in the

range.

2. COUNT Formula: =COUNT(A1:A10)

The count formula counts the number of cells in a range that have numbers in them.

This formula only works with numbers though:

It only counts the cells where there are numbers.

Learn more about the COUNT function in this on-demand, online course. FREE preview

3. COUNTA Formula: =COUNTA(A1:A10)

Counts the number of non-empty cells in a range. It will count cells that have numbers and/or any other characters in them.

The COUNTA Formula works with all data types.

It counts the number of non-empty cells no matter the data type.

4. LEN Formula: =LEN(A1)

The LEN formula counts the number of characters in a cell. Be careful though! This includes spaces.

Notice the difference in the formula results: 10 characters without spaces in between the words, 12 with spaces between

he words.

5. TRIM Formula: =TRIM(A1)

Gets rid of any space in a cell, except for single spaces between words. I’ve found this formula to be extremely useful

because I’ve often run into situations where you pull data from a database and for some reason extra spaces are put in

behind or in front of legitimate data. This can wreak havoc if you are trying to compare using IF statements or VLOOKUP’s.

I added in an extra space behind “I Love Excel”. The TRIM formula removes that extra space. Check out the character count

difference with and without the TRIM formula.

6. RIGHT, LEFT, MID

Formulas: = RIGHT(text, number of characters), =LEFT(text, number of characters), =MID(text, start number, number of

characters).

Note: In all of these formulas, wherever it says “text” you can use a cell reference as well

These formulas return the specified number of characters from a text string. RIGHT gives you the number of characters

from the right of the text string, LEFT gives you the number of characters from the left, and MID gives you the specified

number of characters from the middle of the word. You tell the MID formula where to start with the start_number and then

it grabs the specified number of characters to the right of the start_number.

I used the LEFT formula to get the first word. I had it look in cell A1 and grab only the 1st character from the left.

This gave us the word “I” from “I love Excel”

I used the MID formula to get the middle word. I had it look in cell A1, start at character 3, and grab 5 characters

after that. This gives us just the word “love” from “I love Excel”

I used the RIGHT formula to get the last word. I had it look at cell A1 and grab the first 6 characters from the right.

This gives us “Excel” from “I love Excel”

7. VLOOKUP Formula: =VLOOKUP(lookup_value, table_array, col_index_num, range_lookup)

By far my most used formula. The official description of what it does: “Looks for a value in the leftmost column of a table,

and then returns a value in the same row from a column you specify…”. (See the full explanation of VLOOKUP) Basically,

you define a value (the lookup_value) for the formula to look for. It looks for this value in the leftmost column of a

table (the table_array).

Note: If at all possible use a number for the lookup_value. This makes it a lot easier to make sure the data you are

getting back is a correct match.

If it finds a match of the “lookup_value” in the left column of the “table_array” it will return the value in the column

you specify using the “index_num”. The “index_num” is relative to the left most column. So, if you have the table_index

look in column A and you want what is returned to be what’s in column B the “index_num” would be 2 because the leftmost

column, column A in this case, is the 1st column in the table array and column B is the 2nd column (hence the 2 for the

index number). If you want what is in column C to be returned you’d put 3 for the index_num. The “range_lookup” is a TRUE

or FALSE value. If you put TRUE it will give you the closest match. If you put FALSE it will only give you an exact match.

I only use FALSE when using the VLOOKUP formula.

You have 2 lists: 1 with a sales person’s ID and the sales revenue for the quarter. Another with the sales person’s ID and

the sales person’s name. You want to match up the sales person’s name to the sales person’s revenue numbers for the quarter. They are all jumbled around so to manually match this, even for a small number of salesmen would leave room for a high margin of error and take a lot of time.

The first list goes from A1 to B13. The 2nd list goes from D1 to E25.

In cell C1 I would put the formula =VLOOKUP

B18 = the lookup_value (the sales person’s ID. This is a number that appears on both lists.)

the “table_array”. This is the area I want the formula to search the leftmost column (column E in this case) for the

“lookup_value”. I went to F because if it finds match in column E, I want it to return what’s in column F. (The money

signs are there so that the table_array will stay the same no matter where the formula is moved or copied to. This is

called an absolute reference.)

2 = the index_num. This tells the formula the number of columns away from the left most column to return in case of match.

So, if you find a match between the lookup_value and the leftmost column of the table array, return what’s in the same row

in the 2nd column of the table (the 1st column is always the leftmost column. It starts at 1, not 0).

FALSE= tells the formula I want it to only return the value if it’s an exact match.

I would then copy and paste that formula along all the cells in column C next to the first list. This would give me a

perfectly aligned list with the sales person’s ID, sales person’s revenue for the quarter, and the sales person’s name.

In order to get a nice neat list of Sales Person ID, Sales Person Name, and Sales Person Revenue all next to each other I

used the VLOOKUP formula to compare 1 list to another.

This is a complicated formula, but an extremely useful one. Check out some other examples: Vlookup Example, Microsoft’s

Official Example.

**Learn more about the VLOOKUP function in this on-demand, online course. FREE preview**

8. IF Statements

Formula: =IF(logical_statement, return this if logical statement is true, return this if logical statement is false)

When you’re doing an analysis of a lot of data in Excel there are a lot of scenarios you could be trying to discover and

the data has to react differently based on a different situation.

Continuing with the sales example: Let’s say a salesperson has a quota to meet. You used VLOOKUP to put the revenue next

to the name. Now you can use an IF statement that says: “IF the salesperson met their quota, say “Met quota”, if not say

“Did not meet quota” (Tip: saying it in a statement like this can make it a lot easier to create the formula, especially

when you get to more complicated things like Nested IF Statements in Excel).

It would look like this:

In the example with the VLOOKUP we had the revenue in column B and the person’s name in column C (brought in with the

VLOOKUP). We could put their quota in column D and then we’d put the following formula in cell E1:

=IF(C3>D3, “Met Quota”, “Did Not Meet Quota”)

This IF statement will tell us if the first salesperson met their quota or not. We would then copy and paste this formula

along all the entries in the list. It would change for each sales person.

Having the result right there from the IF statement is a lot easier than manually figuring this out.

9. SUMIF, COUNTIF, AVERAGEIF

Formulas: =SUMIF(range, criteria, sum_range), =COUNTIF(range, criteria), =AVERAGEIF(range, criteria, average_range)

These formulas all do their respective functions (SUM, COUNT, AVERAGE) IF the criteria are met. There are also the

formulas: SUMIFS, COUNTIFS, AVERAGEIFS where they will do their respective functions based on multiple criteria you

give the formula.

I use these formulas in our example to see the average revenue (AVERAGEIF) if a person met their quota, Total revenue

(SUMIF) for the just the sales people who met their quota, and the count of sales people who met their quota (COUNTIF)

10. CONCATENATE

A fancy word for combining data in 2 (or more) different cells into one cell. This can be done with the Concatenate

excel formula or it can be done by simply putting the & symbol in between the two cells. If I have “Steve” in cell A1

and “Quatrani” in cell B1 I could put this formula: =A1&” “&B1 and it would give me “Steve Quatrani”. (The “ “ puts a

space in between what you are combining with the &). I can use =concatenate(A1, “ “, B1) and it will give me the same

thing: “Steve Quatrani”

Finding The Right Excel Formulas For The Job

There are 316 built in functions in Excel. You’re not going to sit there and memorize what all of them do (or at least

I hope not!). Luckily Excel has a built in wizard that helps you find the correct formula for what you’re looking to do

(if there is one).

Click the “fx” next to the formula bar in Excel

This brings up a menu and in there you can type in a description of what you are trying to do and it will bring up the

correct excel formula:

I typed in “remove extra spaces” and it returned the TRIM formula that we went over earlier.

More Excel Formulas

There is so much more that I use on a regular basis such as Time formulas (NOW, TODAY, MONTH, YEAR, DAY, etc.), other

formulas like AND and OR, along with many others.

The real power comes in combining these functions into complicated excel formulas.

Learn Excel Formulas Today!

Breaking Down Complicated Excel Formulas

=IFERROR(TRIM(IF(LEN(VLOOKUP(F7, Sheet2!$A$1:$B$10000, 2, FALSE))>0,SUBSTITUTE(VLOOKUP(F7, Sheet2!$A$1:$B$10000, 2,

FALSE), ” “, “”),”")), “”)

Do you see formulas like the one above and run away screaming AHHHHHHHHHHHHHHHHHHHHHHHHH!!!

Well I don’t blame ya!

This can be a really intimidating formula even for the most seasoned Excel user.

I have a solution for you that makes it easy to both break down, and build up complicated combinations of Excel formulas

but first check out this short video by fellow Udemy trainer, Mynda Treacy, that will reveal tips on how to get inside

the mind of an Excel formula:

So What’s Going on With This Excel Formula?

Let’s break down all the Excel functions in it:

IFERROR

TRIM

IF

LEN

VLOOKUP

SUBSTITUTE

I’m not going to go in depth into each formula, but you’ll get the point.

Let’s start with the innermost formula. This is the end result that you are trying to accomplish.

We want to take a phrase (that we get with a VLOOKUP) that has a space in it and SUBSTITUTE that space with nothing.

So it will take a phrase like “Excel Formula” and make it “ExcelFormula”.

SUBSTITUTE(VLOOKUP(F7, Sheet2!$A$1:$B$10000, 2, FALSE), ” “, “”)

We use the VLOOKUP formula to get the actual phrase we want from a different sheet in the Excel Workbook if it matches

what’s in cell F7.

VLOOKUP(F7, Sheet2!$A$1:$B$10000, 2, FALSE)

We use an IF statement to say IF the number of characters (LEN) of what’s returned from the VLOOKUP is above 0, then run

the VLOOKUP, otherwise, put nothing here. This way if the VLOOKUP returns nothing, then nothing will happen and this will

prevent most errors.

IF(LEN(VLOOKUP(F7, Sheet2!$A$1:$B$10000, 2, FALSE))>0,SUBSTITUTE(VLOOKUP(F7, Sheet2!$A$1:$B$10000, 2, FALSE),

We then wrap it with the TRIM function which gets rid of all extra spaces, besides 1 space in between words. Sometimes

when you’re getting data from a database extra spaces can be added after a word. This can make it really hard to compare

data from different sources, so the TRIM function comes in handy a lot!

TRIM(IF(LEN(VLOOKUP(F7, Sheet2!$A$1:$B$10000, 2, FALSE))>0,SUBSTITUTE(VLOOKUP(F7, Sheet2!$A$1:$B$10000, 2, FALSE),

Finally we wrap it in the IFERROR formula. This formula will be triggered if the other formulas wind up giving you

an error. It could look something like #N/A and that’s pretty ugly. This can happen for legitimate reasons, but you don’t

want to hand your boss a sheet full of #N/A symbols.

What happens is IF the combined formulas return any excel error, it will make the cell blank, otherwise it will show the

results of the formula.

=IFERROR(TRIM(IF(LEN(VLOOKUP(F7, Sheet2!$A$1:$B$10000, 2, FALSE))>0,SUBSTITUTE(VLOOKUP(F7, Sheet2!$A$1:$B$10000, 2, FALSE)

There you have it, this long complicated formula broken down into simple step by step functions.

You can use the same method for building up a long formula

Our goal is to SUBSTITUTE the spaces for nothing so we start with that. We then have to pull it in from another sheet so

we use VLOOKUP for that, etc., etc.

You always start with the innermost formula, the end result that you are looking for. Then you use other formulas to help

you deal with the different situations you’ll have to deal with working with large datasets.

Resources to Become an Excel Expert:

This article is the tip of the iceberg. Check out these other resources for more tips.

Resources For Showing Data:

Excel Formulas will help you deal with data, how you present that data is a whole other animal. Check out this awesome

Excel Dashboard course if you’re interested in using Excel for reporting.

Resources For Excel Help:

Mr. Excel

No matter how good you get at Excel there is too much for one person to be great at everything Excel can do. The Mr. Excel

forums are the place to go for any questions. They have a very active community around that site and you can probably

get your excel questions answered very quickly.

OzGrid

There are a ton of examples for how to do just about anything in Excel or VBA on Ozgrid.

Learning Excel Macros and VBA

Formulas are awesome by themselves. Using VBA to create Excel Macros in combination with Excel Formulas is how you become

a true Excel expert.

I completely automated a business analyst job I had at a fortune 500 company using these methods.

How I Became the “Excel Expert”

It was my 3rd year in college and I went to intern for an options trading firm. There were some brilliant people at this

place and even the janitors knew Excel better than I did. The intern in there before me was a software engineer who had

basically written a ton of macros that automated almost every daily task I had to do. Great! I thought, I’d have the

easiest time doing the daily boring tasks and I could spend time learning how to be a trader.

Well, the week after he left, EVERYTHING BROKE. None of the macros were working and they all depended on each other. “I

was never trained on this” I thought, because the software engineer had only told me what buttons to push and when, never

how to do it manually. I started giving out wrong information, needing to ask busy people to take time to help me, and was

just generally helpless. I did the best I could, but I didn’t even know most excel formulas, much less how to read and

write VBA code and create Excel Macros.

Necessity, the Mother of Invention

Then one day I was taken aside and my manager said, “I know the kid before you did all this stuff, and you weren’t brought

here to write code, but if you can’t fix it and start giving us the correct information on time, we’re going to have to go

in a different direction.” WHAT?! I thought. I hadn’t been prepared or trained for this, and code that another person

wrote is not working, and I’m going to get fired for it? I knew he meant it. I had seen interns get fired from here, it

wasn’t pretty. It was an open trading floor, you didn’t get pulled aside into someone’s office, you were unceremoniously

escorted off the floor and everyone knew what happened.

So, I had a goal to meet, and I had to meet it quickly. I set off to learn VBA, Excel Formulas, and how to get all these

Excel macros working. After about 2 weeks of waking up at 5:30am, struggling through the day with the help of others, then

teaching myself Excel until about 2am, I had learned enough to read the code that was written. After about a month I

became proficient at it. After about 6 months I knew enough to write any Excel formula I wanted, read & write VBA code,

and solve any Excel problem I had without doing manual work in Excel.

Learning it was a pain in the butt! There was no single resource to learn what I needed to learn. I had to go all over

the web to find things or ask questions on Mr. Excel. That’s why I created a course on learning Excel. I put all my

knowledge gained over the last 5 years, all my tips & tricks into this Excel VBA tutorial.

About the Author:

excel expert with years of experience using and teaching Excel. He has created advanced systems

using VBA code, advanced dashboards using Excel and other business intelligence software, and he continues to build

new ways of viewing and analyzing data in Excel and automating those processes. He teaches Microsoft Excel VBA Video

Tutorial on Udemy.

I was astounded by how little some people knew about Excel and they use it every day! I decided to start teaching people

how to use Excel and utilize the true power of this program to make their lives easier. Throughout my video courses I teach

you everything you need to know to become the Excel expert, to spend less time doing stuff manually Excel, and to make

your life a whole lot easier all using Excel.