We’ve all done it – we use complex software for simple calculations and making lists, but we don’t realize there’s more to these programs than meets the eye! In reality, the uses of spreadsheets are endless and when combined with their tools, they can be effective for managing projects, maintaining budgets, presenting information, creating timelines, and generating charts and graphs. In project management and budgeting, spreadsheets are especially helpful for processing and updating information in order to yield useful data in an organized way.
At The Lionesque Group, we use spreadsheets for almost everything we plan. For project management and production, we use spreadsheets to ensure tasks are met on time and it helps us visualize where we are in the life of each project. In accounting, for example, we use it to track our expenses and budgets. In marketing we use them in order to track the effectiveness of campaigns. The most common data processing software are Excel (which is now also available online) and Google Sheets.
Apart from having multiple uses, the spreadsheet’s primary function is performing math calculations, really, really, complicated math calculations that would take us prolonged periods of time to compute if we had a lot of data. Each spreadsheet program also offers thousands of tools and applications to assist with the operations. Here are some of the tools and applications we work with most frequently in our spreadsheets:
Formulas & Functions: Formulas calculate the value of a cell. They always start with “=” and are followed by the command you tell the software to perform. Formulas that are predetermined in the data processing software are also known as functions. Functions also always start with “=” but they are followed by a keyword that triggers the software to perform a command or a calculation. There are are over 450 functions in Excel and over 300 functions in Google Sheets. The following functions make doing calculations more efficient:
- To find the sum: =SUM(number1,number2,…)
This function allows for computing the sum of up to 255 values, cell references, and/or cell ranges. Instead of manually clicking and typing each number or cell, this function is helpful for summing up many values. We use the sum function frequently in our budgets.
- To find the average: =AVERAGE(number1, number2, …)
This function allows for calculating the average or the arithmetic mean of up to 255 names, values, cell references, and/or cell ranges. The average function is helpful for obtaining the average cost of goods and materials for our popups.
- To test an argument: =IF(logicaltest, valueiftrue, valueiffalse)
This function allows for returning a specific value for a condition if it’s true and another value for a condition if it’s false. The If function is helps us prove information quickly and is in a better visual representation. It also saves us time from having to check each condition individually!
- To count: =COUNT & =COUNTA
These functions allow for counting how many times a number or special character occur in a range of cells.
It counts the number of cells in a range that contain only numbers
This function counts how many values are in a cell. Unlike =COUNT, =COUNTA takes into consideration all kinds of values such as text, characters, and dates.
As a rule of thumb, when inserting formulas and functions in your spreadsheet, always remember the order of operations that must be followed: PEMDAS (parentheses are calculated first, exponents next, then multiplication and division, and lastly addition and subtraction).
Keyboard Shortcuts: Keyboard shortcuts replace the use of your mouse. You will be able to make the same edits as you would using the menu. The last thing you want when you are focused is to lose your train of thought by having to look through a menu to make a command. The shortcuts we find helpful when working in our spreadsheets are:
- Shift + F11 = Creates a blank new worksheet within the workbook you are working on
- F2 = Opens the cell you are working on for editing in the formula bar, this eliminates the need to double click on the cell
- F4 = Repeats the last formatting command – Click on a cell where you want the formatting copied to
- Ctrl + Shift + % = Formats number in the cell into percent
- Ctrl + Shift + $ = Formats number in the cell into currency
- Ctrl + Home = Navigates to the top of the worksheet to the home cell A1
- Ctrl + End = Navigates all the way down to the last cell that contains data
- Ctrl + Arrows on the keyboard = Navigate in the first or last cell that contains data in a specific direction
- Ctrl + C = Copy a cell
- Ctrl + X = Cut a cell
- Ctrl + V = Paste a cell
Try applying the functions and keyboard shortcuts in your next spreadsheet. Get to know the tools and let your creativity flow, just like Tatsuo Horiuchi, who uses spreadsheets to create beautiful art!
When working with numbers, these tools can be valuable for managing your projects, will save you time, and provide you with all the tools you need for improving your work efficiency.