Magic in Excel 2010 Tables : Filters on Tap
Free Microsoft Office Tutorials
Excel 2010 Tables: Working with data effectively in Microsoft Excel 2010 has never been easier. Excel has been designed to manage lists of data. In earlier versions of Excel we became familiar with the term data lists. In Microsoft Excel 2007 this feature was expanded and we now refer to data lists as Tables. This feature was carried over to Excel 2010. Lets look at how easy it is to create tables and filters.
Watch this short video tutorial about Tables and Filters
How do we create Excel 2010 tables?
Make sure your data is without blank rows and columns and has headers in adjacent columns.
Click any header or data cell, on the Home tab in the Styles group, click Format as Table. A dialog box will appear, make sure the range in the box reflects the range for your Table and that the Table has Header box is ticked. Click OK. Tables and filters are applied automatically.
Replacing exiting formatting
If you want Excel to replace the existing formatting with the Excel table’s formatting, select the range then right-click the table style you want to apply and then click Apply and Clear Formatting.
Adding new data
To add data to the table click the bottom right cell the bottom row of the data array then press the Tab key and new row will be added to the Table.
You can add a totals row at the bottom .Click in the table data; select the Table Tools Design tab then in the Table Style Options tick the Total Row box.
Convert a table to a range
You can convert the table back to a range and keep the formatting by clicking in the table data then selecting the Table Tools Design tab, in the Tools sections click Convert to Range
How do you rename the Table?
Click in the table data; select the Table Tools Design tab then in the Properties under Table Name: add a name that is appropriate to the project.
Working with the filters
Let’s spend a moment and focus on filtering data. Excel allows you to manage huge data sets with over 1million rows. This presents a problem if we cannot quickly filter data to find what we need. Excel offers a number of powerful and flexible tools with which you can limit the data displayed in your worksheet.
When filtering is applied Excel treats the cells in the relevant column as a range. It is assumed that the first row is a header row.
Let’s look at some of the options.
When you click the filter arrow you are given a list of unique values in the column range. Filter options change depending on when the range is text or a number or if the range has colour. Yes! You can filter by colour.
The new search filter
A new feature with Excel 2010 is the ability to filter with search. As you type the criteria into the Search box the range is dynamically filtered in the list directly underneath. Click OK to finalise your selection.
Filter by color
When a filter is applied the Filter Icon changes colour to let you identify that the column contains a filter that is active.
You can add filter to numerous rows.
Custom filters can be applied to the column or range. Here we have filtered with the custom filter to show only data that equals “Delete” And equals “Apply”