Course Excel Analysing and Presenting Data with Dashboards and Pivot Tables
Download PDF
Course Duration
1 Day
This outline below contains an overview of a wide range of features within Excel that when used together can be used to create interactive visual reports and dashboards. The course will show delegates how these features can be used individually to present data but can also be used in combination to make powerful and insightful reports.

The course content has been pulled from a mix of our Excel courses, some content will be recapped to ensure all delegates are at the same level as they are used in conjunction with each other to effectively present data and build reports.
Public Scheduled Dates
<<
<
>
Public scheduled prices per delegate +VAT
Private Courses
Private courses start from £645 for up to 8 delegates
Prerequisites
    • Delegates should have attended the Excel level 3 course, or have equivalent knowledge.
This course is suitable for
Microsoft Excel 2010, 2013 and 2016
What's included
    • Lunch and refreshments when training is delivered at our training venues
    • Comprehensive course materials and exercises
    • Telephone and Email Support
Course Content
  • Preparing Data for Analysis
    Absolute Referencing
    Consider data types
    Organising data in way best suited to presentation
    Tables
    Creating Tables
    Advantages of using tables
    Adding data to tables
    Table Options
    Slicers
    Naming cells (Ranges)
    Giving text to cells and ranges of cells
    Create Name from Selection
    Create Names using the Name Box
    Names in formulas
    The Name Manager
    Functions used for data preparation
    Proper
    Upper and Lower
    Left, Right Mid
    Concatenate
    Text to Columns
    Remove Duplicates
    FlashFill
    VLOOKUP
    Understanding Lookup Functions
    Using VLOOKUP
    Approximate and Exact Matches
    Gathering data for analysis using VLOOKUP
    Fixing Common Lookup problems
  • Building Insightful Pivot Tables
    Creating a pivot table
    Fields to create row headings
    Fields to create column headings
    Data in the summary area
    Dynamically building a pivot table
    Rearranging fields in a pivot table
    Number formatting of the data area
    Moving pivot tables from one sheet to another or around the same sheet
    Deleting a pivot table
  • Summarising Pivot Table Data
    Managing subtotals and grand totals
    Changing the data field calculation (Sum, Count, Average etc.)
    Sort and Filter
    Drilling down to the raw data
    Building and using Report Pages
  • Grouping Pivot Data
    Grouping Date Fields
    Including Years when Grouping by Months
    Grouping Date Fields by Week
    Multi Grouping
  • Running Totals in Pivot Tables
    Create year on year total comparisons
    Setting Running Total base fields
  • Calculated Fields in Pivot Tables
    Setting the number format for a calculated field
    Limitations on calculated fields
    Adding a calculated field to a pivot table
    Creating calculating fields
  • Conditional Formatting For Pivot Tables
    Conditional Formatting Types
    Highlight Cell Rules
    Analysis of data using Top Bottom Rules
    Manipulate Icon Sets to fit data types
    Data Bars
    Colour Scales to create data heat maps
    Customising Conditional Formatting with formulas
  • Pivot Charts
    Building Pivot Charts
    Editing Pivot Chart Layouts
    Editing Pivot Chart Styles
    Using the built in chart filter buttons for presentation
  • Slicers and Timelines
    Building Slicers for easy and interactive filtering
    Formatting Slicers
    Pivot Table connections
    Timelines
  • Pivot Table Dashboards
    Developing Layout and Design approach to dashboard sheet
    Adding Pivot Tables and Pivot Charts to dashboards
    Adding dynamic filter and slicer buttons
  • Using Simple Macros to make Reports and Dashboards interactive
    Recording a Macro
    Run a Macro
    Delete a Macro
    Assign a Macro to a Button
    Viewing the Code behind a Macro
    Macro Security
  • Sample Dashboard Examples
    Sales and employee analysis example
    HR and location analysis example

 

Latest Course Reviews
"The course was very informative and engaging"

R.D. EGGER UK Ltd, Excel Analysing and Presenting Data with Dashboards and Pivot Tables

Following this training what are you going to do differently "We will use this to create internal KPIs dashboards"

R.D. EGGER UK Ltd, Excel Analysing and Presenting Data with Dashboards and Pivot Tables

Which part of the course did you find particularly valuable "Extremely knowledgeable training and very clear, consistent teaching style of the trainer. Very beneficial and informative training session."

Y.B. EGGER UK, Excel Analysing and Presenting Data with Dashboards and Pivot Tables

Would it be possible to use your feedback in our marketing we only use your initials and organisation name "Very beneficial and informative session with a subject matter expert!"

Y.B. EGGER UK, Excel Analysing and Presenting Data with Dashboards and Pivot Tables

