Excel TipsNIFS Active Aging

In this presentation:• Attendance Spreadsheet

• Add a new line for a member

• Add a new column

• Carry formulas over when adding a new line/column

• Double-checking formulas that go into the MR

• New tracking tools for 2017

• Monthly Report

• Add more lines for incentive/wellness programs

• Make sure formulas on “Annual” tab are consistent with monthly tab

• How to copy/paste Annual tab from one year to the next

• Membership Database

• Add “membership renewal” columns to spreadsheet

• General

• How to use functions to find totals/averages/etc. in your own spreadsheets

• How to “freeze” lines

Attendance Spreadsheet

Add a line for a new member

• Find the spot (in alphabetical order) where you need to insert the person’s name.

• Hover your mouse to the LEFT of the person’s name BELOW where you want to insert the new person. You should see a little black arrow when you hover over the number to the left:

• You MUST highlight the ENTIRE row this way so the formulas don’t get messed up.

Add a line for a new member

For example, if we wanted to enter “Martha Jones” here we would highlight the name BELOW where hers belongs in alphabetical order

Add a line for a new member

Right-click to highlight the entire row of the person BELOW where you need to insert the new person and bring up the menu.

Add a line for a new member

Choose “Insert” and a new row will appear. It’s important to do it this way so that the formatting carries over to the new person’s row.

Side note: you have to do the same thing if you need to delete someone. You highlight the entire row by going to the number to the left of their name and click to highlight everything. Then, right-click and choose “Delete”.

Add a line for a new member

• Enter the new person’s information (name, visit data).

• Now you need to make sure the formulas carry over.

• Any boxes that are supposed to have a total, but are blank will need to have the formulas added. Do this by clicking the cell ABOVE where you need to fill in a formula. For example, if you need a formula filled in for cell G177 you would click on cell G176.

Add a line for a new member

• Place your mouse over the bottom right corner of that cell where the tiny box appears.

• Drag the tiny box down so that the cell below (where you need the formula) is highlighted as well. This will copy the formula, BUT it changes the cells so that they are appropriate for the row below. You CANNOT just copy and paste things because then it will reflect the wrong row’s numbers.

Add a column (for a class)

• Find the spot where you need to insert the new column/class.

• Hover your mouse ABOVE the column that’s to the RIGHT where you want to insert the new column. I highly recommend putting any additional columns between the “Tai Chi” (column K) and “Fit and Fab” (column L) on the template. This will help you avoid making the boxes at the bottom of your sheet look funny.You should see a little black arrow when you hover over the letter at the top of the column:

• You MUST highlight the ENTIRE column this way so the formulas don’t get messed up.

Add a column (for a class)

Add a column (for a class)

Right-click to highlight the entire column of the class to the RIGHT where you need to insert the new column and bring up the menu.

Add a column (for a class)

Choose “Insert” and a new column will appear. It’s important to do it this way so that the formatting carries over to the new column.

Add a column (for a class)

• Enter the new class title.

• Now you need to add a formula to the bottom of the column.

• Do this by clicking the cell to the LEFT where you need to fill in a formula. For example, if you need a formula filled in for cell L139 you would click on cell K139.

Add a column (for a class)• Place your mouse over the bottom right corner of that cell where the tiny

box appears.

• Drag the tiny box right so that the cell below (where you need the formula) is highlighted as well. This will copy the formula, BUT it changes the cells so that they are appropriate for the row below. You CANNOT just copy and paste things because then it will reflect the wrong column’s numbers.

Double-check formulas for MR

• To double-check the formula of any category that goes on your monthly report, you can simply double-click the box that shows the total number of visits for that category.

• This will highlight the boxes that are included in that formula.

***When you’re done looking, you MUST hit “Enter” before you do anything else. If you click another box anywhere on the screen you will mess

up the formula.

New tracking tools for 2017

All you have to do is enter the names under the correct column and the totals will automatically populate at the top.

Monthly Report

Add lines for more incentive/wellness programs

Be sure the number of participants in incentive/wellness programs is the same on both the monthly tab AND the Annual tab. You can do this one of 2 ways:

• Add lines and change the formula on the “Annual 2016” tab yourself.

• This means you’ll have to start by going to your monthly tab and see what boxes contain headcounts for incentive programs.

• Then move back over to your “Annual 2016” tab and check that the formula in the corresponding cell includes all boxes with headcounts for your incentive programs.

• So you may have to add boxes to the formula.

• Do the same for the wellness programs.

Add lines for more incentive/wellness programs• Or the simpler method is to pay attention to where you are adding extra

lines under those Incentive/Wellness headings. If you do this correctly, then Excel will automatically change the formula for you.

• To add lines under Incentive Programs:

• Hover your mouse to the left of line 39 and click to highlight the entire line

Add lines for more incentive/wellness programs

• Right-click and choose “insert”

Add lines for more incentive/wellness programs

• This should add a new line IN BETWEEN the existing ones. You’ll know you did it right because your new line won’t have the word “participant(s)” in column C.

Add lines for more incentive/wellness programs

• Continue to do this until you have as many lines as needed

• To add lines under Wellness Programs:

• Follow the same steps as above except start by hovering your mouse to the left of line 42. (Note: If you’ve already added to the Incentive Programs then this won’t be line 42 anymore. Just highlight the bottom line in the box of Wellness Programs.

Make sure formulas on “Annual” tab are consistent with monthly tab

• This is where you need to make sure that your formula matches the lines in your spreadsheet. The formula should list the “…first cell:the last cell” with those participation numbers.

Copy/paste Annual tab from one year to the next

• There are several ways to “copy” the info from your Annual tab. I’m just going to show you the fastest.

• Click above column A so you see this arrow:

• Drag your mouse along the columns until they are all highlighted and then right-click to see the menu.

Copy/paste Annual tab from one year to the next

• Choose “copy” from the menu.

• Open your new MR (for the next year)

• Click on the last tab. In our example, we’re copying “Annual 2015” from the 2015 MR to the 2016 MR.

• Highlight the entire sheet the same way you did when you were copying data on the 2015 MR.

Copy/paste Annual tab from one year to the next

• Right-click for the menu and choose “paste” to fill in all the data

• Be sure to click on a couple of the individual month tabs to make sure the data all populated correctly.

Membership Database

Add “membership renewal” columns to membership database spreadsheet

• Highlight the columns the same way you would if you were inserting a new column.

• Hover your mouse ABOVE column A and then click and drag along the letters until all columns are highlighted.

Add “membership renewal” columns to membership database spreadsheet

• Right-click and choose “Copy”

Add “membership renewal” columns to membership database spreadsheet

• Open your Membership Database

• Right-click column K – the entire column highlights and the menu comes up.

• Choose “paste” to paste the copied cells from the Membership Renewal spreadsheet

Add “membership renewal” columns to membership database spreadsheet

• It should look like this when you’re done:

General Excel Tips

General Excel Tips

• Use functions to find totals/averages/etc. in your own spreadsheets

• Freeze/unfreeze lines

2 Final Tips

Save OFTEN

• When you’re first starting out, save every single step along the way.

• That way, if you mess up (or you think you messed up) you can just close the spreadsheet and click “don’t save” and it will all go back to the last time you saved it.

Google

• No, seriously

• Google has some amazing answers to Excel formulas and other questions. How do you think I learned all this?

Questions?