Excel - Pivot Tables and Intelligent Database Design

Excel - Pivot Tables and Intelligent Database Design

Overview

Pivot Tables allow users to reach beyond the obvious data to design, arrange and manipulate data at a deeper level. Ensure that your data is cleaned and correct. Design databases which are robust and foolproof. Create stunning reports and graphics for ongoing ease of use.

Syllabus
  • Work with text features of Excel to clean imported data.
  • Understand how to construct an effective database within Excel.
  • Work with Tables to future-proof data entry.
  • Create Control Sheets for data variables.
  • Use Data Validation to force compliance with input standards.
  • Creating dynamic dropdown lists, with sub categories.
  • Create and use Pivot Tables.
  • Active Field Settings.
  • Creating Formulas in Pivot Tables.
  • Rearranging/filtering Pivot Tables.
  • Grouping data in Pivot Tables.
  • Slicers and timelines.
  • Replacing and adding to monthly data with ease.
  • Summarising data and presenting numbers effectively.
  • Updating and Refreshing options.
  • Working with totals and subtotals.
  • Control Pivot Table options and report layout.
  • Drill down to find the data behind the results.
  • Create Pivot Charts and use them in the design of mini dashboards.
  • Apply Conditional Formatting to Pivot Tables.