Most people are unaware that the SUMIFS function has a serious limitation when it comes to codes with leading zeroes. This post shows you how to perform calculations involving codes with leading zeroes. This issue also affects SUMIF, COUNTIF and COUNTIFS.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.