All answers

The basics

• Add up a column of numbers
• Find the average of some numbers
• Find the highest or lowest number
• Count how many cells hold numbers
• Count cells that are not empty
• Count empty cells
• Keep one cell fixed when copying (the $ sign)
• Round a number
• Always round up or always round down
• Multiply two cells
• Multiply two columns and add the results
• Work out a weighted average
• Find the middle number (median)
• Find the most common number
• Find the 2nd, 3rd or nth largest number
• Work out a percentage of a number
• Raise or lower a number by a percent
• Find the remainder after dividing
• Find a square root or a power
• Remove a minus sign (absolute value)
• Measure how spread out numbers are
• Check if two columns move together

How do I...

• Make a pivot table
• Make a chart or graph
• Add a tiny chart inside a cell
• Color cells automatically
• Filter my data
• Sort my data
• Delete duplicate rows
• Split a column with the Text to Columns tool
• Let Excel copy a pattern (Flash Fill)
• Turn a list into a table
• Copy a formula down a column
• Add a dropdown list
• Only allow certain entries in a cell
• Find and replace
• Keep the top row or first column in view
• Fit long text inside a cell
• Merge cells or center a title
• Add or delete rows and columns
• Hide or unhide rows and columns
• Make a column fit its contents
• Hide the gridlines
• Show numbers as currency, percent or a date
• Add a note or comment to a cell
• Print my sheet on one page
• Repeat the heading row on every printed page
• Print only part of a sheet
• Stop people changing cells
• Put a password on a workbook
• Save a sheet as a CSV file
• Get back a file I did not save
• Paste values without the formula
• Flip rows into columns
• Use a value from another sheet
• Add the same cell across many sheets
• Give a cell or range a name
• Learn the most useful shortcuts
• Work backward to a target number (Goal Seek)
• Test many scenarios at once (Data Table)
• See all the formulas in a sheet
• Find out where a number comes from

Fix an error

• Fix the #REF! error
• Fix the #SPILL! error
• Fix the #N/A error
• Fix the #VALUE! error
• Understand the #DIV/0! error
• Fix the #NAME? error
• Fix the #NUM! error
• Fix the #NULL! error
• Fix the #CALC! error
• Fix a cell full of #### signs
• Fix a circular reference warning
• Fix a formula that shows as text
• Fix formulas that do not update
• Fix a date that shows as a number

Look things up

• Find a value in a table (XLOOKUP)
• Find a value with VLOOKUP
• Find a value with INDEX and MATCH
• Look up using two conditions
• Show only the rows that match
• Find which bracket or tier a number falls in
• Find the last entry in a column
• Check if something is in a list
• Compare two lists to find what is missing
• Sort a list with a formula
• List the top few rows
• Find the last match instead of the first
• Look up by both row and column
• Look up using part of a word
• List every match in one cell
• Build a cell reference from text
• Pick from a short list by number
• Find the most common word in a list
• Stack two lists into one
• Number rows automatically

Add and count

• Add up numbers that meet one condition
• Add up numbers that meet two or more conditions
• Count how many times something appears
• Count rows that meet two conditions
• Average numbers that meet a condition
• Keep a running total
• Show each number as a percent of the total
• Rank numbers from highest to lowest
• Add only the rows you can see after filtering
• Find the highest number in one category
• Mark which rows are duplicates
• Count how many different items are in a list
• Add up numbers between two dates
• Add up numbers for one month
• Count cells that contain a word
• Add up the top few numbers
• Count numbers within a range
• Count everything except one item
• Add up numbers while skipping errors
• Average while ignoring zeros
• Add up numbers for one year

Make decisions

• Show one result or another (IF)
• Choose between several results (IFS)
• Hide errors such as #DIV/0! or #N/A
• Check two things at once (IF with AND)
• Pass if at least one thing is true (IF with OR)
• Do something if a cell is empty
• Turn codes into words (SWITCH)
• Check if a number is within a range
• Check if a cell holds a number
• Turn Yes and No into 1 and 0
• Name a step inside a formula (LET)

