BUDGETING TIP OF THE MONTH
Tricks to Improve Your Excel-Based Budgeting
Excel is now over thirty years old and remains the leading budgeting and planning tool for nonprofit organizations, but despite it’s maturity, it can be manual or error prone at times. Here are a few tips and tricks that can help improve the efficiency of your files and give you more confidence in the data.
When referencing a specific cell across multiple formulas that may hold an overarching assumption or value, implement the use of a Named Range. Simply select the cell or cells that you would like to assign the named range to and type the desired title for that range in the name box (no spaces). Named ranges can then be referenced in formulas and ensure that if rows or columns are inserted, moved or deleted, all formulas that reference the desired cells will continue to function as intended.
Another area to improve data integrity and efficiency is the use of Data Validation. Data validation can be found on the Data tab and provides numerous conditions that can be assigned to a cell or cells upon entry. Custom error messages or warnings can be entered to give the inputting user a clearer understanding of what is expected. A popular condition is the use of a list of values that provide a drop down for efficient and accurate selection. Consider maintaining a list of values on a separate hidden control tab!
When managing a table of data, instead of sorting/manually selecting ranges of data or relying on pivot tables, consider using SUMIF() statements. In the example below, the location and service type values in B15 and B15 are used in the SUMIF() formula to select that intersection of data for quick subtotaling.
Now combine all three tips by using a Named Range in the formula instead of referencing the specific cell and apply a Data Validation list that enables easy selection of what location and service type to total in your SUMIF() formula. These practices can help improve efficiency and control of your data and transform large error spreadsheets into useful management tools for efficient redimension.
