Windows Tools, Help & Guides

One of the improvements in Excel 2007 is the rebuilt Chart Tool.  “It used to be so easy” to create charts in Excel, just highlight an area of cells and click Create Chart, and voilá. Well it still is – it only looks a bit different.  In this mini-tutorial I’m using an example from work, tho the names, and numbers have been altered.

The Mission

At work we have tightened security on mobile Exchange Synchronization. A security policy has been set which requires that we ask each of the 2.500 users to read the new guidelines. Failure to accept the new policy guidelines will result in the user loosing access to the server. To be able to monitor the progress of this work (making sure we know that every user has read and accepted) I was asked to create a chart to graphically display the weekly progress,  number of emails sent, number of accepts etc.  The challenge is that we only record names, and dates for each occurrence (notice sent, reply received, account open or closed).

The task

Create a table that will sort  dates into weeks, update numbers once a week (Fridays) and summarize everything in a line graph.

Preparations

I’ll skip a few steps and re-enter the timeline a couple of weeks into the mission when we have some actual data to process. My main table looks like this:

As you can see it’s a rather long list of dates. Only Cells with content will be counted in the final result.

Creating the Graph Table

I am in need of two sheets: “progress” and “graph”.  My main table is created in the sheet named “Progress”.  To be able to make use of my main data I created a new table (in the sheet “graph”) containing the following Columns: “Week”, “Updated”, “Replies”, “Expected”, “Emails”, “Stopped” and “New This Week”. Since I am going to summarize each week  the “Updated” column is the key to making this thing work as planned.

With the table in place I had to create a formula that will sort the dates into its corresponding week and only count cells containing dates. The formula is quite complex (and there might be other ways of making it work). But here is the one I ended up using:

=SUMPRODUCT(('progress'!$D$2:$D$155<='graph'!B4)*('progress'!$D$2:$D$155<>"")) 

Let’s check out what it does.

• SumProduct = Returns the sum of products of corresponding ranges or arrays. This function allows me to create a snapshot of the status on any given date.
•  'progress'!$D$2:$D$155 = This is the range where we have all the registered dates.
•  'graph'!B4 = This is the comparison date (Fridays)
•  'progress'!$D$2:$D$155<='graph'!B4 = Search for dates up until the date in Cell B4
•  *('progress'!$D$2:$D$155<>"")) = Count only Cells with content (dates)

I used this formula in the columns “Replies”, “Emails” and “Stopped”, only changing the range references (columns) and Comparison Date for each row.

Other Formulas used

In the “New this week” column I use this formula:

=IF(C4<>"";C4-C3;"")

It doesn’t do much except calculating the  “growth” compared to last week.

The Chart

With the finished calculating table prepared, it’s time to create our chart. Again I’m fast forwarding to the end of my project making the chart a better view.

Insert the Chart

Click the Insert-Ribbon, and choose the line tool. Insert a 2D line chart. If you have the table ready (with or without data), you may select the entire thing before you draw the chart. This will allow Excel to create the graph for you.

Editing the Chart

Should you want to re-size the chart, change or add a column or alter the appearance you can do so at any time. Highlight your Chart and look at the top of your screen –  you’ll notice three green highlighted ribbons, called “Chart Tools”: “Design”, Layout” and “Format”. Here you will find everything you’ll ever need,

The Chart Edit Tool

(named: Select Data) was revamped in Excel 2007. The tool allows you to easy change, add or remove columns (in the left pane) and X-axis data in the right pane. Each Column and row is displayed as separate entries in the list, making it easy to edit them to your needs.

To change layout elements you can select the entire chart or specific elements (like the color or line width) by right clicking.

Re-size and move the Chart

Place your mouse pointer over a chart corner or edge, this will activate the re-size and move tool. Simply drag and drop the chart to re-size or move it.

Add, Remove or change in-data

Editing the chart setup is easy as pie too. Highlight the chart, and click “Select Data”-button found in the Design Ribbon. In the graph that Excel created for you it added the Comparison dates to the X-axis. I don’t want it there, so I’m removing it.

In the Select Data Source window,  you see the X-axis (called: Horizontal) in the right side of the window.

• Click the EDIT-button.
• Change the axis label range from A3:B14 to A3:A14
• Click OK
• Click OK

And that is mostly it.

If you play around with this tool a few minutes you will discover that it’s not as hard as you’d think. Making charts is as easy as ever, perhaps even easier.

 

 

Free PC tips by email