LESSON WORKBOOK: Level 1 Lesson 8 2007.xlsx
Data sorting is an extremely handy and versatile feature within Excel. The sorting feature is found by going to the Data tab then selecting Sort to access the Sort dialog box. Generally, sorting is performed on a list, which is defined as a contiguous (no blank cells) group of data where the data is displayed in columns and/or rows. Excel allows you to sort Worksheet data alphabetically, numerically or chronologically. You can sort by columns, by rows, in a Smallest to Largest order (A - Z) or a Largest to Smallest order Z - A order and from left to right. When you sort within Excel, it will rearrange the contents of the sort area according to the instructions that you give it. Excel will always sort blank cells to the bottom of a list.
There are a few basic rules that you need to follow when setting up your list in the first place. These are:
Check that any numbers in your spreadsheet are in a numerical format.
Cells containing both text and numbers need to be formatted as text.
Dates and times must be formatted correctly.
You must unhide any hidden rows or columns (we will cover hiding rows and columns later in this lesson).
Make sure you have no leading spaces. This can happen when you push your space bar before typing into the cell.
Enter column labels in one row (use Alt + Enter to put a hard return in if you need to) or use the Orientation feature under Page Setup on the Page Layout tab.
Excel has specific sort orders to arrange data within your Worksheet according to the value (not the format) of the data. If you performed a Smallest to Largest sort, numbers would be sorted from the smallest negative number through to the largest positive number. If you performed a Largest to Smallest sort, numbers would be sorted in reverse.
If you performed an ascending (lowest to highest) sort on alphanumeric text, Excel will sort your data from left to right, character by character. For example, if a cell contained the text B200, Excel will place the cell after a cell that contains the entry B2 and before a cell that contained the entry B22.
Text that includes numbers and normal text are sorted in the following order:
0 1 2 3 4 5 6 7 8 9 (space) ! " # $ % & ( ) * , . / : ; ? @ [ \ ] ^ _ ` { | } ~ + < = > A B C D E F G H I J K L M N O P Q R S T U V W X Y Z Apostrophes (') and hyphens (-) are ignored, with one exception; if two text strings are the same except for a hyphen, the text with the hyphen is sorted last.
If you are sorting logical values, FALSE will always be sorted before TRUE and if you are sorting error values, they are all equal. We will be covering logical values in the last lesson when we look at the IF function.
As a tip, although it is not really necessary with the fabulous and very handy Undo feature, it is a good idea to save your Workbook prior to performing a sort, just in case.
Let’s have a go at performing some different types of sorts.
Open the Workbook Data Sorting.xlsx.
Make sure you are on the Sorting with Text sheet tab.
Click in any cell
Go to the Data tab and under Sort & Filter options, select Sort to display the Sort dialog box. You will notice here that when you do this, your list will be highlighted (not including the headings). This is the default of the Sort operation (note that the option ). This is, because you are clicked in a cell within the list, Excel will search in all directions from your active cell for the end of the list. The end of the list is deemed to be the first blank cell encountered in all directions, up, down, left and right. If you manually highlight your data and Excel sees there is data immediately to the left, right, top of bottom Excel will ask if you wish to include the data.
Notice in the top box Sort By you have the column heading Staff Payroll No. displayed.
Click the drop down arrow to the right of the Sort By box and you will see the other column headings from your list.
We wish to sort by the Last Name column, so select Last Name from the list.
In the Sort On box, select Values (it should already by showing)
In the Order box. select A to Z. (This is an ascending order)
At the top right hand side of the of the Sort dialog box ensure that the My data has headers option has been checked. If you did not check this box, Excel would include the headings in your list as part of the sort operation.
Click on OK to sort your data by Last Name.
You will notice now that the Last Name column is sorted in alphabetical order, from the lowest value to the highest value.
Let’s reverse the order from Smallest to Largest (A-Z) to Largest to Smallest (Z-A)
There are two ways you can do this.
Stay clicked in any cell in your data
Go to the Data tab and under Sort & Filter options, select Sort to display the Sort dialog box.
Under Order hit the drop down arrow and change the order to Z to A.
Click on OK to sort your data by Last Name in Descending order.
OR
Click the Sort Largest to Smallest tool, which is the Z on top of the A next to a downward pointing arrow next to the Sort button under Sort & Filter options on the Data tab.
By selecting this icon, you can effectively by-pass the Sort dialog box and perform a Largest to Smallest (Z to A) sort. You will note that above the Sort Largest to Smallest tool, is the Sort Smallest to Largest tool. If you have previously performed a sort on a list, Excel will remember the last sort that you did, but be aware that if you are uncertain of the last sort, your list data may not sort as you expect. In other words, if you are using a file or computer that is shared, it is always best to use the Sort dialog box.
When you perform a multiple column sort, each column is sorted one at a time. The list will be sorted by the first column then Excel will check each of the entries to see if there are duplicates. If there are, then the duplicates will be sorted by the second column and so forth.
Click in any cell
Go to the Data tab and under Sort & Filter options, select Sort to display the Sort dialog box.
Click on the drop down arrow to the right of the Sort by box to see the other column headings.
Click the drop down arrow to the right of the Sort By box and you will see the other column headings from your list.
Click on Department
In the Sort On box, select Values (it should already by showing)
In the Order box. select A to Z. (This is an ascending order)
Select the Copy Level tool (this will produce a copy of the sort - if you want a clean row to select your sort options from select the Add Level tool)
Select the drop down arrow to the right of the Then By box.
Select Last Name from the list.
All other options should remain the same.
Click on OK.
Now you have performed a sort on more than one column, let us have a look at sorting numbers. These will work just as efficiently as text when performing a sorting operation.
Let’s find out which Department generated the most income during the month of June.
Click on the Sorting with Numbers worksheet tab.
Click in any cell except the total row.
Go to the Data tab and under Sort & Filter options, select Sort to display the Sort dialog box.
Click on the drop down arrow to the right of the Sort by box and select June
In the Sort On box, select Values (it should already by showing)
In the Order box. select Lowest to Highest.
Click OK.
As you can see, sorting is a very simple operation to perform with either text or numbers. You can also sort from left to right in rows, this is a little trickier, but you will find it a very handy feature to know. Let's have a go at performing a left to right sort on our Sorting with Numbers Worksheet.
Highlight the range B5:G12. Quick way – click in B5, move the mouse over cell G12, hold down the shift key and press your left mouse button – quick and easy.
Go to the Data tab and under Sort & Filter options, select Sort to display the Sort dialog box.
Click on the Options button to see a list of options available to you.
Under Orientation select Left to Right.
Click on OK to return you to the Sort dialog box.
In the Sort by box, click on the downward pointing arrow to the right and select Row 12.
Click OK.
As you can see, sorting, once mastered, can be a huge benefit to a user. Remember to set up your data in a list. That is with column headings (defined in some way, such as bolded and centered) and without any blank cells, rows or columns. If you don’t, your sorting operation can turn into a nightmare! Remember the very handy undo key that you can use if you make an error, however, it is a good habit to get into to save your Worksheet first before performing a sort.
A very handy feature of Excel is its ability to hide rows and columns from a user without it affecting calculations in any way. This can be handy if you wish to hide calculations or certain information from a user. Hiding rows or columns can be performed in two ways, by selecting the row or column you wish to hide and going to Cells options on the Home tab and selecting Format>Hide & Unhide>Hide Columns or by selecting the row or column that you wish to hide, right clicking and selecting Hide.
Let’s have a look at this now.
On a new Worksheet, click in B2 and type 100. In C2 type 100, in D2 type 100, in E2 type 100, in F2 type 100.
Click in G2 and use the AutoSum feature under Editing on the Home tab to sum the range B2:F2.
Select the entire column D by selecting the column reference (the D with the grey background).
Go to the Home tab and under Cells options select Format>Hide & Unhide>Hide Columns
You will notice now that column D has disappeared, but the result of your formula, 500 has not changed. This is because you have only hidden the column, not deleted it.
Lets unhide the column now.
Highlight the entire columns C and D by clicking on the column headers and then go to the Home tab and under Cells options select Format>Hide & Unhide>Unhide Columns
Notice that you have now unhidden column D.
As mentioned above, you can also perform the hide/unhide operation by right clicking and selecting either Hide or Unhide from the shortcut menu. This is my preferred option, but it is up to you which one you use.
Lets have a go at hiding some rows, using the right click option.
In B3 type 100, in B4 type 100, in B5 type 100, in B6 type 100, B7 type 100.
In B8 use the AutoSum feature to sum the range B3:B7.
Now select the entire row 3 by selecting the row reference.
Right click and select Hide.
Select the entire row 5 by selecting the row reference.
Right click and select Hide.
You should now have two rows hidden, but your formula result will still be 500.
Lets unhide the rows now.
Highlight rows 2 to 7.
Right click and select Unhide.
You can also hide sheets using the Hide button under Window options on the View tab or by selecting the Home tab, then under Cells options selecting Format>Hide & Unhide>Hide Sheet You need to be aware that the right click option is not available if you wish to hide a sheet. You must do it via the View tab. As with hidden rows and columns you can still reference the hidden sheet via a formula and have it return the correct value. Of course though it is wise to reference the sheet while it is visible and use the mouse pointing method to build your reference and then hide it.
If you go to the Hide button under Window options on the View tab and the Unhide is greyed out this means there are no Worksheets hidden within the Workbook. The same applies if you go to Format>Hide & Unhide>Unhide Sheet via Cells options on the Home tab. If there are sheets hidden the Hide will not be greyed out and selecting it will display the Unhide dialog box. Within this box will be the names of all hidden sheets, to unhide one simply select the sheet name from the box and clicks OK or double click it (the sheet name).
Another very handy feature of Excel is it’s ability to use its built-in Table Formats on your data, and the flexibility that you have in changing these to suit your personal taste. There are many Table Formats available to you and they are found by clicking Format as Table under Styles options on the Home tab. Lets have a look at how this would work with our previous Workbook Data Sorting.
Open the Workbook Data Sorting. .xlsx
Click on the Sorting with Text sheet tab.
Click on cell D5 or any other single cell within your list.
Select Format as Table under Styles options on the Home tab and a range of table formats will present under the headings Light, Medium and Dark
Hover your mouse over the different styles until you find one that you like.
When you find the desired table style, click on it and the Format as Table dialog will pop up, you should see your whole table range highlighted behind the dialog box and the table range will appear in the box.
Note here that as when doing a sort operation, Excel will automatically highlight your whole table for you (it will only do this if there are NO blank columns or rows).
Click OK.
You will notice that once you have applied a Table style to your table, a new tab, the Design tab will become available. This tab gives extra options relating to tables in particular. Have a look at the Table Style options and click in each option to see the effects on your table. The Table Styles area allows you to easily change the Table Style you have selected. Just scroll through the list and make a change if desired.
You can create your own specified Table Style and save it so it is available for selection in the same area as the other Table Styles by selecting New Table Style at the bottom of the Table Styles designs, giving the style a name and selecting your desired attributes.
As discussed in Lesson 6, Cell styles are like Table Formats, but they format cells rather than the whole table. All they are are a set of attributes such as font, font size, cell color, border color etc that can be applied to one cell or many cells.
Try this:
Open the Workbook Data Sorting.
Click on the Sorting with Numbers sheet tab.
Highlight A4:H12
Select Cell Styles under Styles options on the Home tab and a range of cell formats will present under the headings Good, Bad and Neutral, Data and Model and Titles, Headings and Number Format
Hover your mouse over some of the styles to find one that you like.
Click to accept the style.
You can create your own specified Cell Style and save it so it is available for selection in the same area as the other Cell Styles by selecting New Cell Style at the bottom of the Cell Styles designs, giving the style a name and selecting your desired attributes.
You can quickly and easily select a Cell Style from the built in Style gallery in Excel, rather than use the formatting tools on the Home tab. A Cell Style is a predefined set of attributes that can be applied to a cell or range of cells. Attributes include, font style, font size, font colour, cell background and number style. You can use one of Excel's existing Cell Styles, or even create your own if you prefer.
If you wanted to create your own Cell Style try this
Select any cell
Select Cell Styles under Styles options on the Home tab, then select New Cell Style
Give your style a name
Make your selection as per your requirements under Style Includes (By Example)
Click the Format button and choose the attributes for your new style
Click OK to save the style
Now select the cell(s) you wish to apply your new style to
Select Cell Styles under Styles options on the Home tab
Select the style under the Custom heading
Once you have applied a Cell Style to a cell or range of cells, you can easily remove it by Clearing the Cells Format (as described above) or selecting the cell, going to Cell Styles under Styles options on the Home tab, then selecting Normal under Good, Bad and Neutral.
OzGrid.com accepts no responsibility for any adverse affect that may result from undertaking our training.