Have you altered a list by sorting it by an adjacent column which was not part of your data set? This shocking-Autofilter-incident modifies your data forever (impossible to undo after saving).
There is a solution to this problem: using a helper column with the original order; if something goes wrong you set data back. Now, you can focus on your data instead of those workarounds.
Forget those old uncomfortable times with new Excel 2007 List (Excel Table) capabilities…
1) Manage Data Regions Consistently
- Field Headers or subtotal rows included as data when you Filter or Sort
- Inclusion of adjacent data that is not part of the data set (see graphic 1)
- Exclusion of adjacent data that is part of the data set (see graphic 1)
- Exclusion of data beyond 1000th row in the Filter drop-down list
Your confidence will increase in sorting/filtering because now you have a kind of MS Access table inside your Excel Spreadsheet.
No more worries about:
2) Handle your Data more Intuitively because Filter and Sort Are Now Together in Excel 2007 List
- You usually apply a filter criteria, then sort by any field or
- Sort first, visualize data and then filter
- Apply two filter criteria and then sort by any field
Sort and Filter had been unconnected in old versions but they are interrelated features. For example:
Now you have them together.
3) Stay Focused on Your Data instead of Opening Filter Dialogs
Just place the mouse over the filter icon and get a message to know what kind of filter you have applied. This keeps your momentum in your analysis of data. See below:
4) Filter/Sort by Cell Color and by Font Color
- When the format is the result of a complex criteria via Conditional Format command or
- When the format is set by yourself with your own color codes
- This feature is activated when at least one cell contains a fill/font color
- Use color keys to make users know the meaning of cell colors, for example: Red = pending, Green = done, etc
5) Spend Less Time Setting Common-Filter-Criteria with New Predefined Filters
- Text filters: Contains, begins with, etc
- Number filters: Equals, greater than, above average, etc
- Dates filters: Tomorrow, today, this week, next quarter, next year, etc
Filter quicker than ever; now you have new preset filters (date filters specially). Some classical filter criteria were just repurposed for easier access. See graphic 5…
- Sort A to Z
- Text filters: equals, begins with, etc.
- Sort from smallest to largest
- Number Filters: Equals, Less than, Top 10, etc.
- Sort oldest to newest
- Date Filters: before, after, today, next month, etc
What does this mean?
For column containing texts in an Excel 2007 List you will be presented with a Filter/Sort dialog accordingly (see graphic 5):
Well, for column containing numbers you will be presented with a Filter/Sort dialog for that reason (see graphic 4):
For column containing dates you will be presented with a Filter/Sort dialog consequently (see graphic 4):
7) Apply Format Quickly and Easily with Table styles
Apply formatting to table in one click; predefined table styles are available or you can create your owns.
How do you do it? Place inside the table and then go to: Design>Table styles
8) Access new/more Options with New Right Click Menu
- Reapply filter (refresh filter)
- Filter by selection (by cell’s value, color, icon)
- Custom sort
- Select table row/column
You dispose of more and new options in the contextual menu. Just right click and choose the command (see graphic 6).
The most useful options you may find are:
- Go to Insert>Table or
- Press CTRL + T
Excel 2007 converts a data region into a new table quickly. Just put inside it and do one of the following things:
10) Avoid Clustering of Toolbars with Contextual Excel Design Ribbon
One problem with custom toolbars is that they remain visible after you need them. New contextual ribbons are available just when you require them.
How is it activated? Each time you are inside the table, you dispose of a ribbon with the options you most need and use. The Ribbon is called Design.
11) Quick Transition from Excel 2007 List (Excel Table) To Pivot Tables
Once you gain confidence in working with Excel Tables your move to Pivot Table is a step away. This option is now integrated.
In Ribbons go to: Design>Tools>Summarize with Pivot Table.
12) Avoid Copying and Pasting Formulas Up/Down in an Excel 2007 List (Excel Table) with Automatic Formula Copying
Wherever you insert/modify a formula in any field; Excel 2007 do the dirty job for you, it fills down and up the formula as necessary.
You keep free of manual work and from error in manipulating formulas.
13) Write Formulas Quicker with New Structured References
Avoid picking entire column as arguments in formula. By selecting a cell you are selecting the entire range, for example:
The formula =F8/D8 was typed in G8 but the New Excel Table uses structured reference to create a unique formula for the entire field, see below:
=Table6[[#This Row],[Order Amount]]/Table6[[#This Row],[Number of items]]
The formula is then copied to the entire field as said in point 12.
14) Keep Oriented with Fields After Vertical Scrolling
Nothing is more annoying than working on an unknown field and freezing top row as you work. Now field names keep on top of your Excel 2007 List(see graphic 8).
15) Forget Cells Excluded in your Subtotal Formula after Inserting Rows in your Excel 2007 List (Excel Table)
Keep your subtotal formula consistent all the time with the new Table Row tool. The subtotal formula will include all the rows of the field even after inserting rows.
You won’t need to use right-corner handle to resize the referenced range of the formula.
Activate Total Row by going to: Design>Table style options>Total row.
Important notice: Excel 2003 Autofilter considered an adjacent row with subtotals as part of the data and included it in the filter/sort process.
16) Delete Duplicates Intuitively
This is a most-wanted feature. Now it is integrated and you can choose the field on which you will apply the command.Go to: Design>Tools>Remove duplicates. See graphic 10
17) Avoid Retyping Data by Using Pick From Drop-Down List (even between Blanks)
Consistently pick already-entered-values even when there are spaces between them.
18) Don’t Lose Data after Loading Bigger Data Sets
Load bigger files. Now you have 1,048,576 rows by 16,384 columns.
It is true that Excel 2007 brings new features that will boost your productivity. It is also true that fundamentals remain the same.I think that the best way to get the most of the software is by learning the building blocks that doesn’t change version after version: cell referencing, formula writing and nesting, filtering data, etc.