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.
Adding Up Values Only in Excel
Here is the problem – you have a list of values which includes sub-totals calculated using SUM functions. You only want add up the values and ignore the SUM function cells. (more…)
Show Sheet Tabs
If you find the sheet tabs are missing at the bottom of the screen, you can fix the issue with the setting below. To access this screen press, in sequence (not held down) Alt t o a and scroll down about half way.
Sequential Numbers With a Twist
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? (more…)
Clearing Filters
The keyboard shortcut to clear all filters is pretty straight forward Alt a c Keys are pressed in sequence, not held down.
Japanese Yen Format in Excel
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
Getting the Week Number From a Date in Excel
Excel has had a week number function for many versions, but Excel 2013 added a new week number function that complies with the ISO 8601 standard week number. (more…)
Adding years or months to a date in Excel
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. (more…)
Its a FACT Jack
Do you remember factorials in maths? That’s where you have, say four numbers, and you want to find out how many four number combinations you can make with those four numbers. In Maths you write it as 4!. The answer is 4 x 3 x 2 x 1 = 24. (more…)
Confirm Two Lists Match in Excel
If you have two lists of numbers and you need to ensure they are identical there is a simple formula that can confirm they match. (more…)
Summing every second row
Let’s say you have a list of values that has a set sequence. In the list which starts in row 2 you want to add up every second entry. So you want to add up the entries in row 3, row 5, row 7 etc going down the sheet. The SUMPRODUCT function to the rescue yet again. (more…)
Ordinals For Dates
If you want to place ordinals (st, nd, rd and th) after a number in a date. You can use the CHOOSE function to do this. (more…)
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.
Random Dates
In Excel dates are stored as numbers. To generate random dates you can use the RANDBETWEEN function with a start date and an end date. The formula in cell C2 is =RANDBETWEEN(A2,B2) Cell C2 has been formatted as a date. This formula is dynamic. Each time Excel calculates it will update and most likely change. You can use Copy > Paste Values to capture the date(s).
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. (more…)
Counting Errors in a Range in Excel
If you need to ensure that a range has no errors you can count the errors and compare the result to zero to ensure the range is error free. The formula in cell C1 which counts errors in a range is =SUMPRODUCT(ISERROR(A1:A5)*1) If you want to display TRUE for no errors and FALSE for error(s) you can use =SUMPRODUCT(ISERROR(A1:A5)*1)=0 You can also use an IF function to display the text Error if any errors are found. =IF(SUMPRODUCT(ISERROR(A1:A5)*1)=0,"OK","Error")
Using a Number as Text in Excel Made Easy
When using numbers in text strings you usually need to format them. You typically need to use the comma format and handle decimals. There is a function that can simplify this task. (more…)
Removing Line Feeds in Excel
Sometimes when text data is imported from other systems it can contain line feeds. A line feed wraps the text onto a new line - it is not associated with the Wrap Text format. Cell A1 below shows an example. Cell B1 has the formula that removes line feeds and replaces it with a space. The formula in B1 is =SUBSTITUTE(A1,CHAR(10)," ")
Controlling the Enter Key
When you press the Enter key Excel typically selects the cell beneath the current cell. Some people change this default option so that the selected cell does not move at all when Enter is pressed. When you think about it, you often press Enter and then immediately move back to the cell to either copy it, or format it. If you want to change what the Enter does when you press it, you can change a setting in Excel Options.…
Easy Protection
Applying sheet protection stops your sheet structure from being accidentally changed by you, or someone else. You can add sheet protection by right clicking the sheet tab and clicking Protect Sheet or using the Protect Sheet option in the Review tab. You typically use a password when applying protection, but you can also leave the password box blank. This still applies sheet protection and stops accidental changes, but allows you to easily unprotect the sheet; make a change and then…
Getting Excel to Recognise an Email Address
Sometimes when Excel imports email addresses they are not recognised as emails and are not hyperlinks. They are two ways to fix this.
(more…)Backup your important files
Excel has a feature that has been around for many versions, but most users are unaware of it as it is tucked away in a separate menu and dialog. You can have Excel automatically make a backup of the previous version of a file each time you save it. Note: if you click save twice in a row the backup will be identical to the current version. This can be a life saver if your file gets corrupted. In the…
How to stop overwriting files in Excel
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. (more…)
Excel – How To Create a MINIF and a MAXIF
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. (more…)
Enabling PowerPivot
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. (more…)
Data Validation to Ensure a Weekend Date
If you want to ensure the user enters a date that is a Saturday or a Sunday, you can use a Custom Data Validation. This example is for cell A1. To open the Data Validation dialog use Alt a v v (pressed in sequence, not held down). Click the first drop down and select Custom. This allows you to enter a formula to determine the data validation. The formula used is =WEEKDAY(A1,2)>5 The WEEKDAY function returns a number from 1…
Pasting Data to the Bottom of a Formatted Table
Formatted tables are a great feature in Excel. They were expanded and renamed in Excel 2007 (formerly called Lists) and their use offers many advantages. (more…)
Running an Excel Macro from a Graphic
These days running a macro off a control button seems to be old school and many people have started running macros off graphics. (more…)
VBA AutoComplete
When you are typing code in the VBA code window you can press Ctrl + Space Bar to have Excel finish the word for you. Eg type appli And then press Ctrl + Space Bar to have Excel finish the word Application.
Excel VBA Command to Clear the Clipboard
When using copy and paste in a macro it is a good idea to clear the clipboard at the end of the macro. If you don’t, the user could use paste to paste the last thing you had copied in the macro.
(more…)