<< Home

The Wonderful World of Excel – Part 1

The Wonderful World of Excel - Part 1

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.

“Key”board shortcuts

  • 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 Dollar Sign

The Building Blocks aka The Basics

IF

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”)

AND, OR, NOT, ISERROR

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

The Ampersand

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(“STRING”,X) =RIGHT(“STRING”,X) =LEN(“STRING”)
  • 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:

Basic Driver Scorecard

Iding Percentage

Column K, labelled Idling % of Driving Time, uses a simple IF statement:=IF(E16>0,L16/E16,0)This formula checks if the device had trips above 5 minutes and if so it divides the idling duration by the trip duration

Iding Percentage

Column B, labelled Short Name, uses a simple LEN function to extract a portion of the full item name on Column A.

=LEFT(A16,10)

Next Edition

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:

Mr. Excel

OzGrid

Leave a Reply

Your email is never published nor shared. Required fields are marked *

Time limit is exhausted. Please reload CAPTCHA.

Subscribe Now
Get more Geotab news. You can unsubscribe at any time.