Category Archives: Excel 2007

Filtering weekdays in Excel 2007

Posted on by
Reply

I wanted to conditionally format all Mondays in a date range. I found no way to do this with conditional formatting and filters, but at least I found a workaround.

In a column, next to the table I have, I used this formula:

=IF(WEEKDAY(Table1[[#This Row];[Datum]])=2;”Monday”;”Not Monday”)

I copied this formula to the other cells and then I filtered that specific column for Mondays and colored the background of those cells manually. This method does obviously not work so well if you have a lot of data, but formatting a few rows where dates are not changing should at least be quicker than manually going through each date.

You could also use something like this:

=IF(WEEKDAY(A1)=2;”Monday”;”Not Monday”)

The weekdays have the following numbering:

Sunday = 1
Monday = 2
Tuesday = 3
Wednesday = 4
Thursday = 5
Friday = 6
Saturday = 7

Column charts with negative axis

Posted on by
2

A really good tutorial can be found here on how to create “clustered-stacked column charts”. It works very well in Excel 2007 also. The result looks something like this:

Clustered-stacked column chart

It took me a while to find how to accomplish this, I googled different variations of “Excel 2007 series values” minus, negative, stacked, “excel 2007 axis scale”, “same secondary scale”, “negative bar color”. I even attempted scripting it, but with no luck.

This chart is used to illustrate why a company is not necessarily profitable, despite that it looks that way first. The profits exceed the costs at a first look (above the zero), but adding costs for personnell and operating expenses, the actual result is negative, as seen above where the multi-colored bar (expense types) is longer than the blue (income).