|Intro||Created by Steven Au|
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.
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)
Each section is structured to contain the following highlights:
|Application / Example|
|Exercises / Practice|
You are free to modify cells highlighted red.
The following are the sections
|Logic||First Principles Bool|
|Conditionals||IF - Flow Control|
|Text Manipulation||Pattern Analysis|
|Other Excel Features||"Good to know"|
|Further Sources||Expansion Pack|
Section Color Code indicating difficulty.
|Orange||Useful + Intermediate|
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.
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
|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|
|Data||values that have meaning to us for some purpose|
|String||Hello||A piece of text; Also any other data that is enclosed by two quotation marks "" in a function/formula|
|Value||2.199||Any floating point number|
|Helper Column (or row)||Any column (or row) used in assisting evaluation of a formula|
|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|
|Cell, variable||A single data represented by a row and column address (eg: A1, B4)|
|Array, List, Vector||a1:a26, a1:z1||A single row or column data, please note the colon symbol used|
|Range, Table, 2D List, Dataframe||a1:z26||A collection of one or more row and/or column data, please note the colon symbol used|
|Row||=row()||A range of "cells" from left to right|
|Column||=column()||A range of "cells" from top to bottom|
|Ribbon||Top tabs on Excel (File, Home, etc.)|
|Delimiter||Any character that is used as a data separator -> TSV = Tab Separated Values. Tabs are used to delineate the data.|
|Active Cell||The currently selected cell (Usually has its own special "highlight")|
|CSE||Control Shift Enter - usually for special formulas that require a calculation array|
|Question to ponder:|
Can a number be a string value? If so, can you make a number a string value?
The answer to the above is hidden below (Unhide it)
|Basics||The fundamentals | Procedural|
|Hiding and Unhiding Sections|
Click on the heading of each respective Column or Row and right click
|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?
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
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 .
Thus, a good formula, similar to programming, is simple and easy to follow its syntax and logic. Documentation (Notes/Annotations) is also helpful.
* 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.
Standard Cell reference construct
|Common Navigation||Arrow Keys|
Move to a designated direction from the currently selected cell
|(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
|The "+" is keyboard key combination||Shift + Arrow Keys|
Singlular movement highlighting cells, based on the currently selected cell
|Ctrl + Shift + Arrow Keys|
Moves to the end of data selection, based on the currently selected cell
|Ctrl + A|
Highlights the entire range of data or worksheet, based on the currently selected cell
|Ctrl + Mouse Select|
|Ctrl + * (Ctrl + shift + 8)|
Highlights the current active area, otherwise, the enter range of data based on the currently selected cell
|Delete, clearing||Backspace, 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
|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
|Dynamic-Relative Reference||A1 => B1 or A1 => A2|
Automatic updating simultaneously to a change from dragging / using the fill handle
|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
$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+
|Coloring, formatting, & merging||Self Explanatory||Ribbon: Home|
|Formatting parts of a string||The Blue Bold boat|
Click on the text on the cell directly, not via the formula bar and modify accordingly
|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
|Empty Space, double "double quotes"||""|
Use two double quotes (") to output an empty string or a formulaic blank
|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
If Shift isn't held, Excel wil copy over the highlighted range's data. Unfavorable if you have existing data in the ajacent range.
In math, this process of simply moving a range of data from one area to antoher in the same plane is called a translation.