Shortcuts Flashcards
All shortcut commands
ALT
Move to cell on the right after writing text
Tab
Mgtgrouove to cell below after writing text
Enter
Edit the contents of the selected cell
F2
Move cursor one word at a time
CTRL + ARROW
Select one character at a time
SHIFT + ARROW
Select one word at a time
CTRL + SHIFT + ARROW
Move to worksheet on the right
CTRL + Pg Dn
Move to worksheet on the left
CTRL + Pg Up
Create new worksheet
SHIFT + F11
Delete worksheet
ALT + E, L
Change name of worksheet
ALT + O, H, R
Move to next cell
→
Move to edge of data region
CTRL + →
Return to first cell in the selected row / move cursor to the first character in the cell
HOME
Return to cell A1
CTRL + HOME
Move down one screen
Pg Dn
Move up one screen
Pg Up
Move across one screen to the right
ALT + Pg Dn
Move across one screen to the left
ALT + Pg Up
Show Go-To Dialog box
F5
type in sheet name + cell number e.g. (sheet 3!D300)
Select cells in the direction of arrow
SHIFT + →
Select all cells within data region
CTRL + SHIFT + →
Select all cells in data region
CTRL + A
Select entire row
SHIFT + Spacebar
Select entire column
CTRL + Spacebar
To select additional cells - edit multiple data regions
Shift + F8
Insert a row above selected row
ALT + I, R
Insert a column to the right of the selected column
ALT+ I, C
Repeats previous command
F4
Delete selected row or column
CTRL + -
Autofit column width on selected columns
ALT + O, C, A
Manually choose column width
ALT + O, C, W
Autofit row height on selected rows
ALT + O, R, A
Manually choose row height
Right-Mouse button key+ R
Split command (freeze panes)
Use Alt + Navigation to split the viewing pane into two sections each with a scroller so as to compare things easily (doesn’t work with frozen panes)
Select cell where want split
Freeze / unfreeze panes to the left and above selected cell
ALT + W, F, F
Open the zoom dialogue box
ALT + V, Z
If you want to view whole dataset - fit selection command
Select whole dataset - then alt vz + f = OK.
Open Format cells dialog box
CTRL + 1
Pastes the format of our copied cell
ALT + E, S, T
Access fill colour dropdown
ALT + H, H
Show/Hide Gridlines
ALT + W,V, G
Wrap text
ALT + H, W
Merge & center
ALT + H, M, C
Unmerge cells
ALT + H, M, U
Group selected columns/rows
ALT + A, G, G
Hide columns / rows
ALT + A, H
Show columns / rows
ALT + A, J
Ungroup selected rows / columns
ALT + A, U, U
Paste formulas
ALT + E , S , F
Paste values
ALT + E , S , V
Show sort dialog box
ALT + A, S, S
See a formula/jump back inside it
F2
Counts the number of cells (with numbers) in the selected range - doesn’t count text
=Count(range)
Count number of cells with text
=CountA(range)
Conditional Arithmetic
=COUNTIF/SUMIF
You can count anything, including text.
Count number of cells if comply with certain criteria
=CountIF(range,criteria [e.g. “>3000”])
=CountIFS(range1,criteria1,range2,criteria2)
Sum number of cells that comply with certain criteria
=SumIF(range, criteria [e.g. “>3000”], range)
First range might be orders in march…. final range is the revenue
=SUMIFS(range1,criteria1,range2,criteria2)
- easy to build - just need to understand range and criteria.
Calculates average value within a range
=AVERAGE(range)
Calculates average value for cells that fit a single/muliple criterion
=AVERAGEIF
=AVERAGEIFS
=AVERAGEA: Calculates the average for TRUE/FALSE values
Calculates maximum or minimum value within a range
=MAX(range)
=MIN(range)
Logical testing IF
Performs a logical test, returning a value if test is TRUE and another if test is FALSE
=IF(xxxx=>
Logical test to check if more than one criteria is met
Returns TRUE if ALL logical tests return TRUE
=AND(xx11=>yy22) etc.
Logical test of multiple criteria and return of a true value or false value
=IF(AND(xx11=<>yy11,xx22=>
Returns TRUE if AT LEAST ONE logical test returns TRUE
OR
Logical operators
not equal to: <>
greater than or equal to: >=
less than or equal to: <=
Put in “ “ and add &+cell
Show formulas
Replaces cells with formulas
ALT + M, H
Cycle through anchoring cells when inside cell formula
F4
Naming cells
CTRL+F3;
Make sure to capitalise the name and use underscore = creates a constant that we can reference in cells.
Means you don’t have to anchor cells + great time saver.
(if you want to delete name - just F3 + Alt D)
Array function
CTRL + SHIFT + ENTER
- Allow you to perform calculations on arrays, not just individual cells
- Can output to a single cell or to multiple cells (must be pre-selected)
- Shown by curly braces around a cell formula
- Common uses of array functions are MaxIF and MinIF formulas
Convert row to column and vice versa
=TRANSPOSE(range) - need to include array function { } in order to execute.
If you want a whole range of column numbers to convert into rows, need to pre-select destination cells before pressing control shift enter (ARRAY COMMAND).
Formula Auditing and Traces
Alt + M, P: Trace precedents
Alt + M, D: Trace dependents
Alt + M, A, A: Remove all traces
Fill commands
CTRL + R - fills right
CTRL + D - fills down
Information functions
- Enable you to check the status of a cell
- Accept a cell as an input and return TRUE/FALSE as an output
- Often located inside IF functions as logical tests (e.g. “recheck order quantity” or “OK”
- Typically used to check the quality and integrity of an imported dataset
- Some useful functions include:
- – ISBLANK( )
- – ISERROR( )
- – ISNUMBER( )
Add filters to a dataset
CTRL + SHIFT + L
CTRL + A; CTRL + A + T
Rounding Numbers
Rounding errors often occur in Excel and irritate a lot of managers
- To solve this problem, you can use the ROUND( ) function
- ROUND( ) enables you to specify the number of decimal places for a given value
- ROUNDUP( ) and ROUNDDOWN( ) are also available as functions in Excel
Remove filters from a dataset
ALT + A, C
Generating random numbers
RAND (generates random number between 0-1) - can specify what you want to happen thereafter by having as follow on from IF formula
RANDBETWEEN (allows you to set an upper and lower bound. E.g. 1,3 would give you either 1-3; you then have to specify for the naming = e.g:
=IF(RANDBETWEEN(1,3)=1, “Internal”,IF(RANDBETWEEM(1,3)=2,”Survey”,”None”)
Sentence Cases
=Lower()
=Upper()
=Proper() - capitalizes first letter
Combining two cells of text
=First cell + & + second cell
E.g. =A2&B2
You can also combine formatting in formula:
E.g. A2&“_“&Lower(B2)
- You can combine almost any string of works you want!
Chopping characters from text
=LEFT()
=RIGHT()
=MID()
- Select cells + enter number of characters you want to chop.
- For mid; number of characters from start + number of characters to chop…
Identify position of specific character
=FIND()
- input character, plus relevant cell + also number of characters from where to start counting!
- gives you number from start where this character is found!
Used with MID function, you can chop text within a string to extract what you want…
- Will give you number if text exists.
- Can convert with =ISNUMBER(FIND(“xx”,N2))
- If you want other result than TRUE/FALSE; ADD =IF( - at beginning - and after: )),N2,””)
= Gives you the value in that cell, or if false, an empty string.
Split the content in a cell
Split text to columns, wizard
= ALT A E
TRIM() - allows you to remove white space before a text
Create date value using day/month/year
Datevalue()
Manually widen a column
ALT + O, C, W
Converting dates into days of week
=TEXT(xx, “dddd”)
mm: Displays the month as a number with a leading zero when appropriate
mmm: Displays the month as an abbreviation (Jan to Dec)
mmmm: Displays the month as a full name (January to December)
mmmmm: Displays the month as a single letter (J to D)
d: Displays the day as a number without a leading zero
dd: Displays the day as a number with a leading zero when appropriate
ddd: Displays the day as an abbreviation (Sun to Sat)
dddd: Displays the day as a full name (Sunday to Saturday)
yy: Displays the year as a two-digit number
yyyy: Displays the year as a four-digit number
Combining two data points to allow for sorting by this way - e.g. date and time as one
= format all cells to general cells (CTRL + SHIFT + #)
As time and date are stored as fractions and numbers of same unit - we can just add these together.
Then you convert back into appropriate format (CTRL + 1)
Select another cell when inside formula
F2 again…
Adding to date/time
To date - just add #
To time - add “7:00”
If manipulating sheet this way, make this a cell on the front page - which you can then update accordingly.
Covert to time value
CTRL + SHIFT + ‘
Remove all digits after a decimal point
=INT() = if on a time/date, only leaves time. Covert digits to time value to get the correct display
Naming arrays
E.g. Selection of cells
CTRL + F3
ALT + N
- Name the array
- Makes it easier to read formulas
Alternative ways of performing LOOKUPS
INDEX = Select array + Row number + Column number
(not so useful by itself - combine with match):
MATCH = Value (name); Array with value (company names), 0 (exact match) => gives you number
- use for both horizontal and vertical to give you the inputs necessary for index!
Combining into one formula: =INDEX(lookupvalue, MATCH(cell, cell array,0),MATCH(cell, cell array,0)
Advantages - no need to move columns
- Must faster lookup than v-lookup.
Top 5/ Lowest 5
=LARGE(range,position) - position = which ranking
=SMALL(-“-)
You can embed an if function to this, as an array.
E.g. {=large(IF(Datarange=selectedcell,datarange,””),position
- with position, you would select the cell with the relevant ranking - 1 through 5.
Ways to look up multiple criteria
https://www.microsoft.com/en/microsoft-365/blog/
1 Create a MATCH that looks for the value of 1 from the product of our two criteria
2 Put this MATCH as the row_num input for an INDEX with one column
3 Copy/Paste for remaining cells
Single lookups on multiple criteria
DSUM(database, field, criteria)
- – database: Select all the columns in your dataset
- – field: The column header you wish to perform sum on
- – criteria: Array of cells encompassing the sales headers and criteria
Other database functions:
- DAVERAGE
- DCOUNT
- DGET
- DMAX
- DMIN
- DPRODUCT
- DSTDEV (Standard deviation for population sample)
- DSTDEVP (Standard deviation for a population)
- DVAR (Variance for a population sample)
- DVARP (Variance for a population)
Updating forumlas with new data
For expanding datasets, you can program so that it will automatically update to take into account additional rows/columns
Dynamic ranges for updating formulas
- Most companies add new data to existing datasets over time
- Automatically updating your formulas to include new data is a huge timesaver
- We use OFFSET and COUNTA to do this
- Put the OFFSET formula in your named range to keep formulas readable
Formulas
OFFSET(reference, rows, cols, [height], [width])
- – reference: The value off which you base the offset - normally column header
- – rows: The number of rows the array is away from reference
- – columns: The number of columns the array is away from reference
- – height: Height of the array. Use COUNTA to count non-blank cells in column
- – width: Width of the array in columns
Best practice is to create names arrays with offsets before you build your formulas - in worksheets where you will be adding rows
Creating pivot table
Format data to contain no blank rows, then format as a table… Select any cell in region, and choose inset pivot table. Choose where you want it placed…
You could also just select entire dataset and chose insert pivot, but will then need to be updated manually if you add data.
- Simply drag fields into the Column Labels, Row Labels and Values areas
- Values determine what data will be included in the body of Pivot Table
Column and Row Labels
- Only fields with repeating values (e.g. State, City) should be added as Labels
- Row Labels determine the metric for each row of data
- Column Labels determine the metric for each column of data
Formatting Pivot tables
Chose style under format table.
Can also choose banded rows, format numbers to include commas to make it much more ledgible.
Summing/showing values in the pivot
Automatically assumes that you want sum of revenues, but you can change this by right clicking and choosing:
- summarise values by …
- show values as …
Click subtotals icon, show subtotals at bottom of group.
Show as % of parent helps you to get at a share of the subtotals…
Group data in pivot
Right click and select group information so as to not have to add another column to the original dataset.
You can also select two - e.g. month and quarters (pay attention to date range it uses to calculate)
Alt + AH
Alt + AJ
Add qualifiers to pivots
You can add IF statements to categorise by certain traits for example. Will autofill, but you then need to update the data in the pivot table.
Alt + F5
Sorting Data in Pivot Tables
- The selected cell is determines which field is sorted
- The most common sort options are alphabetically or by sum of revenue
- However, you can specify the column you want to sort by in More options
- Sorts are removed when you expand / collapse a group
