Excel is a powerhouse in the business world, but the software also offers tremendous benefits for individuals in need of budgeting help. From playing with your stock options to planning for taxes, there are dozens of ways in which people can use Excel to their advantage, provided they understand the program’s tools and tricks.
Below are 11 Excel shortcuts that make budgeting easy while saving you valuable time each month.
AutoSum is a basic function in Excel that saves significant time when adding or subtracting numbers. When creating a budget, AutoSum can be incredibly helpful for adding items like monthly expenses or different income components quickly.
To sum a column of numbers, select the cell immediately below the last number in the column and click the AutoSum button found at Home > AutoSum, and Formulas > AutoSum. Do the same for rows. Once entered into a cell, the formula can be easily copied to others and will sum the information depending on the new location. For example, if an AutoSum function in B3 adds the cells in B1 and B2, when the formula is moved to cell C3, the cells C1 and C2 will be summed, and the result will appear in cell C3. Check out this ExcelCentral.com video to see the AutoSum feature in action.
2. The “$” Sign
When creating a budget, the “$” shortcut is incredibly helpful for adding items like monthly expenses or different income components. The “$” symbol is a quick fix for anchoring a cell reference. Typically, when an Excel formula is copied from one cell to another, the references change depending on the formula cell location. The dollar sign allows you to fix the row, the column or both on any cell reference by preceding the column or row with the dollar sign. Hence, the row or column won’t change when copied.
When a formula contains an absolute reference, no matter which cell the formula occupies, the cell reference will not change. If you copy or move the formula, it refers to the same cell as it did in its original location. For example, if the cell A1 is in a formula, that cell might change to A2 if the formula is copied to an adjacent cell. If “$A$1″ is used instead, wherever the formula is copied or moved, it always refers to cell A1. See the “$” tool in action in the video below:
For budgets, the “$” saves the user from typing a variable — such as an hourly wage rate or interest rate — into every cell to which it applies. For example, you might want to list the hours of various workers and then multiply them by a set hourly rate to find the employees’ total wages. Instead of typing in the wage rate for each worker in each function, you can use the dollar sign. Excel will then automatically use the wage rate from the cell indicated.
3. Insert Multiple Rows or Columns
Whether you’re preparing budgets — or simply entering large amounts of data into a spreadsheet — inserting rows or columns can be time-consuming if the user simply right clicks and then selects “insert row.” Fortunately, Excel offers a shortcut that allows the user to insert as many rows as desired quickly.
First, right-click on the row before which you want to add new rows. Then click insert and use the mouse to drag over the number of rows you want to insert. You should see a pop-up appear showing “R” and the number of rows you are inserting, for example, “R4.” Right-click on a row and click “insert” in the pop-up menu. Blank rows should appear above the row that you originally selected. Watch this video to see the steps utilized to insert multiple rows.
4. 3D Sum
The 3D Sum function allows you to refer to the same cell or a range of cells across multiple sheets. In this way, a 3D reference is a convenient shortcut for referencing several worksheets that follow the same pattern or cells on each worksheet containing the same type of data. For example, you might want to record the same expense items by month on different Excel worksheets, using one sheet for January, one for February, and so on. 3D reference would allow you to sum all expenses for one item, for every month, in a single cell.
3D sum is a useful tool for budget preparation and reporting with multiple spreadsheets. To use this function, simply type in the name of each sheet from which you want to pull the data, such as “January,” “February” and “March.” Learn how to add across Excel sheets with 3D sum reference in this MS Office video:
The word “concatenate” might sound intimidating, however when used in the context of Excel, it simply means to combine two or more values. In Excel, the concatenate function can bring various data into one cell without the user having to type out all the information. For example, you might have a list of expense items in one column and a list of due dates in another. Using the concatenate function, you can combine information from multiple cells into one cell and present the item and due date together. This Excel tutorial shows how the function can then be copied to other cells, saving you time in the long run.
6. Goal Seek
Ideal for budgeting projections, Goal Seek allows the user to experiment with “what if” scenarios. For example, you might want to calculate the minimum mortgage payment needed in order to pay off a loan at a given interest rate in a given period. In this way, Goal Seek can help you budget appropriately for repayment. You can view step-by-step instructions for using Goal Seek in Excel on the Office Support page.
Additionally, Goal Seek is valuable for small businesses in that it aids owners in determining revenue targets. Check out the below video by Aldo Mencaraglia for an example of three products with varying contributions. You can use Goal Seek to determine the amount of product A that you would have to sell given the revenue values for B and C.
7. Pivot Tables
A pivot table summarizes and analyzes data from multiple rows or columns. Additionally, you can use pivot tables to create a new, single table that summarizes only the information desired. This video from GCF LearnFree demonstrates how easy it is to create a summary of your data with the pivot table shortcut.
To start creating a pivot table, select the column headers that are relevant for your new table. Click Insert > Pivot Table. You will then have to identify your source data and where you want the new table to appear, for example in a new worksheet. Excel will automatically create a pivot table and ask you to select the fields that you want to appear in your table. Depending on your instructions, the pivot table will calculate and summarize the data in the fields that you select.
For budgeting purposes, lengthy worksheets that detail many expenses or factors can be summarized into smaller, less cluttered tables. For example, you might record different expenses for each month or year in a spreadsheet using a new sheet for each month. Or you might want to show only recruitment-related expenses during a presentation. By specifying which cells from which to pull data, you can create one table that shows the totals for each required expense over the course of a year and extract just that table for a presentation.
The filter command in Excel does just that; it filters out the information that you don’t want and provides just the information that you do based on the specified criteria. This function is useful for budgeting in that it helps to weed out distracting information while enabling you to focus on the most important elements.
For example, you might have a spreadsheet column that displays expenses by month. Using filters and column headers as the criteria, you can show only the expenses for January. Check out this video for an example of how to filter data in Excel:
The transpose feature is useful if you want to switch data from a row to a column or vice versa. For example, a user might have listed monthly expense data under the heading January and itemized the products in the rows below the heading. Using the transpose feature, the products could be listed as column headings, and the months itemized below each heading.
Learn more about using the “transpose” shortcut in this ExcelEasy.com tutorial. You can also check out this video for information on the transpose and index functions. Similar to transpose, index allows for greater flexibility in cases when the number of possible outcomes is uncertain.
The COUNTIF function allows you to count the number of cells in a range that meet your specified criteria. The syntax that should be entered for the function is “=COUNTIF (range, criteria).” In this example from Best Excel Tutorial, the function is used to count the number of text cells that contain the word “report.”
For budgeting purposes, you might want to keep a record of payments for services. The COUNTIF function allows you to enter criteria so that you can identify cells that meet the desired criteria. For example, you might want to find the number of payments made to “Joe” for “office cleaning.” It would take a long time to search through a lengthy spreadsheet and identify all the expenses that come under this category. The COUNTIF function will automatically search the data for any entries that contain the criteria and give you the total number.
This Excel tool is useful for cases in which you have two spreadsheets with information, and you want to integrate data from both sources. The VLOOKUP function will search for a specific value and then identify other information that is associated with that value. Conjectures Incorporated provides useful videos for the application of VLOOKUP.
For example, you might keep a list of your earnings for one year on one spreadsheet and earnings for the previous year on a separate sheet. If you want to find out how much you earned each year in January, the VLOOKUP function can find that information for you and enter that data wherever you specify.
By mastering a few tips and tricks, you can reduce the amount of time spent on monthly budgeting in Excel while keeping your accounts in the black.