Best Microsoft Excel Bloggers

Tuesday, November 24, 2009

Converting Excel 2003 Commands into Excel 2007

I have been searching for how to do something with an Excel pivot table that was one step in Excel 2003 and now is hidden in a sub menu on a contextual toolbar! .. but while searching I came across a nice site that has a table showing the Excel 2003 command and its match in Excel 2007.
Take a look if you get frustrated trying to find something.

The link is .html but you can also download a worksheet version too.
My daughter is off tomorrow so I probably will be too so...........
Have a great Thanksgiving... don't eat too much and don't overdo the shopping on Friday:)

Friday, November 20, 2009

Moving a Pie Slice

Hey- the holidays are starting to close in.... perhaps that explains why all my examples lately have been wine and food.
Anyway, below are the steps if you want to explode a piece of your pie chart.
Please notice how nicely the pie slices match their respective pie colors :)

In the example, above, I moved the red Apple slice.

  • Click on the Apple slice to select it
  • Be certain that you have selected the slice and not the data label in the slice!
  • Click your left mouse button
  • Slowly drag your hand to the right and you should see an outline of the slice follow it.
  • After you have moved it a little, let up on your mouse and you should see that the pie slice has moved.
If you want to explode all the pieces... the fastest way is to change the Chart Type and select the exploded pie illustration.

Well I am off to Chicago for the weekend. I am going to see Young Frankenstein- the Musical. .. yes.. that is right.hard to imagine isn't it?
I'll let you know on Monday how it was. Have a great weekend

Thursday, November 19, 2009

X Axis and Numbers

Common X Axis Problem

If your first column contains numbers, such as years, Excel will not recognize that you want this series to be on the X axis. Excel will just treat it as another series. In the example below, zip code has been treated as a series rather than displaying on the x axis as the user wanted.

  • On the Design tab, click on Select Data
  • Select Zipcode in the dialog box
  • Click Remove
  • Click on the Edit button in the Horizontal (Category) Axis labels dialog box
    and select the zipcode data (A8:A11) as the Axis label range
  • Make sure not to include the heading zipcode
  • Click OK
  • Click OK

Now, you have a nice chart with Zipcode labels on the X axis!

Tuesday, November 17, 2009

Function Keys - Charts

Here is a quick tip about a really quick way to create a chart.

  • Select your data and then press F11. A full size chart will automatically be created on its own sheet.

  • Select your data and press Alt+F1 and a chart will automatically be created on the same sheet as the data.

Monday, November 16, 2009

Creating a Custom Chart in Excel 2007

Customized Charts
A custom chart type differs from the default chart type as a custom chart will retain formatting as well as chart type. A default chart only duplicates the chart type. Using a customized chart, better known as a template, can save you a lot of time as you don't have to recreate the chart type, font and/or colors everytime you create a chart. It is also very useful if you have multiple people creating a presentation - you can save the custom chart/template on the network for everyone to access - this will present a unified cohesive looking presentation.
You can also create more than one custom chart.

If you like to do all your charts in your corporate colors and include a corporate logo and do it all in a specific font,then custom charts are for you. The best part is that it is very easy to do.

All you need to do is create a chart with all the formatting, chart type - everything exactly as you want it to look in all your future charts.

  • Click on the Design tab under Chart Tools
  • Click on Save As Template.
  • Name the chart and save it.

    When you want to retrieve the chart,
  • Click the Insert Tab
  • Select Other Charts
  • All Chart Types. from the Insert Chart dialog box
  • Select Templates then select the template chart you had created and saved earlier.

Now, all you have to do is change the data series and some of the documentation on the chart.
Easy... peasey.....

Thursday, November 12, 2009

Creating Default Charts in Excel 2007

Gosh..sorry.. I am really getting behind and it is only November.. December is not looking promising. Anyway, I wanted to spend some more time talking about charts particularly since there are a lot of changes between Excel 2003 and Excel 2007. You can still do almost everything that you could before -( the exception being using goal seek and charts)- it is just more steps and/or not as intuitive as before.

So let's start with some basics.
Today .. or tonight I want to talk about creating a default chart.

The default chart type for Microsoft Excel is a column chart. If you routinely create a different type of chart, such as a line chart, you can change the default chart type.
Create a default chart from an existing chart
  • Open the file containing the chart you want to use
  • Double-click on the chart
  • On the Type group, select Change Chart Type
  • Select the chart type you want to be the default.. i.e. a line chart
  • Click Set as Default Chart (located at the bottom of the dialog box)
  • Click OK
  • Please note that only impacts the chart type- it does not change the colors or other formatting elements.

