If you need to find the highest or lowest three entries in a filtered list you can use the AGGREGATE function to find them.
Category Archives: Excel 2010
Fixing too many different cell formats
A VBA solution or two
In a recent webinar I was asked about the “Too many different cell formats” error. This tends to be an error in Excel 2010 and earlier versions. In many cases this error is caused by having too many custom Styles.
Creating a Unique Dynamic List in Excel
A Power Query technique
There are a couple of techniques to automate a unique list of items in Excel. I have covered them in previous blog posts (see links below). I thought I would describe how to use Power Query to create a dynamic unique list.
Export a sheet as a PDF
It takes a few clicks but it is possible
I have previously posted about using CutePDF to create pdfs from Excel sheets. There is another way, but it takes a few clicks and it only works in Excel 2010 and later versions. (It may work in Excel 2007 but I have taken that version off my PC so I can’t test it.)
Show Horizontal Axis Entries Below the Chart
Great for displaying negatives in a line or column chart
I learned about a chart Axis option in Excel during a recent webinar – thanks to one of the attendees. You can show the Axis entries below the chart – this is handy for column charts that display negatives.
Finding the Last Used Cell in an Excel Column
Array free zone
If you need a formula to identify the last used cell in a column you don’t have to use an array formula. The AGGREGATE function can calculate it for you.
PowerPivot and Power Query
How to get and install
I have run two introductory webinars in 2016 on these two topics. You may need to download them and install them before the webinars so you can use them during the webinars. The Add-ins are free from Microsoft.
Day Of The Week
A quick and easy way to find out the day of the week for a specific date is to use the Long Date format from the drop down in the middle of the Home ribbon (in the Number section) – see below.
Flexible Hyperlinks in Excel
Using the HYPERLINK Function
Hyperlinks are a great way to navigate around large spreadsheets. Unfortunately they each take a few clicks to create and can be easily broken. You can use a function to easily create multiple, flexible hyperlinks.
Clearing the tab colour
I use colours on my sheet tabs to signify different things.
To clear a colour, you can use the following keyboard shortcut
Alt h o t n
Pressed in sequence, not held down.
Related Posts
Sequential Numbers With a Twist
How to ignore hidden rows
You want a sequential number in a column. The challenge is, it must display as sequential even if rows are hidden or filtered. Is this possible?
Japanese Yen Format in Excel
Applying it quickly
To apply the Japanese Yen format can take quite a few mouse clicks.
The macro that does it, on the other hand, is quite simple. Select the range, then run the macro.
Sub JapaneseYen() Selection.NumberFormat = "[$¥-411]#,##0.00" End Sub
If you are unsure how to use macros, see the link below.
Adding years or months to a date in Excel
Finding the end date can be easy
When working with loans or leases, it is common to have to add a number of years to a start date to determine the end date. An Excel function can automate that process.
Paste Values
After copying, use the following keyboard combination to paste just the values – no formulas or formats.
Alt h v v
These keys are pressed in sequence, not held down.
Working with Imported Dates and Times in Excel
Date data imported from other systems can include times. This can make lookup and other calculations difficult. One function can make removing or extracting time easy.
How to stop overwriting files in Excel
Open a copy instead
Is this you? You open the May file and make the changes for June and save it and then remember you hadn’t renamed the file as June. Well, I’ve done that too.
Excel – How To Create a MINIF and a MAXIF
Without an array
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.
Enabling PowerPivot
XL2013 and XL2010
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.
Generating Random Text Entries in Excel
Create random first and last name combinations
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.
Two Random Functions in Excel
They both create random numbers
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.
Summing Coloured Cells in Excel
A SUBTOTAL trick worth learning
In my training sessions I sometimes get asked about summing cells based on their colour. A SUMIF based on colour.
Excel and Hyperlinks
Sometimes you want them, sometimes you don't
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.
Protecting Your Excel VBA Macro Code
Applying a password to VBA
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.
Excel Top 10
Making Pareto analysis easy
Excel has two options that relate to top and bottom scores. These are usually shown as Top 10 or Bottom 10 options. In both cases they are much more flexible than that.
Techniques to Paste Values in Excel
Lose the formulas and keep the values
Sometimes in Excel you need to paste just the values from copied cells. You don’t want the formulas. You may have created temporary formulas that you need to replace with their values. You may need to capture the current values, make some changes and then compare the new values with their old values to see the difference.
Excel Format as Table Part 2 [VIDEO]
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.
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.
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.