When you have a lists of numbers, you don’t need to use the SUM() function to get the sum of the numbers when you just need to know it at a glance. Just highlight the cells and the sum will appear in the status bar at the bottom of the window.
Summarizing data by month is very popular - but if you need to summarize data by month and your dataset bridges two years, your data will most likely be out-of-order or worse, summarized incorrectly as the group for “July” will contain 2006 and 2007 data. Here’s a function I use to print out the year+month so I can summarize across years:
=YEAR(A1)&”-”&IF(MONTH(A1)<10,”0″,”")&MONTH(A1)
That function will produce the year + month value in the format “CCYY-MM” and thus will always sort well in pivot tables or even just filtering.
Many of us are still using spreadsheets for tracking hours in professional service organizations. It’s easy to get time-sheets and track metrics in a quick Excel spreadsheet so we do it instead of soliciting IT to build or buy a system. Once you get more than a few sheets in your workbook it’s a common practice to build a cover worksheet that contains summary data - a dashboard of sorts.
Using conditional formatting, you can turn simple metrics into a little something more. You can set automatic formatting on cells to reflect the value within. This means you can automatically set the background color to red when the value is below an acceptable range. Here’s how you do it…
First, create your table (hopefully using a function referencing the actual data):
Select “Conditional Formatting…” from the “Format” menu:
Using this dialog, you can define ranges of values and assign formatting elements. In this case I used numbers (percentages) but you can use text also. For instance you can set a color to green, use “greater than or equal to” in the middle box, and “Y” for the value on the right. This codes “Yes” values to green. The next rule could be “N” and mapped to red would give you a green/red based on answers to a questionnaire.
When you’re done, juts click the “OK” button and your formatting will be applied to the cells. You can always select the cells and return to this dialog to adjust the settings if you made a mistake or want to add another range.
This formatting makes key values really stand-out among a busy worksheet.
Working with Finance, Sales, and Marketing- you must calculate summaries based on financial and calendar quarters. There are no built-in function to handle this in Excel 2000-2003. Luckily, you can create a short formula to create the value:
Calendar Quarter:
=INT((MONTH(A2)-1)/3)+1
Financial Quarter (Sept. Start):
=MOD(CEILING(22+MONTH(A2)-9-1,3)/3,4)+1
