Nonprofit Accounting Basics

Everyday Excel Tips and Techniques for Non-Profit Organizations: Tips 1-4

Topics: 

We all understand that Excel is widely used in both the nonprofit and for-profit business communities.  The importance of this software program cannot be overstated.  Once you become proficient in Excel, you will be able to use the program for projects such as:

  • budgeting

  • time tracking

  • allocating time worked to projects

  • financial reporting

  • managing lists of data

  • analyzing the data to identify important trends

This article, and articles that follow, will help you master “must know” features in Excel and other Microsoft Office software, helping you save time each day and increase your productivity.

Tips 1-4 presented below include screen shots and steps to follow.  You can create the examples on your computer and try the steps as presented.

The 4 tips covered are:

  1. Using Shortcut Menus (Excel 2003 or 2007)

  2. The Ribbon and The Quick Access Toolbar (Excel 2007)

  3. Changing Default Settings (Excel 2007)

  4. Protecting Formulas (Excel 2003 or 2007)

1. Excel Shortcut Menus

Tip 1

To insert a row in Excel, instead of choosing Insert/Row from the menu in Excel 2003 (or clicking the Home tab on the Ribbon in Excel 2007) and then clicking the Insert tool and the Insert Sheet Rows tool, try right-clicking on the worksheet row number.  The whole row is selected. Click Insert from the shortcut menu.  Try right-clicking on column letters also.  In Excel 2007, right-clicking in the worksheet also shows the mini toolbar that is full of format tools. 

2. The Ribbon and the Quick Access Toolbar (Excel 2007)

Tip 2

In Excel 2007, you might be spending too much time on the Ribbon using the same tools each day while working on your spreadsheets.  If there is a tool on the Ribbon used frequently (AutoSum is an example), right-click the tool and select Add to the Quick Access Toolbar. 

A copy of the tool will be placed on the right end of the Quick Access Toolbar. 

Excel Quick Access Toolbar

 

 

 

3. Change Default Settings (Excel 2007)

Tip 3

By default, Excel 2007 is using the Calibri font style set at 11 point size for all new worksheets. Most Excel users I teach would rather set the default to Arial, 12 point.  

To make this change, click the Office Button in the upper left corner of the Excel Ribbon, then click the Excel Options button at the bottom. 

Then, from the Use this Font drop-down list, select Arial.  From the Font Size drop-down list, select the point size.  Click the OK button.  Your changes will appear in all new files in Excel 2007.  Take a look around Excel Options for other default settings you might want to change. 

4. Protecting Formulas (Excel 2003 and 2007)

There might be times when you would like to make sure that none of your formulas are altered accidentally in the worksheet.  

In the next steps, we will limit the protection to the formula cells.  Before protecting the worksheet, do the following two steps to the worksheet cells.  Step 1 unlocks the format on all cells.  Step 2 locks the format on the formula cells.

Tip 4

Step 1: Select all cells by clicking the select all button in the upper left corner of the worksheet (or CTRL+a) and from the Format Cells dialog box, click the Protection tab and “uncheck” Locked.  The Format Cells dialog box is located in either version of Excel by using the keyboard and pressing CTRL+SHIFT+f.

Step 2: Select all the formula cells on the worksheet.  To do this is press the F5 function key to open the Go To dialog box.  Click the Special button to show the Go To Special dialog box.  Choose the Formulas option and make sure the four check boxes are all checked.  Click OK.  Excel has selected all formula cells. While all formula cells are selected, choose Format, Cells with CTRL+SHIFT+f, click the Protection tab, and put a check mark next to Locked. Click OK.  

To turn the worksheet protection on, select the Protect Sheet command from the Tools menu (Excel 2003) or the Protect Sheet tool on the Review tab on the Ribbon (Excel 2007).  Set an optional password and click OK.  

The result is that only the formula cells cannot be changed.  Should you need to once again allow changes to the whole worksheet, unprotect the sheet.

To unprotect the worksheet, select the Unprotect Sheet command. (The Protect command toggles to Unprotect when the sheet is protected.)  Select the Unprotect Sheet command from the Tools menu (Excel 2003) or the Unprotect Sheet tool on the Review tab on the Ribbon (Excel 2007).  Enter the password if prompted. Remember that passwords are case sensitive. 

Conclusion: With practice, you can continue to learn many new productivity features to save time and protect your work in Excel.