Chapter 3
Microsoft Excel 2010





Welcome to the world of Excel£® Now in the business world, the financial world, the manufacturing world, and any other industry you can think of, you will see people using Excel£® It is by far one of the most used programs in the history of business applications£®
The contents of this chapter are sketched in Figure 3ª²1£®


Figure 3ª²1The relationship chart of this chapter



3£®1An Overview of Microsoft Excel 2010
To make managing and analyzing a group of related data easier, you can turn a range of cells into an Excel table as shown in Figure 3ª²2£®


Figure 3ª²2An example of an Excel table



3£®1£®1The elements of Excel table
A table can include the following elements:
1£® Header row
By default, a table has a header row£® Every table column has filtering enabled in the header row so that you can filter or sort your table data quickly, which are shown in Figure 3ª²3£® And you can turn off the header row in a table£® 



2£® Banded rows
Alternate shading or banding in rows helps to better distinguish the data, as shown in Figure 3ª²4£®



Figure 3ª²3Filter data and Sort data




Figure 3ª²4Banded rows



3£® Calculated columns
By entering a formula in one cell in a table column, you can create a calculated column in which that formula is instantly applied to all other cells in that table column, which is shown in Figure 3ª²5£®
4£® Total row
Once you add a total row to a table, Excel gives you an AutoSum dropª²down list to select from functions such as SUM, AVERAGE, and so on£® When you select one of these options, the table will automatically convert them to a SUBTOTAL function, which will ignore rows that have been hidden with a filter by default£® If you want to include hidden rows in your calculations, you can change the SUBTOTAL function arguments, which is shown in Figure 3ª²6£®



Figure 3ª²5An example of calculated columns




Figure 3ª²6An example of total row




Figure 3ª²7Resize the table by sizing handle

5£® Sizing handle
A sizing handle in the lowerª²right corner of the table allows you to drag the table to the size that you want, as shown in Figure 3ª²7£® 


3£®1£®2Create a table
You can create as many tables as you want in a spreadsheet£® To quickly create a table in Excel, do the following:
£¨1£© Select the cell or the range in the data£®
£¨2£© Select ¡°Home¡±¡ú¡°Format as Table¡±, as shown in Figure 3ª²8£®
£¨3£© Pick a table style, as shown in Figure 3ª²9£®
£¨4£© In the ¡°Format as Table¡± dialog box, set your cell range£®
£¨5£© In the ¡°Format as Table¡± dialog box, select the checkbox next to ¡°My table as headers¡±, an shown in Figure 3ª²10£® if you want the first row of the range to be the header row, and then click ¡°OK¡±£®


Figure 3ª²8Select ¡°Home¡±¡ú¡°Format as Table¡±




Figure 3ª²9Choose a style for your table




Figure 3ª²10Set cell range



3£®2Experiment 1: Basic Excel 
3£®2£®1Experiment purpose¡¡

The purpose of this experiment is to master the operations of building and closing an Excel, some basic operations including inserting and deleting rows and columns, basic formulas, table design and charts£® 
3£®2£®2Experiment contents 
Experiment 3£®1

