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:

You are free to modify cells highlighted red.

The following are the sections

Section Color Code indicating difficulty.

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

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)

Click on the heading of each respective Column or Row and right click

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

Move to a designated direction from the currently selected cell

Moves to end of the data selection (non-blank) or to the end the blanked cells, based on the currently selected cell

Singlular movement highlighting cells, based on the currently selected cell

Moves to the end of data selection, based on the currently selected cell

Highlights the entire range of data or worksheet, based on the currently selected cell

Multi-cell selection

Highlights the current active area, otherwise, the enter range of data based on the currently selected cell

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

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

Automatic updating simultaneously to a change from dragging / using the fill handle

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+

Click on the text on the cell directly, not via the formula bar and modify accordingly

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

Use two double quotes (") to output an empty string or a formulaic blank

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.