ABCDEFGHIJKLMNOPQRSTUVWXYZ
1
IntroCreated by Steven Au
2
Welcome!, this training was designed with interactive exercises - some spacing is dedicated for user notes but you are free to add, customize, and annotate wherever desired.
3
We are going to cover beyond basic functions like sums and dive into the nitty-gritty of Excel. However, just to be exhaustive, a Basics section is included below. (Excel 2016)
4
5
Each section is structured to contain the following highlights:
6
Information
7
Application / Example
8
Exercises / Practice
You are free to modify cells highlighted red.
9
10
The following are the sections
Quick Summary
11
IntroBasic Information
12
LogicFirst Principles Bool
13
ConditionalsIF - Flow Control
14
Look UpsIndexing
15
Text ManipulationPattern Analysis
16
IntegrationSynthesis
17
PivotsReporting
18
DebuggingError Handling
19
AutomationSimplifying
20
Other Excel Features"Good to know"
21
Further SourcesExpansion Pack
22
23
Section Color Code indicating difficulty.
24
GrayIntro Basics
25
GreenTheoretical
26
BluePractical Basics
27
OrangeUseful + Intermediate
28
29
PurpleOther
30
31
Remarks
The first three tabs are simple (Logic to Lookup) but are important building blocks for the other tabs. The fourth tab (RegEx) allows us to do string pattern matching, useful for all subsequent topics.
32
The theoretical tabs allow us to understand the building blocks per using Excel as a tool behind "How" things happen, Otherwise if skipped, the important basics tab allow us to do most Excel tasks
33
34
Terminology:TermSampleDefinition
35
Nest(-ing,-ed)=if(a1=2,2,if(a1=1,1,0))Having more than one function together, such as f(Sin(cos(pi/2))) where Cos() is nested in Sin() and all of that is nested in f(), what's evaluated first is inside following PEMDAS
36
Datavalues that have meaning to us for some purpose
37
StringHelloA piece of text; Also any other data that is enclosed by two quotation marks "" in a function/formula
38
Value2.199Any floating point number
39
Helper Column (or row)Any column (or row) used in assisting evaluation of a formula
40
Function=sum(1,2)Any single entry logic invoking an output per input arguments, Commas are for seperate arguments. Opening/closing parentheses () open/close a function respectively
41
Cell, variableA single data represented by a row and column address (eg: A1, B4)
42
Array, List, Vectora1:a26, a1:z1A single row or column data, please note the colon symbol used
43
Range, Table, 2D List, Dataframea1:z26A collection of one or more row and/or column data, please note the colon symbol used
44
Row=row()A range of "cells" from left to right
45
Column=column()A range of "cells" from top to bottom
46
RibbonTop tabs on Excel (File, Home, etc.)
47
DelimiterAny character that is used as a data separator -> TSV = Tab Separated Values. Tabs are used to delineate the data.
48
Active CellThe currently selected cell (Usually has its own special "highlight")
49
CSEControl Shift Enter - usually for special formulas that require a calculation array
50
51
Question to ponder:
52
Can a number be a string value? If so, can you make a number a string value?
53
54
55
The answer to the above is hidden below (Unhide it)
59
60
61
BasicsThe fundamentals | Procedural
62
InformationContextDetail
63
Hiding and Unhiding Sections
Click on the heading of each respective Column or Row and right click
64
65
66
Cell Referencing, Function invoking=
In computer programming, most languages will not allow you to declare a variable if you start with a number because a number already has a value. Is it interesting that when you want to reference a cell, it cannot start with a Number?
67
=A1
What we probably know: The more we store data into variables and variable referencing (Cell referencing in Excel), the simpler the output calculations may become
68
=if(true,false,true)
Caveat: The more dependency used, the more complicated debugging the workbook can become if the code or formula referencing was designed poorly or is hard to follow .
69
+
Thus, a good formula, similar to programming, is simple and easy to follow its syntax and logic. Documentation (Notes/Annotations) is also helpful.
70
-
* Most of the syntax is searchable on demand depending on which logic output you need, however the fundamentals do not change often. This lesson was designed with the most commonly used and useful features.
71
"[Workbook]Sheet!Cell"
Standard Cell reference construct
72
73
Move to a designated direction from the currently selected cell
74
(Alternative to Mouse Clicking)Ctrl + Arrow Keys
Moves to end of the data selection (non-blank) or to the end the blanked cells, based on the currently selected cell
75
The "+" is keyboard key combinationShift + Arrow Keys
Singlular movement highlighting cells, based on the currently selected cell
76
Ctrl + Shift + Arrow Keys
Moves to the end of data selection, based on the currently selected cell
77
Ctrl + A
Highlights the entire range of data or worksheet, based on the currently selected cell
78
Ctrl + Mouse Select
Multi-cell selection
79
Ctrl + * (Ctrl + shift + 8)
Highlights the current active area, otherwise, the enter range of data based on the currently selected cell
80
81
Delete, clearingBackspace, Delete key
Backspace clears the active cell data, Delete key does the same but also allows for multi-cell deleting. You can also clear contents via the right click menu
82
83
Dragging, Fill Handle
Simply drag or double click the bottom right corner of thte active cell. If there is data, then drag fill will follow a predetermined sequence when 1 or more cells selected are in the pattern. You may also create your own in the settings
84
85
Dynamic-Relative ReferenceA1 => B1 or A1 => A2
Automatic updating simultaneously to a change from dragging / using the fill handle
86
87
Absolute Reference\$A\$1 , \$A1, A\$1
Locks a reference Cell, Column, or Row - by the use of a \$ before the reference. The shortcut is the "F4" key in Windows to cycle through 4 combinations: Abolute: Both, Row, Col, None
88
\$A\$1 locks at A1, \$A1 will lock at Column A and Row 1 can change to 2+, A\$1 locks at Row 1 but Column A can change to B+
89
90
Coloring, formatting, & mergingSelf ExplanatoryRibbon: Home
91
92
93
94
95
Formatting parts of a stringThe Blue Bold boat
Click on the text on the cell directly, not via the formula bar and modify accordingly
96
97
Apostrophe, single quote'
If used before a function/formula "=, +, -" or numbers, this will force the cell to become a string text data - this is like an commenting characcter in programming
98
Empty Space, double "double quotes"""
Use two double quotes (") to output an empty string or a formulaic blank
99
100
Moving a Row or Column
Clear existing Filters, Click on the Col/Row heading (eg: G or 3 respectively), HOLD Shift key with the Cross that appears when the mouse is hovering either edge of the highlighted Col/Row, Move accordingly
101
If Shift isn't held, Excel wil copy over the highlighted range's data. Unfavorable if you have existing data in the ajacent range.
102
In math, this process of simply moving a range of data from one area to antoher in the same plane is called a translation.
103