Basic Tips for Working with Data PDF

Al-Mustaqbal University College Optics Techniques Department Lecturer: Samir H. Mohammed Basic Tips for Working with Data  Introduction Excel workbooks are designed to store a lot of information. Whet...

Al-Mustaqbal University College Optics Techniques Department Lecturer: Samir H. Mohammed Basic Tips for Working with Data  Introduction Excel workbooks are designed to store a lot of information. Whether you are working with 20 cells or 20,000, Excel has several features to help you organize your data and find what you need. You can see some of the most useful features below. In addition, be sure to review the other lessons in this tutorial to get step-by-step instructions for each of these features. Several tools that make it easier to view content from different parts of your workbook at the same time: 1. Freezing data 2. Sorting data 3. Filtering data 1. Freezing data A. To freeze rows: You may want to see certain rows or columns all the time in your worksheet, especially header cells. By freezing rows or columns in place, you will be able to scroll through your content while continuing to view the frozen cells. Lecturer 1 Samir H. Mohammed  Al-Mustaqbal University College Optics Techniques Department Lecturer: Samir H. Mohammed 1. Select the row below the row(s) you want to freeze. In our example, we want to freeze rows 1 and 2, so we will select row 3. 2. On the View tab, select the Freeze Panes command, then choose Freeze Panes from the drop-down menu. 3. The rows will be frozen in place, as indicated by the gray line. You can scroll down the worksheet while continuing to view the frozen rows at the top. In our example, we have scrolled down to row 18. Lecturer 2 Samir H. Mohammed  Al-Mustaqbal University College Optics Techniques Department Lecturer: Samir H. Mohammed B. To freeze columns: 1. Select the column to the right of the column(s) you want to freeze. In our example, we want to freeze column A, so we will select column B. 2. On the View tab, select the Freeze Panes command, then choose Freeze Panes from the drop-down menu. 3. The column will be frozen in place, as indicated by the gray line. You can scroll across the worksheet while continuing to view the frozen column on the left. In our example, we have scrolled across to column E. Lecturer 3 Samir H. Mohammed  Al-Mustaqbal University College Optics Techniques Department Lecturer: Samir H. Mohammed If you only need to freeze the top row (row 1) or first column (column A) in the worksheet, you can simply select Freeze Top Row or Freeze First Column from the drop-down menu. C. To unfreeze panes: If you want to select a different view option, you may first need to reset the spreadsheet by unfreezing panes. To unfreeze rows or columns, click the Freeze Panes command, then select Unfreeze Panes from the drop-down menu. Lecturer 4 Samir H. Mohammed  Al-Mustaqbal University College Optics Techniques Department Lecturer: Samir H. Mohammed 2. Sorting data Add more content to a worksheet, organizing this information becomes especially important. You can quickly reorganize a worksheet by sorting your data. For example, you can organize a list of contact information by last name. Content can be sorted alphabetically, numerically, and in several other ways.  Sort range sorts the data in a range of cells, which can be helpful when working with a sheet that contains several tables. Sorting a range will not affect other content in the worksheet. Lecturer 5 Samir H. Mohammed  Al-Mustaqbal University College Optics Techniques Department Lecturer: Samir H. Mohammed A. To sort a sheet: In our example, we will sort a T-shirt order form alphabetically by Last Name (column C). 1. Select a cell in the column you want to sort. In our example, we will select cell C2. Lecturer 6 Samir H. Mohammed  Al-Mustaqbal University College Optics Techniques Department Lecturer: Samir H. Mohammed 2. Select the Data tab on the Ribbon, then click the A-Z command to sort A to Z, or the Z-A command to sort Z to A. In our example, we will sort A to Z. 3. The worksheet will be sorted by the selected column. In our example, the worksheet is now sorted by last name. B. To sort a range: In our example, we will select a separate table in our T-shirt order form to sort the number of shirts that were ordered in each grade. 1. Select the cell range you want to sort. In our example, we will select cell range G2:H6. Lecturer 7 Samir H. Mohammed  Al-Mustaqbal University College Optics Techniques Department Lecturer: Samir H. Mohammed 2. Select the Data tab on the Ribbon, then click the Sort command. 3. The Sort dialog box will appear. Choose the column you want to sort. In our example, we want to sort the data by the number of T- shirt orders, so we will select Orders. 4. Decide the sorting order (either ascending or descending). In our example, we will use Largest to Smallest. 5. Once you are satisfied with your selection, click OK. Lecturer 8 Samir H. Mohammed  Al-Mustaqbal University College Optics Techniques Department Lecturer: Samir H. Mohammed 6. The cell range will be sorted by the selected column. In our example, the Orders column will be sorted from highest to lowest. Notice that the other content in the worksheet was not affected by the sort. If your data is not sorting properly, double-check your cell values to make sure they are entered into the worksheet correctly. Even a small typo could cause problems when sorting a large worksheet. In the example below, we forgot to include a hyphen in cell A18, causing our sort to be slightly inaccurate. Lecturer 9 Samir H. Mohammed  Al-Mustaqbal University College Optics Techniques Department Lecturer: Samir H. Mohammed C. Custom sorting Sometimes you may find that the default sorting options cannot sort data in the order you need. Fortunately, Excel allows you to create a custom list to define your own sorting order. To create a custom sort: In our example, we want to sort the worksheet by T-Shirt Size (column D). A regular sort would organize the sizes alphabetically, which would be incorrect. Instead, we will create a custom list to sort from smallest to largest. 1. Select a cell in the column you want to sort. In our example, we will select cell D2. 2. Select the Data tab, then click the Sort command. Lecturer 10 Samir H. Mohammed  Al-Mustaqbal University College Optics Techniques Department Lecturer: Samir H. Mohammed 3. The Sort dialog box will appear. Select the column you want to sort, and then choose Custom List... from the Order field. In our example, we will choose to sort by T-Shirt Size. 4. The Custom Lists dialog box will appear. Select NEW LIST from the Custom Lists: box. 5. Type the items in the desired custom order in the List entries: box. In our example, we want to sort our data by T-shirt size from smallest to largest, so we will type Small, Medium, Large, and X-Large, pressing Enter on the keyboard after each item. Lecturer 11 Samir H. Mohammed  Al-Mustaqbal University College Optics Techniques Department Lecturer: Samir H. Mohammed 6. Click Add to save the new sort order. The new list will be added to the Custom lists: box. Make sure the new list is selected, then click OK. 7. The Custom Lists dialog box will close. Click OK in the Sort dialog box to perform the custom sort. 8. The worksheet will be sorted by the custom order. In our example, the worksheet is now organized by T-shirt size from smallest to largest. Lecturer 12 Samir H. Mohammed  Al-Mustaqbal University College Optics Techniques Department Lecturer: Samir H. Mohammed Lecturer 13 Samir H. Mohammed  Al-Mustaqbal University College Optics Techniques Department Lecturer: Samir H. Mohammed D. Sorting levels If you need more control over how your data is sorted, you can add multiple levels to any sort. This allows you to sort your data by more than one column. To add a level: In our example below, we'll sort the worksheet by T-Shirt Size (Column D), then by Homeroom # (column A). 1. Select a cell in the column you want to sort. In our example, we'll select cell A2. 2. Click the Data tab, then select the Sort command. Lecturer 14 Samir H. Mohammed  Al-Mustaqbal University College Optics Techniques Department Lecturer: Samir H. Mohammed 3. The Sort dialog box will appear. Select the first column you want to sort. In this example, we will sort by T-Shirt Size (column D) with the custom list we previously created for the Order field. 4. Click Add Level to add another column to sort. 5. Select the next column you want to sort, then click OK. In our example, we'll sort by Homeroom # (column A). 6. The worksheet will be sorted according to the selected order. In our example, the orders are sorted by T-shirt size. Within each group of T-shirt sizes, students are sorted by homeroom number. Lecturer 15 Samir H. Mohammed  Al-Mustaqbal University College Optics Techniques Department Lecturer: Samir H. Mohammed If you need to change the order of a multilevel sort, it is easy to control which column is sorted first. Simply select the desired column, and then click the Move Up or Move Down arrow to adjust its priority. Lecturer 16 Samir H. Mohammed  Al-Mustaqbal University College Optics Techniques Department Lecturer: Samir H. Mohammed 3. Filtering data If your worksheet contains a lot of content, it can be difficult to find information quickly. Filters can be used to narrow down the data in your worksheet, allowing you to view only the information you need. A. To filter data: In our example, we will apply a filter to an equipment log worksheet to display only the laptops and projectors that are available for checkout. 1. In order for filtering to work correctly, your worksheet should include a header row, which is used to identify the name of each column. In our example, our worksheet is organized into different columns identified by the header cells in row. 2. 1: ID#, Type, Equipment Detail, and so on. 3. Select the Data tab, then click the Filter command. Lecturer 17 Samir H. Mohammed  Al-Mustaqbal University College Optics Techniques Department Lecturer: Samir H. Mohammed 4. A drop-down arrow will appear in the header cell for each column. 5. Click the drop-down arrow for the column you want to filter. In our example, we will filter column B to view only certain types of equipment. 6. The Filter menu will appear. 7. Uncheck the box next to Select All to quickly deselect all data. 8. Check the boxes next to the data you want to filter, then click OK. In this example, we will check Laptop and Projector to view only these types of equipment. Lecturer 18 Samir H. Mohammed  Al-Mustaqbal University College Optics Techniques Department Lecturer: Samir H. Mohammed 9. The data will be filtered, temporarily hiding any content that does not match the criteria. In our example, only laptops and projectors are visible. Lecturer 19 Samir H. Mohammed  Al-Mustaqbal University College Optics Techniques Department Lecturer: Samir H. Mohammed Filtering options can also be accessed from the Sort & Filter command on the Home tab. B. To apply multiple filters: Filters are cumulative, which means you can apply multiple filters to help narrow down your results. In this example, we have already filtered our worksheet to show laptops and projectors, and we would like to narrow it down further to only show laptops and projectors that were checked out in August. 1. Click the drop-down arrow for the column you want to filter. In this example, we will add a filter to column D to view information by date. Lecturer 20 Samir H. Mohammed  Al-Mustaqbal University College Optics Techniques Department Lecturer: Samir H. Mohammed 2. The Filter menu will appear. 3. Check or uncheck the boxes depending on the data you want to filter, and then click OK. In our example, we will uncheck everything except for August. 4. The new filter will be applied. In our example, the worksheet is now filtered to show only laptops and projectors that were checked out in August. Lecturer 21 Samir H. Mohammed  Al-Mustaqbal University College Optics Techniques Department Lecturer: Samir H. Mohammed C. To clear a filter: After applying a filter, you may want to remove—or clear—it from your worksheet so you will be able to filter content in different ways. 1. Click the drop-down arrow for the filter you want to clear. In our example, we will clear the filter in column D. 2. The Filter menu will appear. 3. Choose Clear Filter From [COLUMN NAME] from the Filter menu. In our example, we will select Clear Filter From "Checked Out". Lecturer 22 Samir H. Mohammed  Al-Mustaqbal University College Optics Techniques Department Lecturer: Samir H. Mohammed 4. The filter will be cleared from the column. The previously hidden data will be displayed. To remove all filters from your worksheet, click the Filter command on the Data tab. Lecturer 23 Samir H. Mohammed