Photo: Dean Drobot (Shutterstock)
From the most thorough travel plans, to excruciatingly detailed journals, to elaborate to-do lists: My life motto is that you have a goal, your goal needs a spreadsheet. And when your goal is to manage your money better, a customized spreadsheet can be your number one budgeting tool.
Both Excel and Google Sheets have budget templates to get your spreadsheet started, but if you’re serious about staying on top of your spendings and savings, it helps to become fluent in spreadsheet shortcuts. Here are our favorite tricks to craft your budget spreadsheet like a master.
Learn all the hot keys
Budgeting doesn’t need to be so tedious when you make Excel do the work for you. Memorize these basic key combinations to make your life much easier:
Select entire column: Shift + SpaceSelect entire row: Ctrl + SpaceToggle bold: Ctrl + BToggle italics: Ctrl + IToggle underscore: Ctrl + UAutoFit: ALT+H+O+I. Select the data in the column(s) you want to adjust and press ALT+H+O+I to make the column widths fit your data.(For Mac users: Use the command button instead of control.)
Here are lesser-known, but wildly helpful shortcuts as you start as your budget gets more and more detailed.
AutoSum: ALT+=. AutoSum is crucial for any budget spreadsheet. To use this shortcut, select an empty cell adjacent to the data that needs to be added, then press ALT+= to get the sum of those cells.Flash fill: CTRL+E. This automatically fills data down a column based on detected patterns. Start to enter the pattern you want your data to appear in, then hit CTRL+E to let Excel does the rest.Applying fixed costs: CTRL + Enter. Instead of entering the number in the first cell and using autofill, you can mark all the cells where you want a fixed value, write that number, and use CTRL + Enter to input that amount into the selected cells.Toggle formula: CTRL+~. This shortcut allows you to toggle between displaying a cell’s formula and value in the active worksheet—much faster than checking each one individually in the formula bar. If you eventually need to remove the formulas and only keep the values of cells, right click and select “copy as values only.”Auto bar chart: ALT+F1. Want to visualize your budget? Of course you do! This shortcut automatically creates and inserts into the active worksheet a bar chart using data you select.Color code the dang thing
Your budget will benefit from color coding to keep all your different entries straight. Here’s how to assign colors to different groups of income and expenses, like Groceries, Rent, Bills, Entertainment, and so on:
In Excel, select Data > Data Validation and then choose “List” in the Allow section. Type in the categories you want to add to your list.Select the cell(s) you want to add to the list, e.g. the cell with your rent payment to a list item labeled “Rent.” Head to the Format tab and select Conditional Formatting > Highlight Cells Rules > More Rules.From here, make it so that whenever cell contains certain text (let’s say “Rent”) turns a certain color (let’s say red). Going forward, whenever you select “Rent” in your dropdown list, the cell will automatically be colored red, allowing you to more easily visualize your budget.Format your dollars
Do not waste your life manually adding a dollar sign to each and every cell. you can mark cells as default U.S. dollars by heading to the main menu and selecting Number > Currency.
Alternatively, once you input all your values, use Ctrl + Shift + $ to change an entire column into dollar amounts. This shortcut not only adds the dollar sign, but also reformats your decimal points and commas properly. Don’t want decimals at all? Go to Number > Decrease Decimal. (This doesn’t delete the decimals, it just hides them.)
For more spreadsheet hacks, the @exceldisctionary Instagram account is full of visually aesthetic tips, and check out our recommendation of HowToExcel for a video tutorial.
