Data in an Excel worksheet is easier to analyze when formatted as a table. To help you understand tables and their many features, including sorting, filtering, slicers, and calculated columns, Belleyre Books has published a new video.
0:04 Excel 2016 Table from the book Microsoft Excel 2016 In 90 Pages by Beth Brown
0:10 In this video you will learn how to:
0:12 Format related data as a table.
0:15 Add a Total row to the table.
0:18 Sort and filter a table.
0:21 Create a slicer for filtering table data.
0:26 Create a calculated column.
0:33 In this spreadsheet, I will convert the range of inventory data to a table and then demonstrate the features of a table.
0:44 Notice the rows contain the same type of data in each column. A table is for grouping related information and this must be considered when deciding to format a range into a table.
0:51 The first step to creating a table is to select the range to be formatted.
0:56 Next, select the Format as Table command on the Home tab and the pick a table style.
1:02 Excel detected that the first row of data contained headers.
1:06 If your first row of data does not contain headers, Excel will automatically add generic headers for you.
1:15 If you’re not happy with this style, click Quick Styles on the Design tab and choose a different one.
1:27 To expand a table, simply drag the sizing handle.
1:30 Or, when the last cell in the table is selected, simply press the Tab key to add a new row.
1:39 Let’s add a new inventory item.
1:43 Notice that the new row has all the same formatting as the previous rows in the table.
1:48 You can also right-click in a table for commands to add or delete rows and columns.
1:54 To summarize table data, I’ll add a Total row.
1:57 To do this, click anywhere in the table and then in the Table Style Options on the Design tab, click Total Row.
2:06 A total is automatically added for the last column, but you can click in any total row cell and select how you want the column to be analyzed.
2:16 Tables make it easy to sort data.
2:18 Simply, click the drop-down list for the column you want to sort by and then select a Sort command.
2:25 The drop-down lists for the columns also have commands for filtering, or querying, table data.
2:32 For example, let’s see which items have less than 60 in stock.
2:35 For this, we’ll use the Number Filters command.
2:44 You can type the criteria and then select OK to filter the table.
2:50 To again display all the data, click the Clear Filter command.
2:58 Another way to filter data is with a slicer.
3:01 It’s a filtering tool that remains on the worksheet.
3:06 To add a slicer, on the Design tab, click the Insert Slicer command.
3:14 Choose which columns you want to have slicers displayed for
3:21 Slicers can be sized and moved anywhere on the worksheet.
3:35 To apply a filter, simply click on criteria.
3:42 To clear the filter, click the Clear Filter button.
3:52 The last thing I’ll demonstrate is how to create a calculated column.
3:58 Let’s insert a column and change the name to Value.
4:08 From anywhere in the column, I’ll add a formula to calculate the value of the inventory of each item.
4:26 Notice how cell references are relative to the table data rather than the exact cell row and column.
4:34 When I press Enter, formulas are automatically copied to the entire column.
4:40 Now we need to simply apply the appropriate format.
4:46 And then we can quickly add a new summary in the Total row
4:51 And there you have it.
5:03 Tips
5:04 Formatting related data as a table allows you to quickly analyze data.
5:13 When formatting data as a table, include only consistent rows of data. Do not include a row with totals; add it after the table is created.
5:25 All this and more can be found in Microsoft Excel 2016 In 90 Pages by Beth Brown. Available at Amazon.com.
Tables and much more are covered in Microsoft Excel 2016 In 90 Pages.
Belleyre Books 