Excel doesn’t have a MINIF or a MAXIF function and many advanced users create an array formula to provide that functionality. If you have Excel 2010 or later there is a non-array solution.
In my latest free webinar I provided a brief demonstration of PowerPivot in Excel 2013. I forgot to show you how to enable it. Luckily its easy.
In my previous blog post I discussed generating random numbers in Excel. What about generating random text? E.g. generating random names for testing or training purposes.
Excel has had the RAND function for a long time. In Excel 2007 a new function was added. Called RANDBETWEEN it made it easier to create random numbers.
Excel 2013 Hide Filter Icon in a Table
This tip applies to Excel 2013 only.
If you have used the Format as Table option on a table you now have the ability to turn off the filter icons in the header row without losing the filter that has been applied.
The new Option is in the Table Style Option section of the DESIGN ribbon, see image below.
Slicers are a graphic filtering tool added in Excel 2010. They allow you to filter Pivot Tables. Excel 2013 added a new slicer that makes filtering by dates a lot easier.
In my training sessions I sometimes get asked about summing cells based on their colour. A SUMIF based on colour.
Hyperlinks are a great tool as they allow you to speed up and simplify navigation within a file. Sometimes hyperlinks can be frustrating. See how to remove some of those frustrations below.
The more you use macros the more important they become and the more you want to make sure the VBA* code doesn’t get changed by someone who shouldn’t change it. You may also want to stop people viewing your code.
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.
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.
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.
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.
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?
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.
The new FORMULATEXT function in Excel 2013 will make my Excel training job a little easier. It also has a formatting use.
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.
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.
Styles are an underrated feature in Excel. They provide an easy way to achieve consistent formatting throughout a workbook.