Under the sheets

In the third and final part of our Excel 2003 user guide, WINDOWS lifts the lid on charts, sorting and more using two real-world spreadsheet examples...

  • E-Mail
By  Published  November 30, 2006

In last month’s issue, we walked you through cell formatting, conditional formatting and more using a small business balance sheet example. This month however we teach you the A to Zs of creating and formatting charts, filtering and sorting data, and tons more using an event budget and company expense spreadsheet example. So jump on your PC right now, follow our instructions and you’ll work your way from a spreadsheet intermediate to an Excel pro in no time.

Event budget example

First off, head over to http://office.microsoft.com/en-us/templates/default.aspx. In the search field (located on the top of the page) type ‘event budget’ and hit the 'Go' button (see A). Next, click the ‘Event budget’ template that appears on the top of the page and click the ‘Download now’ button.

When you open the template, you’ll notice that there are three different worksheets: ‘Expenses’, ‘Income’ and ‘Profit-Loss summary’.

First click on the ‘Expenses’ worksheet tab and begin entering values for budgeted and actual expenses. As you enter numbers for each category, you’ll notice that the ‘Total Estimate Expenses’ and the ‘Total Actual Expenses’ on the top sheet are calculated automatically (see B).

Next, click on the ‘Income’ sheet and repeat the same process we just outlined for the ‘Expenses’ sheet.

Once you’ve finished entering the amounts, hit the ‘Profit-Loss summary’ worksheet tab. Here you’ll see a table on the left that includes the total for estimated and actual income and expenses, as well as the total profit (or loss) made (see C).

Using charts

To the right of the table in this worksheet is a column chart version of the same table (see C). This chart allows you to easily see a comparison between projected and actual income and expenses. To tweak this chart, simply right-click on a white area in the chart and then hit ‘Chart Options’.

Under the ‘Titles’ tab, fill the relevant details for the ‘Chart Title’ and X and Y axis. Next, click on the ‘Gridlines’ tab to add or remove major or minor gridlines in your chart. (We suggest that you display minor gridlines when dealing with column charts as this makes it much easier to view and evaluate data.)

If you want to display the exact values for each bar in the chart, head to Chart Options, click the ‘Data Labels’ tab and select the ‘Value’ option under the ‘Label Contains’ section, then click OK.

Start from scratch

If you’d like to create the ‘Profit summary’ column chart from scratch, here’s how:

4 First select the Profit- Loss summary table, however exclude the 'Total profit (or loss)’ row
4 Navigate to the File menu and click Insert/Chart. A chart wizard window will appear
4 Under ‘Chart Type’ select ‘Column’, choose the ‘Clustered Column’ chart and click ‘Next’ (see D). Keep on clicking ‘Next’ until you reach step 4
4 In step 4 choose whether you want the chart to appear in a new sheet or as in object in the current sheet and click ‘Finish’. Viola! Your chart is complete.

Dress up your charts

To add some pizzazz to your charts, right-click on a white area in the chart and choose the ‘Format Chart area’ option. Here you can change the fonts and the background colour.

To change the colour of one of the data bars in your chart, simply right-click on one of them and hit ‘Format Chat area’ again.

Once you’re done dressing up your chart, you can move it to any location of your worksheet. To do this, click on the white background of the chart, hold down the left mouse button and move your mouse.

When you start to move the mouse pointer it will change into a four-headed black arrow. Now drag-and-drop your chart anywhere on the Excel worksheet.

If you want to print your spreadsheet whilst preventing a particular chart from being printed, select the chart and right-click on it. From the shortcut menu, select ‘Format Chart Area’. Next, select the Properties tab and clear the checkbox beside 'Print Object’. Click OK.

Company expense example

Soon we’ll show you how to sort and filter data using a company expense sheet called companyexpense.xls that we have created ourselves. (To download this file, head over to www.itp.net/features and search for ‘Under the sheets - part 3’.)

However, before we begin using this spreadsheet let’s quickly cover what sorting and filtering is and how these functions can help make you more productive in Excel.

Sorting it out

Sorting involves arranging data in your worksheet by using information from one or more columns. For example, if you have a budget spreadsheet that lists expenditures, you could sort the information by department and then by category, giving you a quick way to see what each department is spending in each category. (This is a similar scenario to that of the expense example we’ll be using in this workshop.) Or suppose you have a 1,000 line vehicle inventory. If you have a customer that wants a 2006 black Honda Civic, you could manually search through your entire inventory spreadsheet, or, you could sort your sheet by model year, then by model, then by colour.

Filtering your data

Filtering is a completely different way to extract information, in that it allows you to selectively block data that you don’t want to see. By using a filter, you can keep all of your data in your sheet, but have Excel just show you a part of this data. For instance, let’s say you want to see all of the purchases approved by a particular manager. You could sort the data by the manager column, but you’d still see plenty of data that won’t be relevant. Apply a filter however and you’ll see only the information related to the manager you want.

