Data & Decisions
Courses on the reporting that shows whether the work has changed: Excel, Power BI and Tableau, from cleaning the data to a report a manager uses.
Courses
Advanced Excel
The Excel that reporting depends on: lookups and logical functions, Power Query for cleaning data, pivot tables, dynamic charts and validation, so the numbers a manager sees are reliable.Introduction to Excel
- Overview of Excel interface
- Understanding workbooks, worksheets, and cells
- Navigating through rows and columns
- Range (Named range & Applications)
- Basic keyboard shortcuts
Reference and statistical functions
- Understanding the reference, Relative, Absolute, Mixed
- SUM, AVERAGE, COUNT, COUNTA, COUNTBLANK
- MAX, MIN LARGE, SMALL
- COUNTIF, COUNTIFS, SUMIF, SUMIFS, AVERAGEIF, AVERAGEIFS
Logical and lookup functions
- Logical Functions
- Simple IF – Analysis using criteria
- IF with other functions while dealing with multiple criteria patterns
- Nested If & IFS Function
- Lookup Function
- VLOOKUP, HLOOKUP
- Using Double VLOOKUP relating multiple data sheets
- To make our search and data retrieval dynamic using Index and Match
Advanced charts
- Creating dynamic charts with named ranges
- Advanced formatting options for charts
- Using combo charts and secondary axes
- Trendlines and data labels in charts
Data validation
- Dynamic dropdown lists using named ranges
- Creating dependent dropdowns
- Customizing error messages and input restrictions
- Using data validation with formulas
Power Query and data transformation
- Introduction to Power Query for data import and transformation
- Merging and appending queries
- Cleaning and shaping data with Power Query
- Loading data to the Excel workbook
- Unpivot columns and understanding data culture
- Important transformations Promoting headers, data type importance, Split columns, fill down and up removing junk, handling errors
Date and time functions
- TODAY, NOW
- EOMONTH
- DATEDIF, NETWORKDAYS, NETWORKDAYS.INTL, WORKDAY
- Aging report
Pivot tables and reporting
- Working on summary and quick reporting using pivot table understanding the table importance and making the report dynamic
- Using appropriate form of pivot using different techniques and learning value field settings
- Grouping the dates and working on analysis over time
- Report page filter technique
- Slicer and Timeline slicers
- Using appropriate visual to prepare a Dashboard
Conditional formatting
- Cell rules
- Top to bottom rules
- Data bars, Gradient fill, icons set
- Using different formulas for custom formatting
Text formulas
- Concatenate
- Upper, Lower, Proper
- Left, Right, Mid
- Text to columns
- Trim and Clean
- Search, Isnumber, Istext
Power BI
Building Power BI reports from raw data to a shared dashboard: cleaning data in Power Query, modelling it, writing DAX measures, and publishing to the Power BI Service.Business intelligence and how Power BI fits together
- How BI analysis differs from spreadsheet analysis
- Components and architecture: connectors, Power BI Desktop, Power Query, Power BI Service
Your first report
- The Power BI Desktop interface
- Loading text and CSV files
- Core visuals: bar and column charts, cards, funnel, tree map, pie, slicers
- Themes and formatting for a quick report
Getting and cleaning data
- Excel sheets and tables as sources
- Cleaning unstructured and semi-structured data in Power Query
- DAX basics: calculated columns, measures and calculated tables
Data modelling and DAX
- Relationships, star and snowflake schemas
- RELATED, CALCULATE, FILTER, COUNTROWS
- Combining filters with AND and OR logic
- Waterfall, KPI, table and matrix visuals, tooltips and small multiples
Time intelligence
- Bi-directional filters and drill-down
- Date tables and time intelligence functions
- Year-on-year, quarter-on-quarter and month-on-month variance
Publishing and sharing
- Conditional formatting
- Publishing from Desktop to the Power BI Service
- Workspaces and access
- Reports versus dashboards
- Exporting to PDF and embedding in PowerPoint, SharePoint or a website
Tableau
Tableau from the start: connecting to data, building views and maps, table calculations, and sharing dashboards.Introduction
- Tableau Desktop and the Tableau Product Line
- Application Terminology
- Visual Cues for Fields
- Getting Started in Tableau Desktop
- Elements of a Visualization
Connecting to Data
- Creating a Live Data Connection
- Saving and Editing a Data Source
- Metadata Grid
- Understanding Changes to Data
- Data Connection Options
- Sharing Options
Simplifying and Sorting Your Data
- Data Filtering
- Creating Date Filters
- Sorting
Organizing Your Data
- Using Groups
- Creating and Using Hierarchies
Slicing Your Data by Date
- Working with Dates in Tableau
- Using Discrete Date Parts
- Defining a Fiscal Year
- Creating Custom Dates
Using Multiple Measures in a View
- Using Measure Values and Measure Names in a View
- Combo Charts
- Combined or Shared Axis Charts
- Creating Dual Axis Charts
Showing the Relationship between Numerical Values
- Creating Scatter Plots
- Using Sets
Mapping Data Geographically
- Mapping in Tableau
- Geographic Mapping
- Background Maps and Layers
- Navigation and Selection in Maps
Viewing Specific Values
- Creating Heat Maps
- Creating Crosstabs
- Creating Highlight Tables
- Grand Totals, Sub-Totals, and Changing Aggregation
Customizing Your Data
- Calculation Types
- Parts of a Calculated Field
- Options for Creating Calculated Fields
- Strings, Dates, and Type Conversion
- Functions
- Calculations and Aggregations
Analyzing Data with Quick Table Calculations
- Calculations Using Quick Table Calculations
Showing Breakdowns of the Whole
- Pie Charts and Parts of the Whole
- Creating Tree Maps
Viewing Distributions
- Bins and Histograms
- Box and Whisker Plots
Highlighting Data with Reference Lines
- Using Reference Lines
- Reference Bands
- Using the Analytics Pane
- Instant Analytics
Making Your Views Available
- Building Dashboards & Its Actions
Python foundations for data
Python from the start for people who work with data: loops and conditions, functions, the core data structures, reading CSV and JSON files, and the NumPy and pandas basics that most analysis starts from.Loops and conditions
- for and while loops
- if, elif and else
Functions
- Writing and calling functions
- Arguments and return values
Lists, tuples, dictionaries and sets
- Indexing and slicing
- Adding, changing and removing items
Files: CSV and JSON
- Reading and writing CSV
- Reading and writing JSON
NumPy and pandas essentials
- Arrays and basic operations
- Loading, filtering and summarising a table
Training for your own team
We run these courses for companies, online or in person. Tell us who needs training and what their work involves.
Ask about training