Clean up text

• Combine text from several cells
• Pull out the first word or first name
• Remove extra spaces
• Fix capital letters
• List each item once (remove duplicates)
• Take the first few characters
• Take the last few characters
• Take characters from the middle
• Count the characters in a cell
• Replace or remove characters in text
• Find where a character is in text
• Turn numbers stored as text into real numbers
• Show a number with a format inside text
• Check if a cell contains a word
• Pull out the last word
• Split text into separate columns
• Keep or add leading zeros
• Count the words in a cell
• Count how many times a letter appears
• Pull the domain out of an email address
• Make initials from a name
• Show names as Last, First
• Remove line breaks from a cell
• Remove strange hidden characters
• Repeat text or make an in-cell bar
• Compare two cells, including capitals

Work with dates

• Count days between two dates
• Count full months or years between dates
• Count working days between dates
• Add months to a date
• Find the last day of a month
• Show today's date
• Pull the year, month or day from a date
• Show the day of the week
• Find a date a number of working days away
• Build a date from a year, month and day
• Find the week number of a date
• Find the quarter of a date
• Count down the days until a date
• Work out hours between two times
• Show an age in years and months
• Find the fiscal year of a date
• Find the first day of a month
• Check if a date falls on a weekend
• Show the month name
• Write a date in a chosen style
• Turn text into a real date
• Turn a time into decimal hours

Finance

• Work out a monthly loan payment
• Find what savings will grow to
• Find the value of future cash today (NPV)
• Find the rate of return on an investment (IRR)
• Find average yearly growth (CAGR)
• Work out percent change
• Work out how much you can borrow
• Work out how long a loan takes to pay off
• Find the interest rate on a loan
• Find NPV when cash flows have exact dates
• Find the return when cash flows have exact dates
• Work out profit margin
• Work out markup on cost
• Find the break-even point
• Work out straight-line depreciation
• Split a loan payment into interest and principal
• Work out total interest paid on a loan
• Work out return on investment (ROI)
• Find the true yearly rate (APY)
• Calculate a stock's beta
• Find what one amount grows to
• Work out double-declining depreciation
• Work out sum-of-years depreciation
• Find the price of a bond
• Find the yield to maturity of a bond
• Find the current yield of a bond
• Calculate WACC
• Calculate expected return with CAPM
• Value a stock from its dividends
• Value a payment that lasts forever
• Find what a future amount is worth today
• Calculate the profitability index
• Find the payback period
• Calculate modified IRR
• Calculate the current ratio
• Calculate the quick ratio
• Calculate debt to equity
• Calculate return on equity (ROE)
• Calculate return on assets (ROA)
• Calculate the P/E ratio
• Calculate earnings per share (EPS)
• Calculate dividend yield
• Calculate inventory turnover
• Calculate days sales outstanding
• Calculate working capital
• Calculate EBITDA
• Calculate free cash flow
• Calculate contribution margin ratio
• Find the units needed for a target profit
• Compare actual results to budget
• Work out simple interest
• Turn an APY into a stated rate
• Work out monthly credit card interest
• Find what is left on a loan
• See how extra payments shorten a loan
• Work out an after-tax amount
• Work out tax with brackets
• Find how long money takes to double
• Adjust a return for inflation
• Work out the return on a stock
• Calculate a log return
• Calculate a portfolio's return
• Calculate the risk of a two-stock portfolio
• Calculate the Sharpe ratio
• Turn a monthly return into a yearly one
• Turn monthly risk into yearly risk
• Estimate value at risk (VaR)
• Work out the payoff of a call option
• Forecast the next number from a trend

Statistics

• Find a percentile
• Find a quartile
• See where a number ranks as a percent
• Calculate a z-score
• Find a probability on a bell curve
• Calculate a moving average
• Count values in a bin or range
• Find how well a trend fits (R-squared)
• Calculate covariance
• Find a margin of error
• Test if two groups really differ
• Find a geometric mean
• Make a random number