This part of experiment should be finished in ¡°Excel Data£®xlsx¡± and save it as ¡°Excel Dataª²practice 0¡±£® 
¤r Insert a new row before the 16th row and input ¡°Tom¡±, ¡°6000¡±, ¡°Master¡±, ¡°132204198810018822¡±, ¡°85£®5¡±£®
¤r Delete the 18th row (After inserting)£®
¤r Insert a new column named ¡°Index¡± on the leftmost table and finish indexing.
¤r Insert a new column named ¡°Location¡± on the rightmost table and finish the input shown in Figure 3ª²11£® 
¤r Insert a new column named ¡°Total Salary¡± on the rightmost table£® The ¡°Total Salary¡± equals to ¡°Salary * Score / 100¡±£® 
¤r Insert a new column named ¡°Tax Rate¡± on the rightmost table£® When the ¡°Total Salary¡± is greater than 7000, the ¡°Tax Rate¡± is 0£®3£® Otherwise it is 0£® 
¤r Insert a new column named ¡°Age¡± before ¡°Salary¡± and finish calculations£® (Format: int(year(today())ª²mid(ID Card,7,4))£® (Hint: you cannot input ¡°ID Card¡± directly£®)
¤r Insert a heading:

¢Ù Insert a new row before the first row and input ¡°Information and Salary of the Dahua Company¡±, Merge and Center A1:K1, and set the font to ¡°Calibri¡±, size 14, bold, light green£®

¢Ú Select A2:K2, and set the font to bold, Orange Accent 6£®
¤r Add ¡°All Borders¡± on the table, and center texts£®
¤r Insert a Chart of 2D Clustered Column according to A3:C22£® 
After finishing this part of experiment, the final effect is shown as Figure 3ª²11 and Figure 3ª²12£® 


Figure 3ª²11The resulting table for Experiment 1




Figure 3ª²12The resulting chart for Experiment 1



3£®2£®3Experiment procedures 
3£®2£®3£®1Insert and Delete Rows and Columns¡¡

(1) Open the file ¡°Excel Data£®xlsx¡±, and save it as ¡°Excel Data ª² practice 0¡±£®
(2) Select the 16th row, right click it and select ¡°Insert¡± (Shown in Figure 3ª²13)£®



Figure 3ª²13Right click and select ¡°Insert¡±




(3) Input ¡°Tom¡±, ¡°6000¡± and ¡°Master¡± from A16 to C16 respectively (Shown in Figure 3ª²14)£®


Figure 3ª²14Input the first 3 columns




(4) Right click D16 and select ¡°Format Cells¡± (Shown in Figure 3ª²15)£®


Figure 3ª²15Right click D16 and select ¡°Format Cells¡±




(5) Choose the tab ¡°Number¡± and select the ¡°Text¡± on the Category (Shown in Figure 3ª²16)£®


Figure 3ª²16Change the format to ¡°Text¡±




(6) Input ¡°132204198810018822¡± and ¡°85£®5¡± on D18 and E18 respectively£® 
(7) Select the 18th row and right click it, choose ¡°Delete¡± (Shown in Figure 3ª²17)£® After deleting, the resulting table is shown in Figure 3ª²18£®


Figure 3ª²17Delete the 18th row




Figure 3ª²18The resulting table after deleting




(8) Right click the first column and select ¡°Insert¡± (Shown in Figure 3ª²19)£® 


Figure 3ª²19Insert a new column




(9) Input ¡°Index¡± on A1, and ¡°1¡± and ¡°2¡± on A2 and A3 respectively£® Select A3, press your mouse on the lower right corner of A3 and drag to A21 (Shown in Figure 3ª²20)£®


Figure 3ª²20Create index for the table


£¨10) Input ¡°Location¡± and ¡°Beijing¡± on relevant positions (Shown in Figure 3ª²21)£® Select G2£® Press your mouse on the lower right corner of G2 and drag to G12£® 


Figure 3ª²21Input ¡°Beijing¡± from G2 to G12




(11) Input ¡°Shanghai¡± from G13 to G21 in the same way (Shown in Figure 3ª²22)£®



3£®2£®3£®2Basic Formulas¡¡
(1) Input ¡°Total Salary¡± on H1£® Select H2£® Input the formula ¡° =C2*F2/100 ¡±£® Or input ¡®=¡¯, and then click C2, and input ¡®*¡¯, and click F2, and finally input ¡° /100 ¡±£® Press the key Enter (Shown in Figure 3ª²23)£®


(2) Select H2 and press the lower right corner of it, drag it to H21 (Shown in Figure 3ª²24)£®



