Description

You will learn how to use formulas, Excel Analysis Tables, conditional formatting and many more data analysis features to help you in your work. 

To get the most from this course

You will have attended the Burningsuit Microsoft Excel Level 1 Course.  Or you feel reasonably competent in using worksheets to enter data and do basic formatting work and you now want to find out how to analyse your data using formulas, functions and more powerful Excel analysis tools.

What this course will do for you

A large percentage of your organisation’s data is held in spreadsheets but at the moment, you’re struggling to extract the information you need.

By completing structured exercises, using data relevant to your work, this course will increase your competence in day-to-day data analysis making you more efficient and productive. This course also prepares you for moving on to the Microsoft Excel Level 3 Course.

What you'll learn

Review of Excel Formulas

  • Accurately enter an Excel formula

Review Basic Excel Functions

  • Accurately enter SUM, MIN, MAX, AVERAGE, COUNTA and TODAY functions.

Absolute References

  • Accurately enter a formula that uses absolute references.

The IF Function

  • Construct an IF function correctly to enter data into cells based on the result of a test.

Sorting and Filtering Data

  • Sort data into alphabetical or numeric order.
  • Use Custom Sorting to sort to more than one level.
  • Filter data to show only the data you want to see.

Creating Subtotals

  • Create subtotals on sorted data.
  • Use an Outline to analyse Subtotals

Using an Excel Table

  • Use Table Styles to format large amounts of data for readability.
  • Use Table Styles to analyse data.
  • Using Quick Analysis to format as a Table.

Using Slicers in an Excel Table

  • Inserting Slicers
  • Using Slicers to filter data in an Excel Table
  • Customising Slicers.

 Conditional Formatting

  • Analyse data by applying conditional formatting based on criteria eg: fill with red all cells greater than 100.
  • Using Quick Analysis to apply Conditional Formatting*

Using Flash Fill

  • How get Excel to automatically fill columns with “patterns” of data.
  • Examples of using Flash fill.

Introduction to the VLookup Function

  • Correctly construct the Vlookup function to find data based on a lookup value.
  • Using Vlookup to find values in an Excel Table

Using Quick Analysis

  • Entering formulas using the Quick Analysis feature.

Creating Charts

  • How to insert and modify a chart to show your data.
  • Work with different charts
  • Using Quick Analysis to create charts

Paste Special

  • Instantly swap rows and columns of data around.
  • Link cells into other sheets accurately.
  • Paste only the values from cells to remove a formula.

Printing Techniques

  • Work with Page Layout View
  • Print Titles so that headings repeat on new pages.
  • Control the printing of Headers and Footers