Book a course

Duration: 1-day

Location: inhouse, online or at our UK locations

What you will learn

This course focuses on writing formulas, analysing data and outputting reports. Use the formula help tools to find and write formulas to output results from multiple questions.

Efficiently output accurate reports to analyse sets of data. You will learn how to use rules to automatically format data to make it stand out. We will also look at ways to compare different sets of data.

Those attending will also learn lots of time-saving tips and tricks.

Prerequisite

Attendees are required to be familiar with some of Excel’s basic functionality, for example, AutoSum, Filters and basic formatting.

Course outline

Calculating with Absolute Reference

  • What is an absolute formula?
  • Apply an absolute cell reference
  • Lock cells when copying formulas

Grouping Worksheets

  • Grouping sheets together
  • Inputting data into multiple sheets
  • Sum tables accross sheets

Linking to Tables

  • Linking to a source table
  • Link a table to another file
  • Manage files with links

The Function Library

  • Write formulas in the Function Library
  • Finding formula with Insert Function
  • Run COUNTA and COUNTBLANK stats
  • Count criteria in a list with COUNTIFS

Logical IFS Function

  • Output results from tests
  • Write an IFS function
  • Difference between text and numbers

Conditional Formatting

  • Enable text and numbers to standout
  • Applying colour to data using rules
  • Managing rules
  • Copying rules with the Format Painter

View Side by Side

  • Compare data in two Excel files
  • Compare data in the same file

Pivot Table Reports

  • Analysing data with Pivot Tables
  • Managing a Pivot Table’s layout
  • Outputting statistical reports
  • Controlling number formats
  • Visualising reports with Pivot Charts
  • Inserting Slicers for filtering data

Data Validation

  • Restrict data input with Data Validation
  • Quicken data entry with Data Validation

VLOOKUP Function

  • Retrieve data from an external table
  • Best practices for writing a VLOOKUP
  • A false type lookup
  • A true type lookup
  • Enhance formula results with IFERROR

Print Options

  • Getting the most from Print
  • Printing page titles across pages
  • Scaling content for print