(3) Input ¡°Tax Rate¡± on I1£® Select I2£® Select ¡°Insert Function¡± on the ¡°Formulas¡± tab£® Then there will be a dialog box£® Select ¡°IF¡± from the list£® Click ¡°OK¡± (Shown in Figure 3ª²25)£®



(4) Input each item in ¡°Logical_test¡±, ¡°Value_if_true¡± and ¡°Value_if_false¡± on the dialog box ¡°Function Arguments¡±£® After that, click ¡°OK¡± (Shown in Figure 3ª²26)£®



Figure 3ª²22Input ¡°Shanghai¡± from G13 to G21 




Figure 3ª²23Input a formula




Figure 3ª²24Fill the column ¡°Total Salary¡±




Figure 3ª²25Another way to input a formula




Figure 3ª²26Input a formula in ¡°Function Arguments¡±




(5) Select I2 and press the lower right corner of it£® Drag it to I21 to finish the calculation (Shown in Figure 3ª²27)£®


Figure 3ª²27Calculate Tax Rate for all students




(6) To calculate the age of each students, add a new column named Age before the column D£® Select C2 and input ¡°=INT(YEAR(TODAY£¨))-MID(F2,7,4)£©¡± on the ¡°Formula Bar¡± (Shown in Figure 3ª²28)£®



(7) Select C2 and press the lower right corner of it£® Drag it to C21 (Shown in Figure 3ª²29)£®



3£®2£®3£®3Table Design¡¡
(1) Insert a new row before the first row and input the title ¡°Information and Salary of the Dahua Company¡± (Shown in Figure 3ª²30)£®



(2) Set the title¡¯s font to ¡°Calibri¡±, size 14, bold, light green, center, and the second row¡¯s font to bold£® Fill the second row with ¡°Orange Accent 6¡± by using ¡°Fill Color¡± on the ¡°Home¡± tab (Shown in Figure 3ª²31)£®



(3) Select A1:J22£® Double click Center button on the ¡°Home¡± tab to center all texts£® Add all borders for it by using ¡°More Borders¡± on the ¡°Home¡± tab (Shown in Figure 3ª²32)£®



Figure 3ª²28Calculate age for each students




Figure 3ª²29Finish calculating ages




Figure 3ª²30Input the title




Figure 3ª²31Design for title and the second row




Figure 3ª²32Finish the basic design of the table




3£®2£®3£®4Charts¡¡
(1) To insert a chart of 2D Clustered Column, Select B2:C22£® Find ¡°Charts¡± on the Insert tab£® Click it£® It will show a list of all kinds of charts£® Select ¡°Column¡± and the first chart type under ¡°2ª²D Column¡± (Shown in Figure 3ª²33)£®


Figure 3ª²33Insert a chart of 2D Clustered Column




(2) The resulting chart will be shown in Figure 3ª²34£® You can try some other charts and other settings if you like£® 


Figure 3ª²34The resulting chart



3£®2£®4Self test practice
1. Practice 1

Open ¡°Excel Data£®xlsx¡± and copy the table in worksheet 2 and paste it onto a new Excel£® Save it as ¡°Excel Dataª²practice 1£®xlsx¡±.
¤r Insert a chart of 2D Clustered Column by using the table in worksheet 2 according to Figure 3ª²35£® 
¤r Set the gridlines to ¡°Dash Dot¡±£® 
¤r Set the ¡°Maximum¡± of yª²axis to 800 with the ¡°Major Unit¡± of 200£®
¤r Set the yª²axis to ¡°No Border¡±£®
¤r Set the Chart Title to ¡°Printers are more popular than projectors¡±£®
¤r Set the Shape Color to Green and Dark Blue respectively£®
¤r Set the table size to 12*10 (Height 12, Width 10)£®


Figure 3ª²35The resulting chart for practice 1




2. Practice 2
Open ¡°Excel Data£®xlsx¡± and copy the table in worksheet 3 and paste it onto a new Excel£® Save it as ¡°Excel Dataª²practice 2£®xlsx¡±.
¤r Insert a chart of 2D Clustered Column by using the table in worksheet 3 according to Figure 3ª²36£®
¤r Put ¡°Rate of Paid Users¡± to the Secondary Axis, and change its type to ¡°Line¡±£®


