Author: Mauricio Muniz, Senior Support
The power of spreadsheets cannot be overstated. In today’s workplace the use of Excel has become more widespread than any other piece of software and expertise of Excel is no longer a nice to have but a requirement. This post will be part 1 of a series of Excel posts that will help the reader dive into the wonderful world of Excel.
- Ctrl + Down/Up Arrow: Moves to the top or bottom cell of the current column
- Ctrl + Left/Right Arrow: Moves to the cell furthest left or right in the current row
- Ctrl + Shift + Down/Up Arrow: Selects all the cells above or below the current cell
- Shift + F11: Creates a new blank worksheet within your workbook
- F2: opens the cell for editing in the formula bar
- Ctrl + Home: Navigates to cell A1
- Ctrl + End: Navigates to the last cell that contains data
- Alt + =: Sums the cells above the current cell
- Ctrl + Shift + $: Formats numbers within highlighted range into currency
- Ctrl + Shift + % : Formats numbers within highlighted range into percentage
- Ctrl + Shift + ; : Inserts current time
- Ctrl + ; : Inserts current date
The Building Blocks aka The Basics
One of the most fundamental formulas but probably the most widely used building block. The IF formula is a logical formula that looks at the condition provided and if it is deemed to be true will return one result, while return a different one when the condition is not met.
=IF(“condition”, “action if true”, “action if false”)
Commonly used within the condition section of an IF statement the AND, OR and NOT are used when more complex logical rules are required.
- AND: will return TRUE if all the conditions within it are met
- OR: will return TRUE if at least one of the conditions within it are met
- NOT: will return TRUE if the logical statement within is FALSE
- ISERROR: used as a failsafe when it is possible that the logical rules within could return an error message. If an error is seen then the ISERROR formula will return TRUE while it will return FALSE
LEFT, RIGHT, LEN
The three basic string manipulation formulas, if you have worked with MyGeotab reports you have used or seen them used when working with the manipulation of groups.
- LEFT: Will return the X leftmost characters from a string
- RIGHT: Will return the X rightmost characters from a string
- LEN: Will return the length of a string
Putting the Blocks to Use Within MyGeotab Reports
Let’s take a look at one of the default dashboards within MyGeotab called Basic Driver Score:
Column B, labelled Short Name, uses a simple LEN function to extract a portion of the full item name on Column A.
Next edition of this series will introduce the reader into a variety of basic math functions that will then lead into conditional math functions. Want even more? Here are other good resources to help immerse yourself in all things Excel: