آموزش کامل Excel 2010-EN

[A M I R]

[h=2]Sorting Data

[h=3]Custom Sorting [h=4]To Sort in the Order of Your Choosing:


You can use a Custom List to identify your own sorting order, such as days of the week, or, in this

example, t-shirt sizes from smallest to largest (Small, Medium, Large, and X-Large).




From the Data tab, click the Sort command to open the Sort dialog box.

Identify the column you want to Sort by by clicking the drop-down arrow in

the Column field. In this example, we will choose T-Shirt Size.

Make sure Values is selected in the Sort On field.

Click the drop-down arrow in the Order field, and choose Custom List...

Select NEW LIST, and enter how you want your data sorted in the List entries box. We will sort t-shirt sizes from smallest to largest.

Click Add to save the list, then click OK.

Click OK to close the Sort dialog box and sort your data.

The spreadsheet will be sorted in order of Small, Medium, Large, and X-Large.

 

[A M I R]

[h=2]Sorting Data

[h=4]To Sort by Cell Color, Font Color, or Cell Icon:
From the Data tab, click the Sort command to open the Sort dialog box.

Identify the column you want to Sort by by clicking the drop-down arrow in the Column field.

Choose whether you want to sort by Cell Color, Font Color, or Cell Icon in the Sort On field. In this example, we will sort on Font Color.

In the Order field, click the drop-down arrow to choose a color, then decide whether you want it ordered On Top or On Bottom.

Click OK. The data is now sorted by attribute rather than text.

​

 

[A M I R]

[h=2]Sorting Data

[h=3]Sorting Multiple Levels

Another feature of custom sorting, sorting multiple levels allows you to identify which columns to

sort by and when, giving you more control over the organization of your data. For example, you could

sort by more than one cell color (such as red, then yellow, then green, to indicate different levels of priority); or, as

seen here, sort students by homeroom number, then by last name.

​

 

[A M I R]

[h=2]Sorting Data

[h=4]To Add a Level:
From the Data tab, click the Sort command to open the Sort dialog box.

Identify the first item you want to Sort by. In this example, we will sort Homeroom # from Smallest to Largest.

Click Add Level to add another item.

Identify the item you want to sort by next. We will sort Last Name from A to Z.

Click OK.

The spreadsheet will be sorted so that homeroom numbers are in order, and within each homeroom, students are listed alphabetically by last name.

Copy Level will add a level by duplicating the one you have selected, and allowing you to

modify the sorting criteria. This is useful if you need to sort multiple levels that share some

criteria, such as the same Column, Sort On, or Order.

​

 

[A M I R]

[h=2]Sorting Data

[h=4]To Change the Sorting Priority:

From the Data tab, click the Sort command to open the Custom Sort dialog box.


Select the level you want to re-order.


Use the Move Up or Move Down arrows. The higher the level is on the list, the higher its priority.

Click OK.


​

 

[A M I R]

[h=2]Sorting Data

[h=3]Challenge!

Open an existing Excel workbook. If you want, you can use this example.

Sort a column in ascending

or descending

order. If you are using the example, sort by Homeroom #.

Add a second level, and sort it according to cell color, font color, or cell icon. If you are using the example, add a second and third level to sort by the red and grey fonts used in T-Shirt Color.

Add another level, and sort it using a Custom List. If you are using the example, sort by T-Shirt Size in the order of Small, Medium, Large, and X-Large.

Change the sorting priority. If you are using the example, re-order the list to sort by T-Shirt Color (red), then by T-Shirt Color (grey), then by T-Shirt Size, then by Homeroom #.

​

 

[A M I R]

[h=2]Outlining Data

[h=3]Introduction

If the amount of data in your worksheet becomes overwhelming, creating an outline can help. Not only does this allow you

to organize your data into groups, and then show or hide them from view; you can also summarize data for quick analysis

using the Subtotal command (for example, subtotaling the cost of office supplies depending on the type of product).


In this lesson, you will learn how to outline your worksheet in order to summarize and control how your data is displayed.

​

 

[A M I R]

[h=2]Outlining Data

[h=3]Outlining Data

Outlines give you the ability to group data that you may want to show or hide from view, and create

a quick summary using the Subtotal command. Because outlines rely on grouping data that is related, you must sort before you can outline.
​

 

[A M I R]

[h=2]Outlining Data

[h=4]To Outline Data Using Subtotal:
The Subtotal command can be used to outline your worksheet in many different ways. It uses common functions

like SUM, COUNT, AVERAGE, and more, to summarize your data, and place it in a group.




In this example, we will use the Subtotal command to count the number of t-shirt sizes that were ordered at a

local high school. This will also place each t-shirt size in a group, making it possible to show the count, but hide

the details that are not crucial to the placing of the order (such as the student's homeroom number and payment date).




Sort according to the data you want to outline. Outlines rely on grouping data that is related. In

this example, we will outline the worksheet by T-Shirt Size, which has been sorted from smallest to largest.

Select the Data tab, and locate the Outline group.

Click the Subtotal command to open the Subtotal dialog box.

In the At each change in field, select the column you want to use to outline your worksheet. In this example, we will choose T-Shirt Size.


In the Use function field, choose from the list of functions that are available for subtotaling. We will use the COUNT function to tally the number of each size.

Select the column you want the subtotal to appear in. We will choose the T-Shirt Size column.

Click OK.

The contents of your worksheet will be outlined. Each t-shirt size will be placed in its own group, and the subtotal (in this case, count) will be listed below each group.

​

 

[A M I R]

[h=2]Outlining Data

[h=3]Showing or Hiding Data [h=4]To Show or Hide a Group:

Click the minus sign, also known as the Hide Detail symbol, to collapse the group.

Click the plus sign, also known as the Show Detail symbol, to expand the group again.

You can also use the

or

commands on the Data tab in the Outline group. First

select a cell in the group you want to show or hide, then click the appropriate command.






​

 

[A M I R]

[h=2]Outlining Data

[h=4]To View Groups by Level:

The groups in your outline, based on their hierarchy, are placed on different levels. You can

quickly display as little or as much information as you want by clicking the level symbols

to

the left of your worksheet. In this example, we will view levels in descending order, starting with

the entire worksheet on display, then finishing with the grand total. While this example contains only 3 levels, Excel can accommodate up to 8.





Click the highest level (in this example, level 3) to view and expand all of your groups. Viewing groups at the highest level will display the entirety of your worksheet.

Click the next level (in this example, level 2) to hide the detail of the previous level. In this example, level 2 contains each subtotal.

Click the lowest level (level 1) to display the lowest level of detail. In this example, level 1 contains only the grand total.

​

 

[A M I R]

[h=2]Outlining Data

[h=3]Removing Groups and Subtotaling [h=4]To Ungroup Data:



Select the rows or columns that you want to ungroup. In this example, we will ungroup size Small.

From the Data tab, click the Ungroup command. The range of cells will be ungrouped.

To ungroup all the groups in your outline, open the drop-down menu under the Ungroup command, and choose Clear Outline.



Ungroup and Clear Outline will not remove subtotaling from your worksheet. Summary or subtotal data will stay in place and continue to function until you remove it.

​

 

[A M I R]

[h=2]Outlining Data

[h=4]To Ungroup Data and Remove Subtotaling:


From the Data tab, click the Subtotal command to open the Subtotal dialog box.

Click Remove All.

All data will be ungrouped, and subtotals will be removed.

​

 

[A M I R]

[h=2]Outlining Data

[h=3]Creating Your Own Groups
The Group command allows you to group any range of cells - either columns or rows. It does not

calculate a subtotal, or rely on your data being sorted. This gives you the ability to show or hide

any part of your worksheet, and display only the information you need.

​

 

[A M I R]

[h=2]Outlining Data


[h=4]To Create and Control Your Own Group:

In this example, we will prepare a list of t-shirt colors and sizes that need to be distributed to each

homeroom. Some of the data in the worksheet is not relevant to the distribution of the t-shirts; however, instead

of deleting it, we will group it, then temporarily hide it from view.




Select the range of cells that you want to group. In this example, we will group the First Name, Last Name, and Payment columns.

From the Data tab, click the Group command.

Excel will group the selected columns or rows.

Click the minus sign, also known as the Hide Detail symbol, to hide the group.

The group will be hidden from view.

Click the plus sign, also known as the Show Detail symbol, to show the group again.

​

 

[A M I R]

[h=2]Outlining Data

[h=3]Challenge!

Open an existing Excel workbook. If you want, you can use this example.

Outline your worksheet using the Subtotal command. If you are using the example, outline by t-shirt size.

Display the first level of groups in your outline.

Display the highest level to view your entire worksheet again.

Create your own group of rows or columns, then hide the group from view.

Ungroup any range of data.

Remove subtotaling from your worksheet.

​

 

[A M I R]

[h=2]******ing Data

[h=3]Introduction

******s can be used to narrow down the data in your worksheet and hide parts of it from view. While it may sound a little like

grouping, ******ing is different in the way that it allows you to qualify and display only the data that interests you. For example, you

could ****** a list of survey participants to view only those who are between the ages of 25-34. You could also ****** an inventory

of paint colors to view anything that contains the word "blue," such as "bluebell" or "robin's egg blue."


In this lesson, you will learn how to ****** the data in your worksheet to display only the information you need.

​

 

[A M I R]

[h=2]F i l t e r i n g Data

[h=2]F i l t e r i n g Data


Filters can be applied in many different ways to improve the performance of your worksheet. You can

****** text, dates, and numbers. You can even use more than one ****** to further narrow down your results.


​

 

[A M I R]

[h=2]******ing Data

[h=4]To ****** Data:

In this example, we will ****** the contents of an equipment log at a technology company. We

will display only the laptops and projectors that are available for check-out.




Begin with a worksheet that identifies each column using a header row.

Select the Data tab, and locate the Sort & ****** group.

Click the ****** command.

Drop-down arrows will appear in the header of each column.

Click the drop-down arrow for the column you would like to ******. In this example, we will ****** the Type column to view only certain types of equipment.

The ****** menu appears.

Uncheck the boxes next to the data you don't want to view. (You can uncheck the box next to Select All to quickly uncheck all.)

Check the boxes next to the data you do want to view. In this example, we will check Laptop and Projector to view only those types of equipment.

Click OK. All other data will be ******ed, or temporarily hidden. Only laptops and projectors will be visible.

******ing options can also be found on the Home tab, condensed into the Sort & ****** command.

​

 

[A M I R]

[h=2]******ing Data

[h=4]To Add Another ******:
******s are additive, meaning you can use as many as you need to narrow down your results. In this

example, we will work with a spreadsheet that has already been ******ed to display only laptops and

projectors. Now we will display only laptops and projectors that were checked out during the month of August.




Click the drop-down arrow where you would like to add a ******. In this example, we will add a ****** to the Checked Out column to view information by date.

Uncheck the boxes next to the data you don't want to view. Check the boxes next to the data you do want to view. In this example, we will check the box next to August.

Click OK. In addition to the original ******, the new ****** will be applied. The worksheet will be narrowed down even further.