I Love Excel!

Every try calculating a pivot table in Excel?  How long would you guess it takes for 800 rows of data?  10 seconds?  15 seconds?  How about minutes?  Two minutes?

That’s right.  I’ve got enough time to go post on a blog when I ask Excel to calculate a Pivot Table on 800 rows X 10 columns.  The same task in Excel 2003 (under Parallels) takes about 4 seconds.

Here’s what it looks like every time you need to refresh your pivot tables:

Just in case you want to save, the new file format (or whatever Excel 2008 does) makes your files about 50x as big as they used to be.  This means a pivot table with a hundred rows of source data will end up being a 20MB file.

My pivot table is almost done being recalculated, so I should get going…

Bookmark to:

Tags: Excel 2008, General // Add Comment »

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.

Bookmark to:

Tags: Excel 2007, Excel 2003, Excel XP // 1 Comment »

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.

Bookmark to:

Tags: Excel 2003, Blogroll, General // 3 Comments »

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.

Bookmark to:

Tags: OpenOffice Calc, Excel 2002, Excel 2007, Excel 2003, Excel XP, Excel 2000 // 1 Comment »

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.

Bookmark to:

Tags: OpenOffice Calc, Excel 2002, Excel 2007, Excel 2003, Excel 2000, Excel XP, General // 3 Comments »

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:

Bookmark to:

Tags: Excel 2003 // 2 Comments »

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.

Bookmark to:

Tags: Excel 2002, Excel 2007, Excel 2003, Excel 2000 // Add Comment »

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

Bookmark to: