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. (more…)
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. How to use macros
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. (more…)
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. (more…)
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. (more…)
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. (more…)
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?
(more…)
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. (more…)
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. (more…)
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.
(more…)
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.
(more…)
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. (more…)