Tomorrow... hmm I think I am on vacation tomorrow... Okay on Monday we will discuss how to create custom charts containing colors, formatting etc that you want applied to all or most of your charts. Have a good weekend if I don't talk to you tomorrow.

Tuesday, November 10, 2009

Using Graphics as a Data Series

Using Graphics as a Data Series
Adding a graphic can be a really nice effect particularly if you are doing a presentation. Of course it can also depend upon your product line. I've seen annual reports of some pharmaceutical companies using a pill/capsule in column charts so people really do use it.

Basically you right-click on the Series and select Format Data Series and then you can insert a graphic file. A quick alternative is to put a piece of clipart on your spreadsheet and then simply copy the graphic, select the data series and then paste it.

All the steps are below. Have some fun!

Right-click on the data series
Select Format Data Series
Click on Fill
Click Picture or texture fill
Click Insert from:…..File
Browse and select the folder that contains the picture you want to use
Click Insert
Click Stack - there are other options here... depends on the size of the chart and the graphic what you want to use.

In the example, above, I inserted the wine bottle which was a .jpg file and pasted in the beer mug which is clipart that I already had on the spreadsheet.

Thursday, November 5, 2009

Moving a Chart Series and Changing its Color-2007

Moving and Formatting Series within the Chart

You can move data series . Moving a series in the legend or in the chart itself can help emphasize or de emphasize it. You can also change the color and border thickness of a data series for the same purpose.

For example, if I was responsible for the Liquers division I would probably not like the chart shown to the left. Liquers looks a bit puny in comparison to the other products.

A quick fix would be to move Liquers up so that it appears as the first column rather than the last and then I could draw people's eyes to it by brightening up the color a bit.

To move a data series
1. Select the Chart
2. Click Select Data on the Data group
3. Select the legend entry to be moved
4. Click the up or down arrow to move it

To change the fill color of the data series
1. Select the data series that you wish to change
2. Right-click and select Format Data Series
3. Click Fill in the left column and then select your color choice- and you have a lot of them

Now , I overdid it a bit with the colors, but you get the idea. In this chart, you are definitely drawn to focus on the Liquers numbers.

Tomorrow, I will talk about how to change a data series into a graphic. So, if you work at the Packy-(anyone know what that is?) you could have a chart of beer mugs . If you work at a pharmaceutical company, pills might make a nice chart or if you work at a sports apparel shop you may want a chart of different football helmets... the choices are endless - just depends on how creative you are and how much time you have :)

Wednesday, November 4, 2009

Excel Charts - Adding and Removing Data Series

I definitely need a break from functions and since I am in the midsts of preparing a seminar on Charts for the Indianapolis CPA Society, I thought I would talk about them. Little did I appreciate the Chart Wizard in Excel 2003- everything is different in Excel 2007 and frankly it just seems a bit harder. I'm not really sure why they felt the need to change it since I don't think it enhanced anything. Oh well, let's talk about adding or removing a data series from an existing chart.

There are multiple ways to add and delete data in a chart.
Adding in a series

The simplest is to simply copy the data and column heading and paste it into the chart. This is the easiest way and works in all versions. In Excel 2007 though depending on the type of chart, sometimes it includes data labels for the added series- whether you want them or not!

An alternative method known as the long way is:

  • Select the chart
  • Select the Chart Tools ribbon
  • Click Select Data on the Data group
  • Click Add
  • Type the Series Name in the Edit Series dialog box
  • Click in the Series values: and enter the cell range of the data
  • Click OK.
The 3rd method which is the in-between way is :
  • Select the chart
  • Right-click and click Select Data
  • Click Add
  • Type the Series Name in the Edit Series dialog box
  • Click in the Series values: and enter the cell range of the data
  • Click OK.

    Tip: To add Non-Adjacent Data use the Control Key
    1. Select the first data series
    2. Press CTRL key
    3. Select the next data series
    4. Repeat Steps 1 and 2 until all series selected

    Delete a Series

    The quick way to delete a series is simply click and select it and then click Delete be careful and ensure that all the data points have been selected – not just one-

    An alternative method of deleting a series is below:

  • Select the chart
  • Select the Chart Tools ribbon
  • Click Select Data on the Data group
  • Select the name of the series you want to delete
  • Click Remove
  • Click OK

  • And finally another way to delete a series is to:
  • Select the chart
  • Right-Click on Select Data
  • Select the name of the series you want to delete
  • Click Remove
  • Click OK

Ms. Excel- Resident Excel Geek