Figure 3ª²36The resulting chart for practice 2





3. Practice 3
Open ¡°Excel Data£®xlsx¡± and copy the table in worksheet 4 and paste it onto a new Excel£® Save it as ¡°Excel Dataª²practice 3£®xlsx¡±.
¤r Add the table named ¡°Sales Figure for Fenghua Market¡±, and set it to Calibra, size 18, bold, italic according to Figure 3ª²37£®
¤r Fill the Index column with 01001 to 01007£®
¤r Calculate the Total Sale by multiplying ¡°Price¡± and ¡°Sale¡±£® 
¤r Merge and Center A10 : D10, and calculate ¡°Total¡± on E10£®
¤r Set the ¡°Format Cells¡± of the two columns ¡°Price¡± and ¡°Sale¡± to ¡°Number¡± with 2 Decimal places£®
¤r Add solid blue Outside Borders to the table£®
¤r On D12, Calculate the amount of goods which has more than 300 Sales(Hint: Use the formula =COUNTIF(D3:D9), ¡°>¡±&300)£®


Figure 3ª²37The resulting chart for the Practice 3




3£®3Experiment 2: Advanced Excel 
3£®3£®1Experiment purpose¡¡

The purpose of this experiment is to master other advanced operations of Excel, including sorting, filter, and subtotal£® There are more advanced tools that you should study by yourself on the practice part£® After finishing this manual, you will have the capability to handle many practical problems by using Excel£® 
3£®3£®2Experiment contents¡¡
This part of experiment should be finished in worksheet named ¡°Data 2¡± of ¡°Excel Data 2£®xlsx¡± £® Copy the worksheet and paste to a new Excel£® Save it as ¡°Excel Data 2ª²practice 0£®xlsx¡±.
¤r Filter all staff whose gender is Female (Shown in Figure 3ª²38)£®
¤r Clear the filter£® 
¤r To find out which person gets the highest salary, or which gets the lowest, sort by the column ¡°Salary¡± from largest to smallest first, then sort by the column ¡°Name¡± from A to Z (Multiª²level sort) (Shown in Figure 3ª²39)£®
¤r To calculate the average salary of different diploma, you can use the ¡°Subtotal¡± tool (Shown in Figure 3ª²40)£®

¢Ù Sort by the column ¡°Diploma¡± from A to Z.

¢Ú Add subtotal to ¡°Salary¡± at each change in ¡°Diploma¡± by using average function£® 



Figure 3ª²38The resulting table after filtering




Figure 3ª²39The resulting table after sorting




Figure 3ª²40The resulting table after subtotal



3£®3£®3Experiment procedures
3£®3£®3£®1Filter¡¡

(1) Open the worksheet 2 named ¡°Data 2¡± in ¡°Excel Data 2£®xlsx¡±£® Copy the table and paste it to a new Excel£® Save it as ¡°Excel Data 2ª²practice 0£®xlsx¡±£® 
(2) Select A1, and then click the ¡°Filter¡± button on the ¡°Data¡± tab£® The filter is now available by simply clicking the list button on each item in row 1 (Shown in Figure 3ª²41)£®


Figure 3ª²41Click the button ¡°Filter¡± to enable filtering




(3) Click the list button on D1, and you can see the list for both sorting and filtering£® Click the checkbox ¡°Male¡± to cancel the selection£® It means we want to filter all female staff but not all male staff (Shown in Figure 3ª²42)£® Click ¡°OK¡±£® 


Figure 3ª²42Click the list button on D1 and set the filter




(4) The resulting table is shown in Figure 3ª²43£® You can notice the list button on D1 is different from others£® 


Figure 3ª²43The resulting table after filtering all female staff




