Login & Start Learning!

or

VIDEO PLAYLIST

Testimonials

Patricia Henry

“During my last semester of college, I only knew a little bit of Excel and was in the job hunt for my post grad life. I purchased the student packet from Excel Tutor and learned may more than I ever thought possible about Excel. I am so excited to put all my new Excel knowledge on my resume.”

Patricia H.
David Phillips

“ I’ve been working in the insurance industry for a few years now and have been trying to get my fellow colleagues to understand Excel and perform at a more efficient rate. I found this site and after completing the courses myself, I now have my entire department completing these courses! ”

David P.
Phil Banks

“My boss told me that I needed to find a way to be “more efficient and effective” for my daily tasks. That’s when I went hunting for an online Excel course. Excel Tutor was the answer! I learned so many tricks and I even received a promotion in my job last week due to what I learned from this website.”

Phil B.

Excel Essentials

This course uses realistic business scenarios to show practical application for each technique. It is a comprehensive overview of the critical functions, applications and concepts necessary to proficiently use Excel.

Many people say they "know Excel" but after going through this course, you really will! The learning style includes on screen visual tips and directions to better accommodate visual learners. The audio style is casual and laidback to provide a little extra entertainment and humor.

I hope you enjoy my course!

Support Files:

All-Templates.zip

Videos included in this Course:

Basics:

Excel is the ultimate calculator. But with thousands of rows and columns, it can be quite intimidating! But don't worry, this video will have you pointing and clicking your way through formulas in not time:) And, we even cover how to fix your mistakes so you'll be golden.

Formatting

(6m 50s)

Once you have completed your spreadsheet, it is a good idea to make it look pretty before presenting it to your boss. Even if your calculations aren't the most complicated (or correct...), aesthetics can go a long way. In this video, I walk you through some basic formatting techniques so you can ensure all your spreadsheets look nice.

Initially, Excel seems like a foreign language and that's partially because it is...lol. But don't worry, this video will help you to understand exactly how to input formulas into Excel and use its input messaging to your advantage. These foundation skills will help you greatly throughout your Excel experience.

This video covers the difference between relative, semi-relative and absolute references. Understanding referencing will make life much easier for copying formulas over to fill in large areas without having to edit each formula. You will also learn some great keyboard shortcuts for copying, pasting, cutting, and editing your formulas.

Excel spreadsheets can contain lots of data and thus it is nice to be able to zoom in on certain areas. Then, once you finish creating your masterpiece, you go to print and...well...if you have ever tried to print a large Excel sheet you realize it's not as easy as clicking File and Print. This video will show you how to print exactly what you want, how you want it, and includes a special BONUS! You'll have to watch to find out:)

Logic Functions:

IF Function

(5m 18s)

The IF function allows you to check if a certain criteria is true or false. It is a very common business function and this video will help you to understand its uses and how the function works. By the end of the video, the following should make sense: Logical Criteria, Video = "Good", True - leave me a good rating and comment, False - rewatch the video, lol...and leave me a comment with your question and I'll make it better:)

Nested If

(4m 13s)

If you want more than two options for an If function, you are going to have to Nest one IF function within another. I call this "InFunction." Oftentimes, your analysis may have more than two results and nesting one "IF" function within another "IF" function will allow you get as many different results as you want (well, technically Excel 2010 only allowed up to 64 IFs but do you really need that many "IFS" - there is probably an easier way...)

And Or

(4m 17s)

The And & Or functions are logical functions which are often used to evaluate certain business decisions. This video will help you familiarize yourself with these functions so that you can start applying them to real world situations.

If And Or

(12m 53s)

This video explores how to combine the IF, AND, OR functions together. Business decisions often require multiple logic rules and being able to easily combine these functions together will greatly enhance your value. This video uses the logic function to see which employees get a raise based on certain criteria. Mastering these logic functions will help put you in the eligible for a raise category!

Lookup:

The Vlookup function is a powerful business function used on a daily basis. The last portion of the function allows you to choose "Approximate" or "Exact." Here, we learn how the Approximate version can be used when our data is in a range. For example, we want to look up a price based on the order size. Excel is able to look at a pricing table and return the correct price by approximating where in the list the value would fall. This function is used for numerical data when we have ranges.

