Excel #1 Flashcards
F2?
Activates the editing mode for an active cell.
Brings back blue moving box showing range in question.
CTL + 1
Formatting options.
F7
Spell check
*Note use of CTL = SHF = Down!
Activate PIN listing
Go to file
ALT + Q
Open search bar.
CTL + F1
Minimise and maximise ribbon.
Right-click?
Mini Toolbar + 19 options
Movements about a table of data?
Tab, Enter, Shift Tab (Back). Shift Enter (Up).
Box and move about parameters?
Mouse square, CTL + Enter on first cell, Move about square.
F1?
Activates Help panel.
File?
Backstage, Admin area.
The seven manual controls?
CTL + Up/Down/Left/Right arrows, CTL+ SHIFT Down/Right, CTL + A, CTL + Enter.
Non-contagious cell selection?
SHIFT + Arrow, SHIFT + F8, SHIFT + Enter, SHIFT + F8….
Name?
Name of formula or named range incorrect / absent.
Ref?
Refer to cells that no longer exist.
Div?
Trying to divide the number by zero.
Absolute reference?
F4 while active cursor in cell, Alternating.
Auto-sum?
ALT + =
Series creation, row or column?
Right=click, One, One cell down, One cell back up, Right-click, Fill series.
Flash-Fill criteria?
Adjacent, CTL + Enter to stay in top cell, CTL + E to flash fill down.
Paste individual table components?
F3
Go to table components?
CTL + G
Format painter rules?
Click once for one use, Click twice for continual use until switched off.
Add manual break in cell (Partick Thistle)?
Place cursor, ALT + Enter
UNIQUE?
Single list of unique items from longer list of repeating items (Dynamic array).
Insert Filter?
CTL + Shift + L
Table creation?
CTL + T
Start and End of worksheet?
CTL + Home, CTL + End.
Save as?
CTL + S, F12.
Move between worksheets?
CTL + Page up. CTL + Page down.
Delete worksheets?
CTL + Left-click to select pages, Right-click and select delete to remove.
Double-click in cell?
Edits, Reveals formula.
Fractions?
Space needed between number and fraction to work. 1 1/4 = 1.25 in cell.
Automatically generate sequential?
CTL + Autofill (Drag down) every line.
Zoom?
CTL + Mouse wheel.
Add a column?
CTL + SHIFT + Plus, CTL + Minus.
Fill down (List?)
CTL + D.
Fill across (List?)
CTL + R
Insert cell comment?
SHIFT + F2.
Same increase in size drag of box?
CTL + Drag.
Round function? (Penny-rounding)?
=ROUND(E2 - (F2 + 1), 2)
Hiding values in a cell while others remain visible?
Formatting - 3 x ; ; ;
Copy Worksheet?
CTL + Drag worksheet to copy.
Move Worksheet?
Drag worksheet to move.
What do pivot tables allow?
(1) Quickly summarise large amounts of data. (2) Pivot data about. (3) Key Metrics, Trends, Issues, Successes and Failures.
Importance of cleaning data?
Data does not always import as expected. Non-clean data = Non-accurate data!
Removing blank rows (Cleaning data #1)
Home > Editing > Find and Select > Go to special > Select blanks > Delete all.
Removing duplicate rows (Cleaning data #2)
Click somewhere within the data set. Data > Data Tools > Remove duplicates. Note - Only if an exact duplicate.
Clearing formatting (Cleaning data #3)
Home > Editing > Clear. Formats, Contents, Comments, Notes and Hyperlinks.
Applying number formatting to pivot tables (Cleaning data #4)
Column by column when it comes to pivot tables. CTL + 1. Select from number tab. Use main ribbon also.
Changing the case (Cleaning data #5)
=UPPER(A1). =LOWER(A1). =PROPER(A1). Use HELPER COLUMN + Paste special to (replace).
Removing Non-Printing Characters and Spaces (Cleaning data #6)
=CLEAN(A1) = Non-printing characters. =TRIM(A1) = Spaces May have non-printing characters / blanks you can’t see.
Merging and Splitting columns (Cleaning data #7)
(1) Flash Fill. (2)
=CONCAT(John,” “,Walsh)
Convert numbers stored as text (Cleaning data #8)
Green triangles, Select all, Highlight all, Convert to number.
Finding and replacing text (Cleaning data #9)
(1) CTL + F = Find and Replace. (2) Home > Editing > Find and Select > Replace.