(5) You should cancel the filter before you want to do other works on Excel£® To cancel it, you can simply click the ¡°Filter¡± button on the ¡°Data¡± tab again£® The button is not selected after doing that£® The table will go back to the original one (Shown in Figure 3ª²44)£®


Figure 3ª²44The way to cancel the filter




3£®3£®3£®2Multiª²level Sort¡¡
(1) Click the ¡°Sort¡± button on the ¡°Data¡± tab£® The sort dialog will be open as shown in Figure 3ª²45£® 


Figure 3ª²45Click the ¡°Sort¡± button




(2) Select the value for each field: Sort by ¡°Salary¡±, Sort on ¡°Values¡±, and Order from ¡°Largest to Smallest¡± (Shown in Figure 3ª²46)£®


Figure 3ª²46Select the value for each field




(3) If you click ¡°OK¡± right now, it can only implement a oneª²level sort£® To implement the multiª²level sort, Click the ¡°Add Level¡± button to add the second level for the sort (Shown in Figure 3ª²47).


Figure 3ª²47Add a new level for the sort




(4) Select the value for each field (Shown in Figure 3ª²48)£® Click ¡°OK¡±£® 


Figure 3ª²48Select the value for the second level of sort




(5) The resulting table after multiª²level sort is shown in Figure 3ª²49£®


Figure 3ª²49The resulting table after multiª²level sort




3£®3£®3£®3Subtotal¡¡
(1) Before adding a subtotal, sort by the ¡°Diploma¡± from ¡°A to Z¡± first (Shown in Figure 3ª²50)£®


Figure 3ª²50Sort by ¡°Diploma¡± from ¡°A to Z¡± first



(2) Click the ¡°Subtotal¡± button on ¡°Online¡± of the ¡°Data¡± tab£® Then the subtotal dialog will be displayed as shown in Figure 3ª²51£® To add the subtotal to ¡°Salary¡± at each change in Diploma, select the value for each field according to the Figure 3ª²51£® Click ¡°OK¡± after finishing£® 


Figure 3ª²51Open the Subtotal dialog and select the value for each field




(3) The resulting table will be shown in Figure 3ª²52£® 


Figure 3ª²52The resulting table after adding subtotal to ¡°Salary¡±




3£®3£®4Self test practice 
1. Practice 1

Open ¡°Excel Data 2£®xlsx¡± and copy the table in worksheet named ¡°Data 1¡± and paste it to a new Excel£® Save it as ¡°Excel Data 2ª²practice 1£®xlsx¡±£® Open a new document and save it as ¡°Screenshotsª²practice 1£®xlsx¡± to save screenshots£® 
¤r Add the column ¡°Total¡± and ¡°Average¡± after ¡°Physics¡±, and use the formula or function to calculate the total score and the average score of each student£® 
¤r To find out the ranking for total scores, do multiª²level sort: first, sort by ¡°Total¡± from largest to smallest, then by ¡°Student ID¡± from smallest to largest£® Take a screenshot of the resulting table and insert it to the document you have built£® 
¤r Add a new column ¡°Rank¡± after ¡°Average¡±, and input the rank of each student according to the table£® 
¤r To find out the ranking for physics scores, do multiª²level sort again: first, sort by ¡°Physics¡± from largest to smallest, and then by ¡°Name¡± from ¡°Z to A¡±£® Take a screenshot of the resulting table and insert it to the document£® 
¤r Filter all students whose gender is ¡°Female¡±£® Take a screenshot and insert it to the document£® 
¤r Clear the filter£® Input ¡°Average¡± in A15, ¡°=SUBTOTAL(1, K2:K10)¡± in B15, and ¡°=SUBTOTAL(104, J2:J10)¡± in B16£® Take a screenshot and insert it to the document£® 
¤r Clear the filter£® 
¤r Filter all students whose gender is ¡°Male¡± and ethnicity is ¡°Han¡±£® Take a screenshot and insert it to the document£® 
¤r Insert the filter condition in E19:G22 to filter all students who is failed in at least one course (shown in Figure 3ª²53)£® In the Figure 3ª²53, there¡¯s a logical relation ¡°and¡± in the same row, while ¡°or¡± in the same column£® So the Figure means to filter all students whose math is less than 60, or English is less than 60, or physics is less than 60£® Take a screenshot of the resulting table and insert it to the document (Hint: the filter condition can be added by selecting the ¡°Advanced¡± button)£®
¤r Change the filter condition to in E19:G21 to filter all students who are failed in both math and English, or failed in physics (shown in Figure 3ª²54)£® The Figure 3ª²54 means to filter all students whose math is less than 60 and English is less than 60, or physics is less than 60£® Take the screenshot and insert it to the document£® 



