Navigation within the sheet

• Necessary shortcuts, ways to move the cursor, proper alignment of data
• How to move the selection (without data)?
• Techniques for selecting large data, that is, no more dragging the mouse over 10,000 rows 🙂

Copying the cell contents, paste options

• Communicating through the clipboard between Excel, Word and Power Point
• Linking between Excel files – a change in file A automatically propagates to file B
• Linking cells with Power Point – a change in Excel automatically propagates to P.P.

Moving, deleting, inserting cells along with their contents

• Swapping columns / rows (not copy-pasting, but actually swapping)
• Inserting cells, rows, columns between existing ones
• Removing cells, aka “a hole” in the sheet
• Problems with Conditional Formatting rule while inserting or deleting rows

Search by formatting

• Searching for cells with the same fill / font color
• Selecting cells with inconsistent formatting (e.g., in a data series, select only numbers formatted in euros, but not in dollars)
• How to locate and select all merged cells?

Recording and installation of macros

• Recording a custom macro
• Installing a macro and the ways to run it
• Security policies, macros as a potential threat to the company
• Collection of macros useful in everyday work

Formulas and addressing

• How to calculate only a selected expression within a formula?
• How to copy formulas that do not have the $ sign in them, but so the addresses in the formulas do not change?
• Naming ranges
• Names with relative addressing, dependent on the place of entry
• Range names, changing size accordingly to search formula results
• Tools for finding and fixing formulas that return errors
• Formula servicing: previewing the order of calculation steps when it produces an error or works incorrectly
• Analysis of the construction of a report built by another person: what do formulas count? Where do they get the data from? Where are these calculations used later?
• Automatic translation of formulas (e.g., PL <--> EN)
• How to deal with long, visually unreadable formulas ?
• Solving the problem of formulas in which we are forced to repeat the same expression several times (making the formula unnecessarily long and unreadable) ? (365)
• How to add $ signs to already existing, typed formulas ?
• Defining the new formulae without using macros (365)
• Optimizing computation performance. What slows down the spreadsheet conversion?

Lookup and reference functions

• Why VLOOKUP is an outdated function?
• Linear vs. binary search
• How to work around the limitations of VLOOKUP in Excel 365/in older versions?
• Overview and comparison of search / copy formulas: MATCH, INDEX and others
• ROW, COLUMN, INDIRECT along with practical examples

Operations on logical values

• How to check if “several conditions at once” are met or if “at least one condition” is met
• Multidirectional alternative: a function that produces several different answers as a result of a logic test
• Forked two- and three-way alternatives, cross alternative
• Using other logical operators that check conditions: …
• “no condition can be met”
• “at least one condition met, but not all at once”
• “one condition met, but not more”
• custom, e.g., “a minimum of two conditions met”, “all conditions met except two”

Array functions, nesting and building complex formulas

• Two meanings of the term “an array formula”
• Entering one and the same formula into multiple cells
• The revolution in Excel 365: dynamic arrays and their versatile capabilities
• Implicit intersection operator, referencing a dynamic array
• Array form of logical functions OR, AND, IF (operating on series of data)
• A complex array function as a program: constructing and testing the calculation steps
• Transforming arrays: expanding, vertical/horizontal merging, splitting, extracting specific rows/columns, etc.
• Sorting and filtering of arrays
• Examples:
• how to remove only letters / only numbers from text?
• a formula replacing Polish special characters in email addresses
• a named range dynamically changing its size from cell A1 down to the last filled cell in col. A (data may have empty cells)
• a formula marking dates of weekends and any specified holidays
• COUNTIF, SUMIF and other functions from this family are not case sensitive. How to perform the same calculation so that the condition distinguishes between lowercase and uppercase letters?

Filtering data 

• How to put the Filter on the data incorrectly? (examples of errors)
• When a regular Filter is not enough: handling the Advanced Filter tool
• How  to make a serial numbering (1, 2, 3, …→999), but only in filtered, visible rows?
• How to paste data onto the area on which the Filter works?
• FILTER function in Excel 365, specifying filtering with multiple conditions
• How to filter data that has headers on the left side? (Filtering “horizontally” instead of “vertically”)

Sorting data

• Sorting by cell colors
• Sorting horizontally instead of vertically (by columns instead of rows)
• Sorting texts by self-set, custom order rather than alphabetically
• The SORT function in Excel 365

Calculations

• Calculations with one and with multiple conditions
• e.g., sum up only negative quotas, and only from Warsaw
• e.g., average amount of purchases in the retail channel in Q2
• How to increase the numbers in several columns in one go by, say, 10?
• How can we count the cells of a given color? (Advanced: and how to count them with a formula?)

Date & time

• Use of date and time to determine the duration of a process (e.g., “134 minutes 12 seconds”, “3 days 2 hours 7 minutes”)
• Business weeks of the year, e.g., WK 26
• Calculation examples:
• multiplication of time: (8 x 45 minutes) + (2 x 15 minutes) = how many clock hours ?
• addition/subtraction of dates including days off: weekends, or freely given own dates
• remuneration by hourly rate
• mark the dates from the 25th to the 30th working week of the current year
• add to the date exactly 1 month, regardless of its length
• how many days are there in the current month? In Excel 365 this is easy, but how do you do it in older versions?
• production time: present the hours as an integer and the minutes as a decimal fraction (e.g., 3.25 is 3 hrs and 15 mins)
• Customizing date formatting, e.g., „1998-12-02 wednesday”, „12/2/98” „1998, 2 december”, 1 ਜੁਲਾਈ 1999 ਵੀਰਵਾਰ

