Thanks to LyteBite, people like midlakewinter are now able to have two spreadsheets open on separate monitors. Mac Office 2008 opens everything in a new window, but if you’re 99% of the modern office, the Windows version of Office is on your computer and this tip should be really helpful.
Is Microsoft embittered by the exodus of business people to the Apple platform? Microsoft’s Mac Office takes 2 or 3 times as long to start and I regularly get this error when working with spreadsheets on my MacBook Pro with 4 GB of ram: 
The spreadsheet which triggered the error was only 10 columns and 20 rows. That’s a total of 200 cells and is far below the typical usage of spreadsheets in my office.Unfortunately this means I end up running Windows XP under Parallels so I can run Microsoft Office 2003 (the only currently usable version of office). All my macros, plugins, and documents work without memory error and run at 2x the speed. If you are evaluating whether or not to use Mac Office or use Parallels with Excel 2003, I strongly suggest the latter.
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.
If you’ve had to summarize data in Excel, there’s no question you’ve used the “month()” function and the “year()” function, but many of us don’t realize there’s also a “week()” function. Given you have dates within your table of data…
How do you append the week number?
One solution is to calculate the week as every 7 days from the start of the year. This is termed “absolute week number” but doesn’t give you the span from Sunday to Saturday, as many businesses like to measure performance. It starts on the first day of the year, so if this year started on a Tuesday - your weeks are measured starting on Tuesday. Here’s the function to do that:
=TRUNC(((A2-DATE(YEAR(A2),1,0))+6)/7)
And this is what it returns, the number of the 7th day occurrence during that year:
The other solution to this problem is to use a “Add-In” called “Analysis ToolPak”. Under the “Tools” menu, select “Add-Ins…”:
Under the resulting pop-up dialog, check the “Analysis ToolPak” and press “OK”.
Now you can use the function for “WEEKNUM()” that takes two parameters, one is a date (or a reference to a cell with the date) and one is an offset for what day the week should start upon - 1-7 for Sunday to Saturday respectively. Here’s what the function looks like in our spreadsheet:
=WEEKNUM(A2,1)
That function returns the calendar week number of this year with Sunday as the starting day of the week. Here’s a copy of the spreadsheet from before, only with a new column showing the result of this function. You can see on the first row that the numbers are actually different:
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
