Excel2010

If the consolidation involves other workbooks, you can use external reference formulas to perform your consolidation discussed in Chapter 5. For example, if you want to add the values in cell B2 from Sheet1 in two workbooks (named East and West); you can use the following formula:

=[East.xlsx]Sheet1!B2+[West.xlsx]Sheet1!B2

You can include any number of external references in this formula, up to the 8,000-character limit for a formula. However, if you use many external references, such a formula can be quite lengthy and confusing when you edit it. If the worksheets that you’re consolidating aren’t laid out the same, you can still use formulas, but you need to ensure that each formula refers to the correct cell.

6.4.2 Using Consolidate Command

You can use the Consolidate dialog box, displayed by choosing Data Data Tools Consolidate.

Example 6.4:

Your assistant director heard about your fame from the teachers and now he is asking a solution to gather marks from teachers into a worksheet.

Solution: He wants to prepare a list like in the Figure 6.15 and distribute it to teachers. Every teacher will fill in his part and return it back to him.

Select an empty worksheet and click the Consolidate button. It’ll open the Consolidate dialog box Figure 6.16).

Figure 6.15: Marks Sheet for distribution

This box, in general, is divided into 4 parts.

From the first combo		1 you select the consolidation
	function. You have the standard 11 outline functions here. In
	this example, because	teacher will enter information
	into his/her range, you can use the Average or Sum
	functions.
	2 box,	define ranges and, click the
	Add button. That range is inserted into the All references
	3	range and click Add
	button. When you change the active sheet, the address in
	Reference box automatically changes, too. So, if you

have identical ranges in the sheets, you don’t have to redefine the ranges, you just click the Add button to add the new range to the All References box.

1

2

3

5

4

Figure 6.16: Consolidate Window

Data Processing

111

	Some files submitted to you may be missing categories that
	aren’t used by some others. In this case, you can use a handy
	feature here that matches data by using row and column titles:
	Use labels in part 4	and Left column
	in this example.
	If you check	5 box, Excel will
	create dynamic links to the other worksheets/workbooks. So, if
	the teachers change their data in their	your file will be
	updated everytime your sheet is recalculated.
	After you finish adding all the worksheets,	OK to finish.