Figure 3ª²53Filter condition(1)




Figure 3ª²54Filter condition(2)


¤r Clear the filter£® 
¤r Use the same tool to filter all students whose math and English is greater than 80£® Take the screenshot and insert it to the document£® 
¤r Clear the filter£®
¤r Add subtotal to A1:K10£® Set ¡°At each change in¡± to ¡°Class¡±, ¡°Use function¡± to ¡°Average¡±, ¡°Add subtotal to¡± math, English, physics and average£® Take the screenshot and insert it to the document£® 
¤r Clear the subtotal£® Add another subtotal to the total score of each course according to ¡°Gender¡±£® Take the screenshot and insert it to the document£® 
¤r Clear the filter£® 
¤r After sorting by ¡°Class¡± from ¡°A to Z¡±, select all students in class 1601 and their three courses¡¯ scores£® Create a Doughnut chart£® Take the screenshot on the chart and insert it to the document (Hint: study how to insert a Doughnut by yourself)£®
¤r After sorting by ¡°Class¡± from ¡°A to Z¡±, select all students in class 1602 and their three courses¡¯ scores£® Create a Cylinder chart£® Take the screenshot on the chart and insert it to the document£® 


2. Practice 2
Open ¡°Excel Data£®xlsx¡± and copy the table in worksheet 2 named ¡°Data 2¡± and paste it to a new Excel£® Save it as ¡°Excel Data 2ª²practice 2£®xlsx¡±£® Study how to insert a ¡°Pivot Table¡± by yourself£® 
¤r Insert a ¡°Pivot Table¡± and count the total amount of male and female for each department (Hint: Row Label: Department, Column Label: Gender, Value: Name)£® Take the screenshot and insert it to the document£® 
¤r Insert another ¡°Pivot Table¡± and count the total amount of staff and the average salary for each department£® Take the screenshot and insert it to the document£® 

3£®4Experiment 3: Excel Project
3£®4£®1Project topic¡¡

£¨1£© You can select any topic you like to analyze data in Excel£® There are some topics you can choose from£® 
¢Ù Student grade analysis.
¢Ú Sales report.
¢Û Personal consumption statistic.
£¨2£© Suggestions: It will be better if you analyze data related to your major£® You can also choose data from your daily life, such as the statistics on the book information, on the consumption in cafeteria, or on the scores for some or all courses£® 
3£®4£®2Experiment requirements¡¡
£¨1£© Do data analysis to meet requirements in your chosen topic£® For example, the analyses on the students¡¯ score are as follows:
¤r Count the total score, and the total amount of courses a student chooses£®
¤r The average score of each course£® 
¤r Sort the data by scores or by classes£® 
¤r Insert a pie chart to compare the average score of several courses£® 
¤r Any useful statistic methods you can think of...
£¨2£© The attributes should be included in the table, that is, what does each column refer to? For example, if you do analysis on the students¡¯ score, the following columns should be contained:
¤r Student ID.
¤r Student name.
¤r Class.
¤r Course ID.
¤r Course name.
¤r Score.
¤r Any attributes you can think of...
£¨3£© The table should be in at least 20 rows and 4 columns£®