Creating a Table
Flash Fill
In today's lesson we will start where we left off. your sheet should currently look like figure 21.
Figure 21: Your table so far.
Activity
Use the flash fill feature (either using "fill series" or by filling in 2 or 3 cells and then dragging to flash fill as per the video above) to number your to-do list from 1 through 14.
Insert A Table (Pre-definded Tables)
Select the cells containing the data you wish to convert into a table, and then move the mouse cursor to the tab Insert and select Table.When you are using data that isn't already formatted as a table, Excel will automatically convert your data to a table to the best of it's ability. You can change the format that it chooses once it is created.
Activity:
- Select the cell range =A4:E18
- Click Insert and then select Table
- When it asks you to confirm the cell range for your table, check that your screen matches that in figure 22 below and that the check box "My table has headers" is ticked.
- Click ok
Figure 22 Table Creation Dialogue Box
Your table should look like that in figure 23.
Figure 23 Table Creation
Changing the Column Headings
Now you are able to Type over the data in Row 4 to create new column headings.
Activity:
Change your headings to:
| Line Item | Details | This Week | Next Week | Completed |
Sorting Data
We can now sort our list of data in a way that suits us a little better - by priority, if we need to do it this week or next week, we can sort it alphabetically, numerically, or we can filter out results we do not need.
Let's start by looking at where sorting and filtering are found:
Figure 24: Sorting Data
To use the sort function, you will need to have the data in your table selected. So let's go ahead and highlight the entirety of our table, from Cell A4 through E18. Once your table is highlighted, follow the trail in Figure 24 to get to the sorting menu.
Once the sort menu pops up, you can choose to sort your data by a particular column, on a different value, in any given order, or even sort the data horizontally, by rows, by clicking on the options button.
Let's try clicking the drop down menu on Column Sort by and selecting Details.
Next, click the drop down menu on Sort On and select Values.
Let's sort the list in alphabetical order, by selecting A to Z in the Order box.
Did you notice how your list changed?
Now let's try putting a priority on each task - whether it needs to be achieved this week, or next week.
You can do this one yourself. Select a few list items that you think should be completed this week, and in the "This Week" Column, put a Y to mark those as a priority. Choose as many or as few as you like.
Once you've done this part of the activity, your table should look a little like that in Figure 25.
Figure 25. Table Activity
Now let's try sorting the list again. Select the entire table again, from Cell A4 through E18. Once your table is highlighted, follow the trail in Figure 24 to get to the sorting menu.
Next, click the drop down menu on Sort On and select Values.
Choose A to Z in the Order box again. You could choose Z to A here in this instance, as we only have one letter it doesn't really matter, it will just group all of the Y's together.
Now that we have all of the things we want to do this week grouped together, it is easier for us to see the things we plan to do next week. Let's add a Y to the Next Week column for those items.
Your table should now have a Y in every line, regardless of which column it is in.
Filtering
Figure 26: Filtering Data
Now that we have filterable data, we can use this handy feature. Filtering allows us to "hide" data that we don't want or need to see, while not deleting the data, so that when we DO need the data later on, it is still there for us to use.
We are currently using our "Master List" sheet. Now it is time for us to copy our data over to our empty sheets "This Week" and "Next Week" and turn on our Filters!!
Activity
Right Click in the Top Left corner of your sheet, where the rows and columns meet, as shown in Figure 27, and select Copy.
Move your cursor into the sheet "This Week" and Right Click in the same space on this sheet (Figure 27) and select Paste. It is important here that you do not select paste values, or paste formatting, or paste formulas. You want to select the normal blank page on a clipboard icon without anything on top of it - if you select one of the others you will have to adjust the formatting yourself manually.
Move into "Next Week" and Right click in the corner again to paste the table one more time.
Figure 27. Copying the formatting and data of a whole sheet.
Now that we have copies of the data away from the Master list, we can filter it to only show what we need to do THIS WEEK, or NEXT WEEK, so we can see what our workload will be like in the library for the next fortnight.
Let's start by hopping into the sheet "This Week"
There are currently little drop down arrows in the corner of each of the column headings. These are FILTER buttons.
We can turn filtering on and off by highlighting the table and selecting the Filter icon as seen in figure 26.
Because we created this data as a table, filtering has been turned on automatically for us.
Select the drop down menu on This week and un-tick the Select all box and blank box, so that only the Y box is ticked.
This will hide all of the tasks that do not have a Y in this column.
Figure 28. Filter Tool
Now, go to the Next Week table and Filter the Next week column by Y again.
That's it, you've done it, Click through to the final page to reach our conclusion of the course. WELL DONE!!
Until Next Time!