Figure 6.17: Consolidated Data	After you finish, it closes and returns the consolidation worksheet
	and prepares the table, as in Figure 6.17, using Outline option in
	Excel (we’ll discuss outline in the following	When you click
	on any plus sign on the left of the row headers, it	open the hidden
	rows and show the contents. As you see, each	contains linked
	cell value in the referred worksheet. And, at	bottom of each
	group, we have the outline function (row 7 in this case contains the
	Sum function).

6.5 Pivot Tables

A pivot table is essentially a dynamic summary report generated from a database. After a few simple clicks, a pivot table can help transform endless rows and columns of numbers into a meaningful presentation of the data. After you create a pivot table, you can rearrange the information in almost any way imaginable and even insert special formulas that perform new calculations. On the other hand, one minor drawback for using a pivot table is that a pivot table does not update automatically when you change information in the source data.

6.5.1 Inserting a Pivot Table

Select the table and

Press the Insert Tab Pivot Table button. This’ll open the Create Pivot Table dialog box.

Then, press OK. Excel then creates an empty Pivot table in a new sheet.

112	Microsoft Excel

a. Create Pivot table Dialog box		b. Empty Pivot Table		a. Adding Pivot table fields

Figure 6.18: Inserting a Pivot table

When you click over this empty range, Excel opens the Pivot Table Task pane on the right.

From this pane, you can insert or remove fields to the Pivot table or if necessary change properties.

Example 6.5:

In a sport center, the big boss wants to summarize the incomes from different fields to produce quick results. The table next shows the sample data. He wants to see and compare the summary according to Field, Qtr and Sports’ Sums.

Solution: Insert the Pivot Table. Choose Amount and Field from the fields List.

Figure 6.19b: Summary according to Fields

Sports	Qtr	Amount	Field
Golf	Qtr3	$1,500	North
Golf	Qtr4	$2,000	North
Tennis	Qtr3	$600	North
Tennis	Qtr4	$1,500	South
Tennis	Qtr3	$4,070	South
Tennis	Qtr4	$5,000	West
Golf	Qtr3	$6,340	South

Figure 6.19a: Accountancy Report

Data Processing

113

Figure 6.20a: Summary according to Sports & Qtrs

With another click you can show: Summary according to Sports and Qtr.

Deselect the Sports from the field list.

Figure 6.20b: Summary according to Qtrs

114	Microsoft Excel

		Questions
1.	You can filter according to colors.
	TRUE	FALSE
2.	You can sort according to colors.
	TRUE	FALSE

3.What is the meaning of this button?

a.Sort ascending

b.Sort descending

c.Enter formula

d.Autosum

4.Which of the following is true for filtering data?

a.It deletes the row(s) which meet criteria.

b.It moves the row(s) which meet criteria.

c.It hides the row(s) which meet criteria.

d.It displays the row(s) which meet criteria.

6.You applied AutoFilter to the table in question 4. Then, for the column ‘No’, you applied a custom filter as shown in the figure below. How many students will be displayed?

a. 5	b. 2	c. 3	d. 4

7.…………. means putting or arranging items in order, according to some criteria.

a. Listing	b. Auto filtering
c. Filtering	d. Sorting

5.Which of the following is used to show only the records according to given criteria?

a. Consolidate	b. Filter
c. Short	d. Pivot Tables

8.You have a list which contains all students’ information from all classes. But, you want to see only one class. Which Excel feature should you use?

a.Validation

b.Auto filter

c.Conditional formatting

d.Auto series

Data Processing

115

9.Which of the following is a way to sort numbers in decreasing order?

a.Select all numbers and click Sort ascending or Sort descending from the View tab.

b.Select all numbers and click on Descending order from the Data tab.

c.Select all numbers and click Sort from the Data tab, then check, Sort left to right from Sort Options.

d.All of them

10.…………. is a quick and easy way to find and work with a subset in a data list.

a. Sorting	b. Filtering
c. Spelling	d. Subtotals

11.You can consolidate data only from the different worksheets of the active workbook.

TRUE

FALSE

12.Which of the following function cannot be used in consolidation?

a. Count	b. Average
c. StdDev	d. VLookUp

13.Which one of the following is not true about sorting in ascending order?

a.Numbers are sorted from the smallest negative number to the largest positive number.

b.When you sort text, Excel sorts left to right, character by character.

c.In logical values, FALSE is placed before TRUE.

d.Blanks are always placed first.

14.In the figure below, you add a custom filter to column B as follows. Which rows are visible?

a. 3,	4, 5	b. 3,	4,	5, 9 10
c. 9,	10	d. 6,	7,	8

Project

1.Create a table including your friends’ names, surnames, birthdates, cities, addresses, etc. Copy this table onto two more sheets. On the second sheet sort the friends’ table according to birthday order. On the third sheet rearrange your friends according to their Cities and then addresses.

2.Collect different price lists from the companies in your town, for the same device. Create a custom list as shown below, and sort the list according to the price in ascending order so that you can have a good reference of cheaper prices.

3.Create the table in the following figure and then make the following arrangements on the table by using Auto Filter.

Determine the most successful salesperson that carries out the best sale.

Put the sales people in order according to their regions and sales.

4.For the figure next, write a criterion to list only the students from class 10.

Data Processing

117

5. Use the table below and prepare the following Pivot Chart

Sports	Qtr	Amount	Field
Golf	Qtr3	$1,500	North
Golf	Qtr4	$2,000	North
Tennis	Qtr3	$600	North
Tennis	Qtr4	$1,500	South
Tennis	Qtr3	$4,070	South
Tennis	Qtr4	$5,000	West
Golf	Qtr3	$6,340	South

6. Collect the data from Golf and Tennis worksheets into a new workbook (Mc.Watson07.xlsx).

	Golf				Tennis
Field	Qtr	Amount		Field	Qtr	Amount
North	Qtr3	$600		North	Qtr3	$1,500
South	Qtr4	$1,500		North	Qtr4	$2,000
South	Qtr3	$4,070		South	Qtr3	$6,340
West	Qtr4	$5,000

CHARTS

After you process your data, you take outputs. Printing and Charts are the two most common output forms of Excel. In Chapter 4, you studied Printing and Page Setup. In this chapter, you are going to prepare Charts from your data.

Numbers can definitely present a lot of information, but most people get lost in too many numbers. Especially, when presenting data to a crowd. People are more interested in visual and graphical representations and they are

Figure 7.1: Annual Sales Report much easier to remember. For example, with a graphical chart, as in Figure 7.1, you can easily see whether your company sales are increasing or decreasing, without having to analyze any number.

Figure 7.2: Istanbul city Monthly Weather Statistics

7.1 Inserting Charts

Excel offers powerful and easy to use Chart tools to create charts with some simple clicks. You select the range and then, select proper chart type from Insert Charts tab. It’s all that simple and your chart is ready.

Example 7.1:

For your geography class, the data sheet for Istanbul city monthly weather statistics has been given as in the Figure 7.2. And you are asked to prepare a chart for this.

Solution:

First, select the data range A1:B12,

Then click Insert Charts Column 3d Clustered Cylinder.

Figure 7.3:		Figure 7.4:
Charts group icons in the Insert Tab		Istanbul city Monthly Weather Statistics

First view of your chart is ready. And now, you can make further improvements on it.

120	Microsoft Excel