Now that you’ve got the general idea behind sorting and filtering, let's sort the data in our company expense example (see E). Let’s say your manager approaches you and asks you to sort information by department, then by category.

First, select all the data in the sheet but exclude the header row. Next, navigate to File menu and click Data/Sort.

Under ‘Sort by’, click the drop-down box and select Department from the list. Next, under the ‘Then By’ choose ‘Category’ from the drop-down box. Click OK.

Now in pic F notice that all of the departments are grouped together and, within each department, the categories are grouped together.

The fast way to sort

Using the sort window is all well and good, but what if you just want to sort by a single column? Perhaps you have data that’s in no particular order and you just want to sort it by date and be done with it.

Excel provides you with a quick way to handle these super simple sorts. Take a look at Excel’s shortcut toolbar. A little more than two thirds of the way across the toolbar, there are two buttons for quick sorting: one is labeled ‘AZ’ with a down arrow and another ‘ZA’ with an up arrow.

Using Auto filter

The best way to better explain filtering and how to use it is to jump right into the use of the AutoFilter feature. Unlike sorting, the AutoFilter function does not have its own dialog window.

To turn on AutoFilter, chose cell A1 (which contains the ‘Date’ heading) and navigate to the File menu, and go to Data/Filter/AutoFilter. When you select this option, Excel will add drop-down arrows next to each column heading in your worksheet (see G)

If you click on any of the down arrows, you’ll get a shortcut menu with a number of options. Each menu will be different and is tailored with the data in that particular column. In the company budget example, if you click the down arrow in Column C (i.e. the Department column), you’ll get the menu shown above in pic H.

Here’s an explanation of each of the options on the filter menu:

4Sort Ascending: Lets you sort a column in ascending order
4Sort Descending: Lets you sort a column in descending order
4(All): Shows all of the records in a particular column. (You would generally use this if you’ve already filtered the information and you now want to ‘un-filter’ a particular column and display all the data.)
4(Top 10…): Want to see the top ten priciest expenditures in this example? Then simply head over to the ‘Amount’ column and press this option. A new window will appear.
4(Custom…): Allows you to create a custom filter. For example, you could create a filter that only shows expenditures that are greater than $5,000 but less than $15,000. When you choose the Custom option, you get a window in which you can make selections for your criteria (see pic I on page 74).

4This column’s choices: This is where the filtering option really shines. If you select one of these items, your table will shrink and show you only these records. This makes it very easy to analyse data, since you can easily omit the information you don’t want. Not to worry, though; Excel doesn’t erase your information; it just temporarily hides it from view.

For instance, you can add a filter that shows just IT’s equipment expenditures. To do this, simply select Equipment from the auto filter drop-down box and then select IT from the Department drop-down box.

Clear filters

There are a couple of ways to clear filters. First, you can clear them on each individual column by clicking the filter down arrow and choosing the filter down arrow and choosing the ‘(All)’ option. This is useful if you've set multiple filters and want to clear just one of them.

If you want to leave the filtering down arrows enabled but show all of your records, go to Data/Filter/ Show All.

You can also clear all your filters in one clean sweep by turning off AutoFilter. This also removes the AutoFilter arrows from your columns. To do this, go to Data /Filter/AutoFilter. This removes the checkbox next to the AutoFilter option.

Get more from your sheet

If you want to calculate the total expenses for a certain department or category, we recommend using the SUMIF function. First select column C, navigate to the File menu and then go to Insert/Name/Define. Under the ‘Name in workbook’ option, type ‘Department’. Then select column B, and repeat the same process outlined above, however in the name in the workbook option type ‘Amount’.

Next, click on cell G5 (or in any empty cell) and click on the Insert Function button located to the left of the formula bar. Next, scroll the list, select SUMIF and click OK. A new window will appear. In the Range field type ‘Department’ and in the Criteria field type IT. In the ‘Sum_range’ field type ‘Amount’ and click OK (see pic J).

This formula will now calculate the total amount of expenses for the IT department only, which is $51964.94.

To calculate the number of expense entries for the Administration department, click on cell H10.

Next, click on the ‘Insert function’ button once more and enter ‘countif’ (without the single quotes) in the search box that appears on top of the following window. Now hit ‘Go’ and click COUNTIF under the ‘Select a function’ section. Click OK.

In the Range field type 'Department' and in the Criteria field type ‘Administration’ (see pic K). (Note: if you don’t include double quotes in the Criteria field, the function will not work). Now click OK. This formula will calculate the total of expense entries for the Administration department.

This concludes our three-part guide to Excel 2003. Be sure to send us your feedback to windows@itp.com. Now for all of you who wish to learn Access 2003, don’t miss our first 2007 issue!

Add a Comment

Your display name This field is mandatory

Your e-mail address This field is mandatory (Your e-mail address won't be published)

Security code