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.
Category Archives: Excel 2007
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
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.
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.
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.
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.
Avoiding Excel’s Blank look
Using the ISBLANK function
When building formulas you sometimes need to identify a blank cell. Let’s say you use a VLOOKUP function. If the cell you are looking up is blank it can cause the #N/A error.
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.
Excel’s Print Options
Get printing right the first time
Printing in Excel can be a tricky exercise. Both Word and PowerPoint basically don’t let you print outside the page, but each Excel sheet has a huge grid with over 17 billion individual cells. Printing large spreadsheets can be a challenge.
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.
Handling Zeroes in Excel
Much Ado About Nothing
It’s amazing how passionate some people can be about zeroes. I have known people who hate, with a passion, to see zeroes displayed in their reports. They will sometimes use some formula acrobatics to avoid having a zero displayed.
Excel – Find and Replace for formats
Excel’s Find and Replace is quick and powerful. It’s great for replacing text. Did you know you can also use it to replace formatting? Well you can in Excel 2007 and Excel 2010.
Customizing Excel 2007 and 2010 [VIDEO]
Save time by using interface as it is was designed to be used
Excel 2007 introduced the new interface called the Ribbon. It’s a cross between a toolbar and a menu. It also has a Quick Access Toolbar (QAT) that many people don’t seem to be using.