Pen and paper are great, but spreadsheets can maximize efficiency and assist users with the interpretation of data by presenting financial information both numerically and graphically. Spreadsheets allow for the input and manipulation of data that may enhance decision-making. Graphs that help tell the story can be created in a matter of moments. Following are factors to keep in mind when using spreadsheets. The commands provided are for Microsoft Excel 2016, but most work with other versions.
Uses for a Spreadsheet
- Budgets and forecasts
- Reconciling financial information such as property taxes and grants
- Analyzing situations with variable financial elements
- Complex calculations
Naming Conventions
Defining ahead of time how naming conventions will be used will increase the organization of documents, and make locating those documents easier. When information pertains to a specific fiscal year, we recommend starting the naming convention with the fiscal year end date. For instance, the original budget for the year ended June 30, 2019, could be saved as 2019-06 Original Budget.
Access to Documents
Consider who needs access to electronic documents. If multiple people will be using the documents, ensure that the documents are saved in a location accessible by others. If sensitive information is included that should be seen by a limited group, consider password-protecting the document. Limiting access to the document can be accomplished by selecting File>Info>Protect Workbook>Encrypt with Password.
Verify Formulas
When work has been completed in the spreadsheet, take a few moments to review it; look for input and formula errors. To quickly view the formulas, navigate to the Formulas tab on the ribbon and select the Show Formulas button that is located in the section labeled Formula Auditing. When this option is selected, you can view formulas throughout the spreadsheet. If you navigate to the cell that includes the formula, the cells included in the formula will be highlighted.
Tips & Tricks
- “Alt =” is a keyboard shortcut that brings up the =SUM formula and automatically sums the numbers above the cell selected.
- Format Painter allows you to copy the format from one cell to another cell. To use it, highlight the cell that has the formatting you desire and click the Format Painter. Then click in the cell you want formatted.
- Double-clicking on the Format Painter button keeps it selected so you can format more than one cell.
- On the ribbon, select View and then New Window to open a second copy of the same document. This feature will allow you to view a second copy of the workbook if you need to compare numbers between multiple tabs.
- To modify information in a cell, using the F2 button on the keyboard will take you to the end of the cell.
- To create a chart, input information with column and row headers. Then, on the ribbon, select the Insert tab and then click on Recommended Charts. Several options that fit the data input will be presented.
Want to learn more? Click here for how Yeo & Yeo Technology can provide training solutions tailored for you.