Working effectively in Excel. Optimising and automating work with reports

Programme

Day 1

Working with formulas and functions

• Combining functions in formulas to solve various tasks
• The concepts of helper functions, main functions, functions without arguments
• Examples of various combinations of functions
• More convenient function entry using line breaks

Mathematical and statistical functions

• The concept of «nested parameters» of functions
• AGGREGATE, ROUND, ROUNDUP, ROUNDDOWN, SUBTOTAL, SUMIF, SUMIFS, COUNT, COUNTA, COUNTBLANK, COUNTIF, COUNTIFS

Text functions

• UPPER, PROPER, LOWER, TRIM, CLEAN, FIND, LEFT, RIGHT, MID, LEN, TEXTJOIN
• Practical examples of using text functions

Date and time functions

• Specifics of how Excel handles dates and times
• Calculating calendar dates without functions
• NOW, TODAY, YEAR, EDATE, WEEKDAY, WEEKNUM, NETWORKDAYS, WORKDAY, WORKDAY.INTL
• Practical examples of using date and time functions

Logical functions and expressions

• Logical operators in Excel, logical values
• IF, AND, OR, IFERROR, IFS, SWITCH

Lookup and array functions

• MATCH, INDEX, OFFSET, FILTER, SORT, SORTBY
• Problems of the Filter tool in Excel, specifics and advantages of the FILTER function
• Combining the FILTER function with Power Query tables

Data validation functions

• Creating drop-down lists
• Creating logistics calculators using drop-down lists
• Using references to cells on other sheets and in other workbooks in formulas
• Using references effectively
• Creating a formula that uses values from another sheet or workbook
• Syntax of a reference to a cell on another sheet in the same workbook
• Syntax of a reference to a cell in another workbook
• Working with external links
• Changing the values of formulas with external links. Checking link status

Tools for working with data and formulas

• Formula Auditing
• Displaying relationships between formulas and cells
• Tracing precedents
• Tracing formulas that reference dependent cells
• Going to a precedent or dependent cell on another sheet
• Finding cells that cause errors in a formula
• Fixing errors in formulas
• Step-by-step evaluation of complex formulas
• The Watch Window

Connecting to data with Power Query

• Creating connections to various types of documents: CSV, XLS, Google Sheets
• Setting connection and data processing settings
• Managing data processing steps

Forecasting data with the Excel Forecast Sheet

• The Forecast Sheet tool
• Data requirements
• Specifics of how the FORECAST.ETS function works

The Data Validation tool

• Defining the data allowed for entry in cells
• Allowing entry of values from a drop-down list only
• Allowing entry of numeric values within specified limits
• Allowing entry of dates and times within a specified time interval
• Allowing text of a specified length
• Using a formula
• Displaying a message when incorrect data is entered
• Displaying incorrectly entered data in the table

Working with tables

• Working with the Table object
• General provisions
• Creating a table
• Using the Quick Analysis tool to create tables
• Table capabilities
• Filter drop-down lists
• Using a slicer to filter data
• The insert row
• The total row
• Editing a table
• Creating a calculated table column

Visual data analysis

• Conditional Formatting
• Conditional formatting
• The concept of conditional formatting
• The general approach to creating conditional formatting
• Types of conditional formatting rules
• Using a formula as a formatting criterion
• The Conditional Formatting Rules Manager
• Processing priority of conditional formatting rules
• Conditional and «unconditional» formatting
• Using the Quick Analysis tool in conditional formatting

Day 2

Sparklines — showing data trends

• Creating sparklines
• Adding text to a sparkline
• Customising sparklines
• Using the Quick Analysis tool to create sparklines

Charts

• Creating charts
• Choosing the «default» standard chart type
• Building a chart from data in non-adjacent cells
• Customising charts
• Changing the chart type
• Choosing a standard chart layout
• Choosing the chart style and colour scheme
• Changing the chart layout
• Chart element parameters
• Changing the chart location
• Filtering chart data
• Trendlines on a chart
• The concept of a trendline
• Chart types that support trendlines
• Adding a trendline to data series
• Choosing the most suitable trendline for the data
• Trendline parameters
• Using a trendline to create a forecasting formula
• Series lines, drop lines and error bars on a chart
• Series lines
• Drop lines
• High-low lines
• Up/down bars
• Error bars

Advanced chart modification

• Using Format Shape to visually adjust a chart
• Changing the chart design and structure using the Format Shape pane
• Creating your own chart layout
• Managing number formats in charts

Pivot tables and pivot charts

• General information about pivot tables
• General information about source data for pivot tables
• Creating a pivot table
• Choosing the source data
• Main objects for working with a pivot table
• Using templates to create a pivot table
• Main areas of a pivot table
• Working with the pivot table field object
• Additional parameters of the pivot table field window
• Creating a pivot table from several source data tables with relationships
• Changing the summarisation operation for totals
• Applying additional calculations to total values
• Renaming control fields
• Using pivot table options to change the pivot table layout
• Filtering by selection
• Filtering by conditions
• Comparative analysis of several filtering options
• Creating a slicer
• Disconnecting a slicer
• Deleting a slicer
• Slicer style settings
• Slicer settings
• Creating a timeline
• Managing the timeline
• Customising the timeline design
• Pivot table styles
• Pivot table style options
• Pivot table layout
• Element visibility options
• Action options
• Data options
• Extracting the source data for a particular total value
• Using pivot table total values in Excel formulas
• Sorting options
• Creating calculated fields and items (calculations)
• Grouping options

The pivot chart

• Creating a pivot chart
• Choosing the pivot chart style and type
• Visual settings of the pivot chart
• Main objects for working with a pivot chart

Exit test on knowledge of the Excel interface and principles of operation

• Practical module of the exit test
• Theoretical module of the exit test