Please enable JavaScript.
Coggle requires JavaScript to display documents.
Excel Skills for Business - Coggle Diagram
Excel Skills for Business
Essentials
formulas
sum, autosum
average, min, max
cell references
absolute cell references
cmd + T
borders, alignment, format painter, styles and themes, number formats
managing rows and columns, find and replace, filtering, sorting, conditional formatting (rules)
printing
orientation, margins, scale
page break, headers and footers, print titles
charts
Intermediate 1
combining text: concat, &
changing text case: upper, lower, proper
extracting text: left, right ,mid
finding text: find
date calculations: now (gives date and time), today, yearfrac
multiple worksheets and workbooks
arrange: tiled view
3D formulas
linking workbooks
consolidating
by position
by category (reference): just check additional box for following labels
named ranges
letter, numbers and underscores only
defined name: under formulas tab
create from selection
count functions: count, counta, countblank, countifs
adding with criteria: sumifs
sparklines
advanced charting, trendlines
tables
creating and formatting
sorting and filtering
total row
automation with tables
autoextension
subtotal
pivot tables
Intermediate 2
data validation
stop
warning
dropdown lists
advanced conditional formatting
logical functions
IF
nested ifs
iferror
ifna
AND
OR
return true or false values on its own only
CHOOSE
must separate values via a comma
VLOOKUP
VLOOKUP can only be used on lookup data that is organised vertically
if not, must use HLOOKUP function
lookup data must be arranged from smallest to largest
exact match (FALSE / 0) vs approximate match (TRUE / 1)
MATCH
use it with VLOOKUP to make column number more flexible
INDEX
error checking in excel workbooks
error checking
formula calculation options
trace precedents and dependents
evaluate formula, watch window
protecting workbooks and worksheets
workbook level
worksheet level
structural level
SUMPRODUCT
what-if analysis
data table
goal seek
only allows for single input
scenario manager
good to use named ranges
solver
Macro
recording a macro
do not save as xlsx
can save as xlsm, xlsb or xltm
running a macro
editting a macro
Advanced