Conditional formatting

• How to search for cells that use Conditional Formatting?
• Constructing own, fully customized Conditional Formatting rules
• highlight only employees without absences whose average salary is greater than 4k
• comparison: highlight only those cells in list A that are present are in list B
• Conditional Formatting highlighting of entire rows
• Malfunction of conditional formatting on long numeric strings (e.g., on bank account numbers)
• Let a conditional format of type “Color Scale” work on one row of numbers. How to set it up for multiple rows so that it calculates from each row separately?
• Form Controls (e.g., dynamic scroll bar, checkbox, radio buttons) controlling the behavior of conditional formatting rules

Data validation

• Building own custom validation rules, e.g.:
• only allow input of content into a cell if the adjacent cell “one above” is already filled
• do not allow to enter a past date
• Applying rules to existing data, clearly highlighting data that does not comply with the rule
• Double drop-down list (dependent on the choice on other drop-down list)

Working with texts

• Insertion symbols, e.g., µ ℮ φ • € № Ω ∞ ∆ ≠ (⌐■_■) 🐌
• Splitting texts based on the occurrence of a given character:
• e.g.: after the first comma, after the last space (easy in 365, advanced in older Excel versions)
• e.g., after each occurrence of one of the following characters: comma, space, full stop (all at once, when all occur in one cell)
• “Cleaning” text of double spaces, of non-printing characters
• Changing data type ( number <--> text )
• How to change the data type to text if the usual ” format as text” operation does not work?
• Building a function to remove all letters or all numbers from text

Preparation of data for analysis

• Introduction to database normalization
• Removing hidden characters
• How to check data type on large arrays? How to change data type to “text” when applying text format doesn’t work?
• Merged cells / Forced text breaks:
• why those features result in incorrect calculations?
• how to fix the problematic cells in bulk?
• how and where can it be used?
• How to “patch” cells merged horizontally and vertically in a large set of data?

Pivot Tables

• How to properly prepare data for P.T. analysis?
• Sorting P.T. manually and automatically
• Report clarity:
• hiding irrelevant calculations with a filter
• grouping ranges of numbers / dates, calculations based on these groups
• displaying calculations as % of column total
• company colors, formatting, report layouts, P.T. styles
• Creating charts based on P.T.
• P.T. analyzing data from several Tables simultaneously
• Custom formulas in P.T. (e.g., raising numbers by 23%)
• How to quickly refresh all P.T.’s in a workbook?

Sheet and workbook protection

• Password upon opening an Excel document
• Locking sheet tabs, their names, order, colors, other operations, etc.
• Blocking the entire worksheet or selective blocking only the selected cells
• Blocking hidden rows and hiding the content of formulas
• How to select only hidden cells?
• How to block editing of cells while still allowing sorting and filtering?
• How to recover a forgotten password without any additional tools?

Linking to external data with Power Query

• The concept of “get and transform”, or how to teach Excel repetitive import tasks
• Introduction to the Power Query editor
• Recording and modifying the import steps
• Checking and changing the data source
• Examples of automating common, frequently performed tasks:
• Import from .csv text file
• Merging table data from different worksheets and workbooks
• Merging tables “columns to columns” and “rows to rows”
• Downloading current exchange rates and other data from WWW pages
• Downloading price lists in PDF from WWW pages, and merging into a Table
• Conversion of a data layout from pivot layout to tabular form

Inserting links to…

• …web pages
• …files on disk (e.g., when you click on a link, Word opens a specific document)
• …cell ranges inside the current workbook
• Link as text in a cell, a floating button, or as a picture
• Inspection of links reporting errors

Tools for “what if…” scenario analysis

• Which variable should have a value so that the complex formula calculating from it will give the desired result?
• Cross matrix in the form of a table: variants of variable X against the variations of variable Y
• Simulation of calculation scenarios (e.g., pessimistic, optimistic, averaged) for multiple variables
• Scenario report: defining a set of complex conditions and constraints on both the input variables and the expected results

Comments & Notes 

• How to print notes along with data?
• How to copy a note to multiple cells?
• What is the difference between the new Comments and Notes? (365)
• How to add a common note to a range of cells?

Inserting photos

• Photos floating above the sheet or anchored in place, resizing according to columns / rows
• Inserting a series of photos from a folder, in series (e.g., into a product catalog)
• Sorting and filtering by photos
• Photo effects: borders, shadows, perspective, etc.

Printing

• Arrangement of data on pages, i.e. looking for a balance between readability and paper usage
• Custom pagination, i.e. putting contents in the header and footer (e.g., email, phone number, current date, etc.)
• Company logo in the header
• Repetition of column titles on all pages of the printout
• Printing of high volume data, i.e. wide tables that do not fit across a single page
• Printing to a multi-page PDF file

Charts

• Systematize the types of charts (in practice we use only 10-12 types)
• Comparison of components and proportions
• Techniques for drawing the MA / EMA moving average line
• Chart filtering, logarithmic scale, two different scales
• Schedule of a project progress on a Gantt chart
• Readability of the presented information. How to mislead the recipient?
• Visual presentation of numbers without using Chart Tool
• Is a chart always a good idea? In what contexts would a table work better?
• Can one chart draw data from two tables?