Excel Advanced 2 Fridays


The Foundations & Concept of Data in Excel - our “3-2-1” Framework of Data
Three Types of Data

Dates
The challenge of “looks like date but does not behave like date”
What is a DATE?
Recommended Date Formatting – dd-mmm-yyyy
Extracting Month Name, Year, Day Name, Week Number, etc from a date.
Text
The main challenges
Cases
Spaces
Numbers
The ideal and recommended formatting – Accounting
Why Accounting is our recommendation
Formatting large numbers as thousands ( 1,234,567 as 1,234 k)
Formatting large numbers as millions with a decimal ( 1,234,567 as 1.2 M)

Two Sources of Data
Raw Data
Calculated Data

One Cell type – Range or Table

Data in “Excel Range” or in “Excel Tables” : The 1978 way of “Range” vs 2010 way of “Table”
Why copy formulas in the column after you have typed one?
Why expand “range” after addition of new row or column to data.
Use of Excel Tables – end of Excel “range” way.
Understand the benefits of using an Excel table over a range of data
Convert a data range into an Excel table
Review of the Excel table contextual tab
Quickly format a table with pre-defined table styles
Filter data within a table
Quickly add columns and rows into an existing table
Add a Total row to a table for quick calculations
Automatically add a function to all rows within a table
Learn the benefits of using an Excel table to create a PivotTable
Why name a Table with “tbl”?

The New era of PivotTables
The NEW way of Pivot Tables: Create a Pivot Table that works for LIFE –on refresh, all new rows and columns of data should get included.

The commonly faced challenges in a Pivot, and solutions for them.
Challenge #1: There are lot of empty cells in the report – how to have “zero” value in all of them.

The FIVE Critical Pivot Table Options
Format Numbers (Not Cells)
Report format: Style and Design Components to Format a Report
The Report: Sum, Count
The Report: Concept Of Data Cubes
The Problem Of Pivot Report not taking New Data
Show Amount as Of Total
of Grand Total or of Column Total – which is better?

Slicers for Data – avoid old FILTERS
Old Way of FILTERs vs NEW Way of SLICERS
Concept of Horizontal Slicers
Best practices for Slicer position and size
Use of multiple columns
Formatting Slicers using Styles
Setting Default Slicer Style
Deleting Slicers
Glue your slicers so that even Excel can not move them
Slicer Settings – best practices

Slicers for Reports
Slicers: One Pivot - Multiple Reports
Super glue your slicers so that even Excel can not move them
Positioning of Slicer with Pivot Tables
To Print or Not to Print Slicers.
Unlocking Slicers while Protecting a Sheet

Slicers for Pivot Charts and Dashboards
Slicers: One Slicers - Multiple Charts
Controlling Multiple Pivot Tables / Pivot Charts with one Slicer
Where to place slicers on the Canvass of Dashboard
The concept of “Connections”

Logical Functions
=IF()
=OR()
=AND()
=IF(IF(IF)) – Nested IF
=IFERROR()

The daily grind of data warriors - Lookup Functions 
=VLOOKUP()
How to lookup Vertically for data – data in other columns. 
Eliminate the fixed range challenge created by “$A$1:$M$500” range way of VLOOKUP. Use TABLES
Using VLOOKUP to find exact matches

How to provide insurance against current and future “#N/A” errors
Root Cause Analysis of why we get “#N/A” error and solutions to the root cause
#N/A Reason: Unwanted Blank Spaces (bsp)
#N/A Reason: Spaces that are NOT spaces in reality (Non Blank Spaces – nbsp)
#N/A Reason: Transactional Data is text where as lookedup data is numbers.
#N/A Reason: Transactional Data is number where as lookedup data is text
#N/A Reason: Spelling Mistakes

Looking across two different files without “$A$1:$M$500” range way. 

Using VLOOKUP to Group number oriented data like Revenue, Stock, Accounts Receivables, Employee Tenure, etc using APPROXIMATE match component of VLOOKUP.

Text Functions to clean data
=SEARCH()
=SUBSTITUTE()
=LEN()
=LEFT()
=RIGHT()
=MID()
=TEXT()
=PROPER()
=TRIM()
=CONCATENATE() (or “&”)

Date Functions
Is it a Date?
Calculate the exact service length in YEARS, MONTHS, DAYS without dividing by 365! Use function DATEDIF().
Find the number of days between two dates using =NETWORKDAYS.INTL()
Calander days
Working Days (without FRI-SAT as weekends)
Working Days (without Public Holidays and FRI-SAT as weekends)

Protection
Multiple Layers of Protecting your work in Excel
Layer 1: Protecting a complete worksheet
Objects Locked
Tables
Slicers

Layer 2: Protecting a part of a worksheet
Objects Locked
Tables
Objects Open
Slicers

Layer 3: Protecting a part of a worksheet
Objects Locked
Tables
Slicers

Objects Open
Any specific column

Layer 4: Protecting a Report
Objects Locked
Pivot Tables
Objects Open
Silcers

Layer 5: Protecting your Logic (Formulas)
Objects Locked
Cells with Formulas
Objects Open
Rest of the worksheet

Layer 6: Protecting your Structure of Workbook
Structure Locked
Move
Copy
Hide

Creating Interactive Dashboards Screenshots
Dashboard planning
Use of Filters inside Pivots.
Using Pivot Charts
Why keep formatting all Charts manually – let Excel do this work!
Using Slicers
Multiple Charts being controlled by several Slicers – the inter-activity
Protecting your Dashboard

Keyboard Shortcuts
Total keyboard shortcuts – nearly 485, classified into 36 categories by Team ExecuTrain
Which ones to Master – the priority
Absolutely Basic
Must Know
Nice to Know
Rarely Used
Use Mouse instead