The Vlookup function allows you to look up a value in the leftmost column of a table and return a value in the same row in the specified column. This function is extremely useful in business. The Exact arguments is used when you are looking up a specific name or exact text. It is most commonly used when you are looking up non-numerical data.

This video explores combining Index and Match functions to look up a value in a specified area. These functions are often combined and its a good idea to become familiar with how they work together.

This video addresses the issue of the column number changing in your Vlookup formula. Sometimes, you want to pull data from a different column and are forced to go into your Vlookup formula and change the column number - not fun. The Match function allows you to dynamically change the column number by referencing a specific input cell. So now, if you want a different column of data, you can just specify in an input cell and the formula will update for you.

The Vlookup function requires you to specify a table to look up the data. But what if your data is sometimes found in a different table? The Indirect function allows you to dynamically change the lookup table in an input cell. So instead of editing the Vlookup formula, you can now specify the table name in a separate cell and Excel will automatically look up the value in the correct table.

Sometimes your data is not organized in nice easy to read tables. Rather, it is all compiled in one single list and you don't want to go bother the IT department about the data...so, you are going to have to manage on your own. This video demonstrates how to match multiple criteria in a lookup function to find exactly what you need - even when your data is not super organized. Master this trick and the IT worker may be asking you for advice next time! Ok, not likely but it's still really cool:)

Text Functions:

Learn how to use the Concatenate function to combine text from multiple cells into a single cell. The Trim function is great for cleaning up extra characters in your data. The Proper, Upper and Lower functions will help you to adjust the case of your text. These functions are great for correcting data entry mistakes.

Understand the subtle differences between the Substitute and Replace functions. The Substitute function changes text based on the contents whereas the replace function changes text based on its location. This video helps you to understand when to use each function.

Learn how to use the Left, Right, Find and Len functions in Excel to extract different portions of text from one cell into another cell. These functions are great for manipulating data to get just the part you need.

What If Analysis:

Goal Seek

(8m 2s)

The Goal Seek functionality is great for finding target values. Often times, we want to know how many units to sell to break even, or what our selling price needs to be to make a certain amount of profit. Sure, we could do all the algebra to solve for it - but who wants to do that? Learn how awesome Excel can be with this powerful tool!

The Scenario Manager allows us to analyze the effects of multiple changes to our forecast. Oftentimes, we want to know what happens if the economy is really good, average or bad. This macro environment change affects many of our inputs. The Scenario Manager lets us easily manage these different scenarios to analyze our profitability (or any other variable of interest) under each scenario.

Data Tables

(5m 50s)

Data Tables are great for sensitivity analysis. Sometimes, one variable is so important to our model, that we want to see what happens if we change just that variable. Excel also allows us to change two variables when doing our sensitivity analysis.

Charts:

Line Charts

(9m 21s)

Line charts are great for tracking data over time. Sometimes we want to compare different metrics on the same chart to see if they are moving together, or diverging. Unfortunately, one number may be measured in dollars ($) and another in percentage (%). When numbers are in very different scales, they are difficult to display in a meaningful way on a single chart. But, this video shows a great trick around this problem by applying a secondary axis, and like magic, the data can be compared!

Column Charts

(13m 24s)

This video looks at a basic column chart and shows how a little 3D effect can go a long way. Then, we show how stacking columns can help us compare revenue and expenses in each year. Finally, we throw profit margin into our revenue column chart - why? - because what is the point of making more sales if you can't keep the money?...

Pie Charts

(7m 28s)

Pie Charts are a great tool for analyzing dessert...ahem...I mean categorical data. In this video, the pie charts show a visual distribution of our website visitors over the two years. I also show you how to adjust the legend and data labels to maximize your charts impact!

Stacked charts are a great way to analyze categorical data over time. In this example, we analyze the changing mix of website visitors over time by stacking the values in each year. Stacked charts make it easy to visually see changes in customer mix.