Which part of the course did you find particularly valuable "Creating a dashboard"

S. Student, Excel Analysing and Presenting Data with Dashboards and Pivot Tables

"Creating a dashboard - has gave me loads of inspiration for how I can apply it within my work role, and the Macros training"

L.B. Greggs, Excel Analysing and Presenting Data with Dashboards and Pivot Tables

"Look out for quicker more efficient ways of summarising and presenting data"

L.B. Greggs, Excel Analysing and Presenting Data with Dashboards and Pivot Tables

"Another very useful course that will help me in my job role, brilliant, thank you."

L.B. Greggs, Excel Analysing and Presenting Data with Dashboards and Pivot Tables

"Made Excel interesting, thank you! "

J.G. Jelf Insurance Brokers, Excel Analysing and Presenting Data with Dashboards and Pivot Tables

"All of it was good value to me"

D.M. CMR UK Ltd, Excel Analysing and Presenting Data with Dashboards and Pivot Tables

"Trainer was very knowledgeable"

D.M. CMR UK Ltd, Excel Analysing and Presenting Data with Dashboards and Pivot Tables

"Really good course at an appropriate pace for everyone. I've been to a few courses by ITTS and all have been good"

M.W. Durham County Council, Excel Analysing and Presenting Data with Dashboards and Pivot Tables

"Excellent training experience. Thank you for answering my questions about other products such as Power Pivot/ Data Model"

C.R. Durham County Council, Excel Analysing and Presenting Data with Dashboards and Pivot Tables

"Good trainer and very clued up on subject"

H.G. Vantec, Excel Analysing and Presenting Data with Dashboards and Pivot Tables

 

Other Courses In This Series Microsoft 365 Copilot for Business Users - Introduction This one-day course is designed to help business users understand the basics of Microsoft Copilot for Microsoft 365, a productivity tool that uses artificial intelligence to provide real-time intelligent assistance within Microsoft 365 productivity apps including Word, Excel, PowerPoint, Teams and O Excel Level 3 This course will benefit those who are already up to speed with Microsoft Excel but would now like to automate some common tasks and apply advanced analysis techniques to more complex data sets. This Excel course is aimed at anyone desiring to gain the skills necessary to create macros, audit and an Excel Level 1 - Essentials Microsoft Excel is a spreadsheet program that can be used for storing, organising and manipulating data.This Excel introductory course will enable delegates to gain a good general understanding of the main features of Microsoft Excel, giving them the ability to produce standard spreadsheets, create Excel Level 2 In Microsoft Office Excel Level 1, delegates created, edited, formatted, and printed basic spreadsheets. This course will enable delegates to streamline repetitive tasks and display spreadsheet data in more visually effective ways. The target students for this course are students who desire to gai VBA Essentials using Excel This course is aimed at those people who find they have reached their limit with the menu items of Excel and need to modify Excel to be better suited to their environment, by creating their own functions and procedures.

 

Your Excel course can be delivered online or in-person.  You can choose your Excel path with introduction to advanced levels. Master Excel Business Intelligence and Power Tools, VBA and more.  Learn in-person or online with our virtual classrooms.  Your course can be delivered online, at your office or at one of our training venues, it's your choice. Small class sizes. Course materials included. Flexible course content. Expert trainers and tried & tested training methods. Course materials available online using your personal training account.  You can master Excel, please contact us to book a place or request a quote.
  • Call us on
    0191 377 8377
    to discuss your requirements.
    Mon-Fri 9am-5pm.
    Ask A Question
    I would like IT Training solutions Ltd to keep me informed with offers and news it believes would interest me*
    By submitting this form, you consent to IT Training Solutions Ltd storing your information in order to respond to your request.
  • Course Options
    Public Scheduled
    Public Scheduled
    Fixed content, fixed date, fixed location, fixed length, multiple organisations - All you need to do is choose which course you would like to attend and contact us to book!
    Private
    Private
    Flexible content, flexible date, flexible location, variable length, private to your organisation - Courses can be held at one of our training venues or at another location convenient to you. Training can be set over a number of days or weeks to allow for practice time between training sessions.
    Bespoke
    Bespoke
    Bespoke private training can be based on an existing course or a number of lessons taken from several courses, we can also write bespoke courses for you based on your in-house systems or development program.
    Virtual
    Virtual
    These courses can be attended from anywhere.
  • Virtual Training

IT Training Solutions Ltd
Oakville, 14 Durham Road West, Bowburn, Durham, DH6 5AU
0191 377 8377
ittrainingsolutions.co.uk
Printed:4/27/2024 7:37:54 AM