The Format As Table feature has many useful features that are worth taking advantage of. The previous post listed them. The video of this blog is shown at the bottom of the post.
Category Archives: Excel 2013
Format As Table in Excel – Part 1
Tables rule - see why
Excel 2007 updated a little used feature of Excel 2003 called Lists to create the functionality behind the Format As Table icon on the Home Ribbon tab.
Excel – Last Used Row In A Column
SUMPRODUCT to the rescue again
Sometimes you need to identify the last used cell in a column. The versatile SUMPRODUCT function can calculate that using a couple of other functions.
Filtering Tables in Excel 2013 with Slicers
Sometimes when you apply a filter to a table it isn’t obvious what filter has been applied. This is the case when you are filtering by multiple entries in a field.
Slicers were introduced in Excel 2010 to filter Pivot Tables. Filtering a Pivot Table has the same issue when filtering by multiple criteria in a single field. Click here to see a post on these Slicers.
Slicers’ abilities were expanded in Excel 2013 to include filtering formatted tables. (A formatted table is one created using the Format as Table icon on the Home Ribbon tab or the Table icon on the Insert Ribbon tab).
The advantage with using a Slicer is that it is visual. It shows the filter being applied. A normal filter is hidden once selected.
The disadvantage with Slicers is the filters are limited to selecting or deselecting entries in the list. There is not the flexibility provided by Excel‘s normal filtering options.
Adding a Slicer
In the Design Tab when a formatted table is selected there is an Insert Slicer icon.
A dialog displays allowing you to select fields to add Slicers.
To select multiple items hold the Ctrl or Shift keys whilst clicking the entries.
As you can see the Slicer shows the states being filtered.
To clear the filter click the icon on the top right of the Slicer.
Counting Characters in Excel
Another useful SUMPRODUCT technique
If you need to know how many characters are in a cell then use the LEN function. What if you wanted to know how many characters were in a range?
Excel Templates – Why and How to Use Them
Save time and effort
Templates allow you to create blank sheets and blank workbooks that have customised formats as well as customised Page Setup settings, including headers and footers.
FORMULATEXT Function in Excel 2013
New function - great for training
The new FORMULATEXT function in Excel 2013 will make my Excel training job a little easier. It also has a formatting use.
Taking Control of Excel shortcuts- Part One
Save time with shortcuts
Keyboard shortcuts can really speed up your work in Excel. Here are some of my favourites that use the Ctrl key. I’ll share some more in later blog posts.
Quick as a Flash with Excel 2013
A new feature that's not perfect yet
Excel’s brand new Flash Fill feature can be a time saver, but it’s not fool proof. Flash Fill is a new feature in Excel 2013.
Formatting with Style in Excel
Consistent formats made easy
Styles are an underrated feature in Excel. They provide an easy way to achieve consistent formatting throughout a workbook.
Macro to save all open Excel files
Time saver
If you have a number of linked Excel files and you make a structural change (insert rows or columns) you need to save all the open files to ensure that the links are updated and retained. This can be tedious as there is no Save All option in Excel. Word has a Save All option, but not Excel.
Quickly and easily hiding and unhiding rows and columns in Excel
Excel's Grouping technique
Hiding rows and columns is a common process in spreadsheets. Excel’s grouping feature can make the process easier and make it more apparent when rows or columns have been hidden.
Handling graphics in Excel
Make the most of graphics
If you need to move or amend a lot of graphics at once Excel has a feature that can save you time and effort. It’s been around for a while, but it’s been hidden away in the latest few versions.
Slice and dice in Excel
Excel 2010 has Slicers
Excel 2010 introduced a new filter interface for Pivot Tables called Slicers. This feature improved the filtering process for Pivot Tables and made filtered selections more visible.
Excel 2013 – first impressions
New features
I installed Excel 2013 last week and have had a bit of play.
Note: you need Windows 7 or later to run Excel 2013.