Scatter charts are used when we have one variable the predicts another variable - meaning each point on the graph is a combination of two variables. In English - we are going to explore how hours studying affects your test grade. Naturally, the chart shows a positive relationship between studying and your test score. I even through in a trend line with basic regression analysis for some statistical fun:)

Data Tables:

Learn how to create a data table. Data tables are a great way to easily format your data and offer great options for analyzing and organizing the data. This video shows how you can sort the data in different orders.

Data tables are a great way to consolidate data and filtering lets us choose which data we want to see. The total row is a great feature that automatically updates after we filter our data. This video simulates a real world application of data table filtering and using the total row to answer business type questions.

Database Functions:

This video shows the power of the SUMIF, COUNTIF, and AVERAGEIF functions. These are great if we only want to analyze a specific segment of our data. Sure, we could filter it and use the total row to find out the answer, but then if someone changes our filter...well...then our answer is gone:( So these functions are great for analyzing data without worrying about how it is sorted or filtered.

This video uses SUMIFS, COUNTIFS and AVERAGEIFS allowing us to set up multiple criteria prior to doing our calculation. Now, we can verify multiple conditions before doing our calculation.

Pivot Tables:

Before you can start analyzing a pivot table, it helps to know how to make one to begin with:) This video shows you how to easily create our own pivot table and understand how to manipulate the fields to display the data you want to see. We also demonstrate how to do some frequency analysis with running totals as actual numbers and percentages.

Pivot tables can be used to display data in a variety of calculations. We can look at a percentages of the grand total or analyze particular segments of our data. This video demonstrates some real world applications of the power of pivot tables with the percent of row total, percent of column total and percent of grand total options.

When analyzing large amounts of data, we often want to only view a certain segment. Pivot tables allow us to filter our data so we can look at relationships in a subset of data. For example, if we have national data, we might want to look only at the performance of our advertisement in Florida - thus we would filter the data to only show values which are from Florida. Why Florida though? I happen to live here so I'm a little biased:)

Sheet Management:

This video demonstrates multiple ways of naming, inserting, and deleting sheets. Naming sheets can help you organize your data and lets your user know where to find information. Naming and inserting sheets is all fun and dandy but be careful when deleting sheets - This action can't be undone! So save your workbook beforehand just in case;)

Hiding sheets is great when you want to leave data in your workbook but don't want to present it to your audience. Naturally, you will want to know how to unhide sheets - not only for sheets you hid, but when someone else sends you a workbook. You never know what treasures you'll find! Also, moving and copying sheets can help you save time by avoiding duplicate data entry and better organize your workbook.

Grouping

(5m 18s)

Learn the power of grouping! When sheets are grouped together, we can edit all of them at the same time. This can be extremely useful when we are analyzing multiple departments or employees all with the same template. Also, grouping can be done within a formula to save time referencing multiple sheets.

Grades Analysis Project:

In this part of the project, we cover how to format our data to make it a little nicer before getting started. You'll learn how to adjust column widths, create a nice header row, and some cool keyboard shortcut formatting. It's always a good idea to make your spreadsheet look nice before doing calculations as it makes it much easier to work with.

Calculate each student's grade and see who mastered their course! and who, well, didn't do to well...We also apply the Vlookup function to assign each student a letter grade based on their percentage score.

In this video we calculate some basic statistics with the MIN, MAX, AVERAGE and STDEV functions followed by creating a frequency distribution of the grades. This distribution lets us see how many students earned each grade as well as the percentage of students earning each grade.

Data Table

(5m 18s)

This video covers how to create a data table in Excel. Data tables allow us to sort and filter our data so we can analyze different segments of data. It also covers how to use a Total Row for some quick and easy calculations.

Sometimes a list of numbers isn't the easiest way to display data. So, in this video we create a beautiful chart (histogram to be precise) to show how the students did. The video explores a variety of ways you can edit your chart so you can customize it to match your company's style.

BONUS - Job Test:

Task 1

(7m 25s)

Task 2: 7

(3m 49s)

Task 2: 8

(6m 56s)

Task 3

(10m 8s)

Members Also Watched

  • Introduction (2m 4s)

  • Task 5: 6 – 10 (3m 36s)

  • Task 5: 1 – 5 (2m 54s)

Loading...