Excel

Create a pivot table

Insert -> Pivot Table -> Highlight all data and Insert into a new Worksheet.

Drag a field to the values section. Double click -> 'Value Field Settings...' -> Average. Number Format -> Percentage.

Drag 'Year' to Columns.

Right click on 'Grand Total' and click 'Remove Grand Total'.

Double click the text and edit 'Custom Name:'

Click on Pivot table to 'PIVOTTABLE TOOLS: ANALYZE'. -> Insert Slicer.

Drag other fields into the VALUES section.

Drag 'values' from columns to rows.

To view the field list. Right click the Pivot Table and click 'Show Field List'.

To create a difference column: Click 'ANALYZE' -> 'Options' -> un-check 'Generate PivotTable'.

Right click on 'Values' -> Show Field List.

To rename a value sometimes you have to add a space at the end of the word.

If you want to expand rows to make them side by side 'Right click' under Row Labels then go to 'Field Settings...' -> Layout and Print -> Click 'Show item labels in tabular form' and 'Repeat item labels'. Remove 'Subtotal'.

To add columns to a slicer: Click on slicer -> Options -> Columns: 2.

To get rid of blanks: Right click slicer -> Slicer Settings -> Hide items with no data.

Delete a pivot table

Pick a cell anywhere in the pivot table -> Click 'Analyze', 'select' -> 'Entire pivot table' -> Press 'delete'

Put row values on the same line in pivot table

Right click the value e.g. 'CS' -> 'Field settings' -> 'Layout & Print' -> Show item labels in tabular form and 'Repeat item labels'

Reference cell from another sheet

=Sheet2!B2

Reference cell from another file

=SUM('C:\Reports\[Budget.xlsx]Annual'!C10:C25)

Find and replace a value

CTRL + H

Conditional formatting (make cell a certain color based on the data)

HOME -> Conditional Formatting

To look for a value

=IFERROR(VLOOKUP(STR_TO_FIND,abc!$A:$D,4,0),"-")

To make a cell a drop down list

Create a list of cells

Click on the cell to make a drop down

Click on 'DATA' then 'Data Validation'

Choose 'List' in 'Allow'.

Choose Source and highlight the list.

To freeze the top two rows

Click on the third row then View -> Freeze Panes

Cumulative sum

...

Week day

Drop duplicates

Data > Data Tools > Remove Duplicates.