The Microsoft Excel Survival Guide

In this lecture you will learn how to use text to columns and you will also learn how to concatenate multiple columns.

In this lecture you will learn how to use the MID, LEFT and RIGHT functions and we will also do some more examples of concatenating columns.

In this video we will look at some basic formulas we will use to prepare our data and also some options to format the data to make it look more presentable.

In this lecture we will learn how to use the IF statement, from that we will use the more advanced SUMIF and COUNTIF statements. We will also do an example of an advanced nested IF statement.

In this video we will look at how to apply conditional formatting to our data.

In this video we will learn how to remove any duplicate values from our data.

In this video we will learn how to use VLOOKUPs to pull through data we need from a different datasets. We will also learn how to use anchors to make sure our formulas work correctly.

In this video we will learn how to build a pivot table. These are really important as we will be using pivot tables in our practical to slice and dice our data.

In this lecture we will learn how to build graphs off our pivot tables. We will use these skills to build our final dashboard in the practical section of this course

In this video we will run through what we will be doing in the practical section of this course.

In this lecture we will use a VLOOKUP to get the amount value

In this video we will use a formula to calculate total sales

In this video we will use an IF statement to determine the Availability column

In this video we will complete the rest of the VLOOKUPs to pull through all the other master data columns we need to complete our dashboard.

In this lecture we will concatenate the sales reps first and last name to get the full name column

Here will be calculate the year and year month values and add them to our dataset

Here we will build the pivot tables we need for the sales and transactions totals

In this lecture we will create pivot tables to identify our top and bottom 5 sales reps by sales total.

In this lecture we will create pivot tables to identify our top and bottom 5 products by sales total.

In this lecture we will create pivot tables to identify our top and bottom 5 stores by sales total.

In this video we will build our sales data table and add the sparklines to the data table

In this video we will build the availability pivot table. We will then build graphs for all of our pivots.

Here we will create the dashboard tab and add all the visuals we created to it.

Here we will add the total sales and total transaction totals to our dashboard

Here we will add the data table to the dashboard

In this lecture we will add the timeline and slicers to our dashboard

In this video we will format all the visuals and we will add a function to display the current date and time.

In this lecture we will link all our visuals to the slicers so they all get filtered when the slicers are selected

Here will will learn how to add data to our dashboard, how to hide sheets in the workbook and how to set the print area.

Here will will continue learning how to add data to our dashboard, how to hide sheets in the workbook and how to set the print area. We will also see how to reduce the file size of our workbook.

We will review what we have done in this course and discuss what you can do if you want to continue learning about data analysis and reporting.