Back
Excel

Shadows on Charts and Cells in Excel 2007

Today’s author, Helen Hosein, a Program Manager on the Excel team.

Certain shadows created in Excel 2003 look different Excel 2007. In particular, you might notice that your old cell shadows, as well as shadows on things like Chart Titles might look a bit different when you open your workbooks in Excel 2007. This is because Excel treats these as shapes with no fill – a bit like a wire frame. The shadow that it casts is the rectangle of its outline. Only when the frame is filled in, does it cast a whole shadow. Similarly, Excel 2007 just draws a shadow on the border of a shape with no fill, instead of on the whole shape. Since many of you, our customers, miss being able to create these types of shadows, we are assessing the possibility of bringing back this capability in the next version of Excel. For now, though, there are ways to work around this and create great-looking shadows on cells and chart elements in Excel 2007.

An Example – Shadows in Excel 2003 vs Shadows in Excel 2007

Two of my very good friends, Rita and Alfie, are thinking of moving to London soon. Man, I’ll miss them. They’re shopping around for “flats” (they’re already learning the lingo) but aren’t sure which neighborhoods to consider. Rita, being web savvy, decides to download some of the 2005 rent data from the Greater London Authority and make an Excel chart so she and Alfie can gauge the relative prices.

Rita copy/pastes the average rent for a studio in each borough into Excel 2003. She also adds the overall average rent for comparison. To make the overall average stand out, she adds a Cell Shadow to that row.

Excel draws a rectangle shape around the selection and adds a shadow to it, giving that row a great effect that draws attention.

Now Rita adds her chart of the rents. She likes gray, so she makes the whole chart background that color. Since the shadow looks so good on the summary, she adds a matching shadow to my Chart Title, and sets the Area fill to None so that the chart area color shows through.

Once she’s done, Rita sends me the workbook to get my opinion. After all, I’m her best friend, and I’m hoping to visit her soon in her new neighborhood. I open her workbook in Excel 2007.

Wow! Do you see what I see? Rent in the City of London is really high. The second thing I notice, however, is that the shadows Rita put on “All Boroughs” and on the Chart Title are not quite what Rita might have intended. The good news is that these can be fixed in no time.

Chart element shadows in Excel 2007

Remember, when Rita added the shadows to the Chart Title and the Legend, she also set the Area fill to None. Excel 2007 sees these chart elements as empty rectangle frames, since they have no fill. Because of that, the shadows on them look just like a second empty rectangle. The quick, easy fix is just to give them a fill.

Step 1: Right-click on the Chart Title and choose Format Chart Title.

Step 2: On the Fill tab of the Format Chart Title dialog, choose Solid fill.

Step 3: Choose the same color fill as the background (in my case, the same shade of gray that Rita’s always on about).

Now the Chart Title looks the same as it did in Excel 2003, and it still blends with the Chart Area because I gave it the same fill. I could also have done that by using the Shape Fill menu on the Format tab under Chart Tools on the Ribbon.

Cell shadows in Excel 2007

Now the shadows on the chart are fixed, but the cell shadow still doesn’t look the way it used to. Fortunately, one of our testers, Jon Adams, wrote a convenient add-in that helps solve this problem. Jon’s add-in gives a great example of how you can use VBA to create your own cell shadows. You can download Jon’s by clicking the attachment at the bottom of this post. Here’s how you install the add-in.

Step 1: Click on the Office button and choose Excel Options.

Step 2: On the left, choose Add-Ins, and under Manage: choose Excel Add-Ins and click Go…

Step 3: An Add-Ins dialog will appear. Click Browse… and choose CellShadows (the file you just downloaded).

Step 4: Now Cellshadows will appear in your list of Add-Ins with a check mark next to it. Click Ok.

Once you’ve installed the CellShadows add-in, you’ll notice a new chunk called Cell Shadows on the Insert tab of the Ribbon. The Shadow menu allows you to insert a new cell shadow on your selection, just like you could in Excel 2003, whereas the Convert Cell Shadows button converts your old cell shadows so that they look right in Excel 2007. I want to convert Rita’s cell shadow, so I hit the Convert Cell Shadows button.

Just like that, Rita’s cell shadow is fixed.

So how did that work? If you try this yourself, you’ll find that if you click on the shadow, you can see that it’s actually a group of rectangles. The convertShadows macro looks for all the shapes in the workbook with an offset shadow and no fill, and replaces each of them with a group of two gray rectangles at the matching offset where the shadow would have existed. The CellShadowFromRange macro does the same thing, but uses the selected range as a starting point rather than an existing shape. When creating a new cell shadow, the add-in will also add a border around the selection to make it look more like Excel 2003′s shadows. If you’re converting old cell shadows that lined up perfectly with the original range that was selected (that is, you didn’t move the cell shadow shapes after creating them), you can optionally add a border to the range after conversion in Excel 2007 to complete the look.

Step 1: Go to the Home tab on the ribbon.

Step 2: On the Font chunk, click on the Border menu.

Step 3: Choose Outside Borders.

Now the shadows all look just as great in Excel 2007 as they did in Excel 2003. I’m still not sure to which neighborhood my friends will be moving, but Rita says Alfie is rather keen on Haringey.