Description

With the use of the If function, Vlookup and Pivot Tables, you will have the necessary skills to extract information and insights from the data in your spreadsheets. 

To get the most from this course

You will have attended the Burningsuit Microsoft Excel Level 2 Course. Or you have been using Excel for a reasonable length of time and you are competent in basic spreadsheet analysis work and have used formulas and functions.  You now need to know how to use more complex functions and data analysis.

What this course will do for you

Although you’re a competent user of Excel, you’re still finding some aspects challenging and you feel frustrated because your existing knowledge keeps letting you down.  This course will increase your competence in data analysis helping you meet those targets more efficiently and also prepares you for moving on to the Microsoft Excel Level 4 Course.

What you'll learn

Naming Cells and Ranges

  • Correctly create named cells and use names in formulas accurately.

The IF Function & Nested IF

  • Construct an IF function correctly to enter data into cells based on the result of a test.
  • Use Nested IF
  • Using AND/OR Functions
  • Using AND and OR functions for more complex IF formulas.

Using an Excel Analysis Table

  • Use Table Styles to format large amounts of data for readability.
  • Use Table Styles to analyse data.
  • Using implicit names automatically generated by Tables.

Countifs and Sumifs Functions

  • Correctly construct the Countifs function to count up specific values in a range of values.
  • Correctly construct the SumIfs function to add up up specific values in a range of values.

VLookup

  • Correctly construct the Vlookup function to find data based on a lookup value.
  • Using Tables as the data source for Vlookup.

Pivot Tables

  • Construct and work with a Pivot Table to analysis data.
  • Drilldown on the Data Area
  • Use the Layout Options
  • Use different functions in the Data Area
  • Use the “Show Data As” Option

Creating Interactive Dashboards

  • Create dashboards using:-
  • Pivot Tables
  • Pivot Charts
  • Slicers

Concatenation

  • In a single cell, accurately string together cell values, text and formulas into a single value.

Flash Fill

  • How you can use Flash Fill to automatically populate columns of data modified from existing columns.

Data Validation

  • Prevent invalid data being entered into a cell.
  • Create “dropdown” lists.

SparkLines

  • Create Sparklines as an alternative to charts.
  • Quick Sparklines.

Advanced Conditional Formatting

  • Using formulas in Conditional Formatting.
  • Applying Conditional formatting to whole rows