Functions

Today's date formula I use =NOW() to update today's date in a spreadsheet. The problem is that it includes a fraction for the time of day. How can I get just the date without the associated fraction? By Neale Blackwood Excel treats dates as numbers, and the time of day is treated as a [...]

Using MAX and MIN instead of IF function I must calculate the time-and-a half and double-time hours from a total overtime figure - in column C. The first two hours of overtime are time-and-a-half, the rest is double-time. Can you provide IF functions to do the calculations? By Neale Blackwood Many of the questions [...]

Formula to stop Error messages I have a formula that returns an error message. Can I get it to display a blank cell instead? By Neale Blackwood Let's assume that cell A1 has some text in it and B1 has a number in it. A1 sometimes contains numbers and then we need to multiply [...]

Counting non-blank cells I have a range of IF functions that return "" in certain circumstances. I want to count the number of cells without "", but COUNTIF won't do it. Is there a way to count the non-blank cells in the range? By Neale Blackwood The SUMPRODUCT function can count non-blanks. Assuming the [...]

See All Menu Options Built-in feature By Neale Blackwood One of the more frustrating features of Excel 2003 is that the default setting for the menu display is set to "Show full menus after a short delay". With this setting Excel only displays the menu items that have been chosen in the past and [...]

Using Dates in Dynamic Headings & symbol and the TEXT function By Neale Blackwood Excel stores dates as numbers and when you try to use a date in a heading with the & symbol Excel will display the number, not the date. Hence, if cell A1 contained Sales For and A2 contained 1/1/08 then [...]

In cell Drop Down Data Validation By Neale Blackwood To create a drop down list in a cell you need to use Validation, which is in the Data menu in Excel 2003 and earlier versions. In Excel 2007 it is in the Data Ribbon in the Data Tools section under Data Validation. In the [...]

Data entry shortcut Built-in feature By Neale Blackwood If you have a defined area where you enter data within a spreadsheet, you can simplify the data entry process by selecting the range before you start to enter data. Once you have selected the range you can use the Enter and Tab keys after typing [...]

Zoom to a section of the screen Display tip By Neale Blackwood Large spreadsheets frequently have the zoom percentage set to below 100% to fit more information on the screen. This can make it hard to read some details. Excel allows you to zoom into a region of the sheet so that it fills [...]

Alternative to IF I use an IF function to compare two values to confirm they balance and put a 1 if they don't balance and a 0 if they do balance. Something like =IF(A1=B1,0,1). I then SUM the 1's to see how many don't balance. Is there an easier way? By Neale Blackwood In [...]

Paste Hyperlink Quick way to create a hyperlink By Neale Blackwood Hyperlinks (as on the internet) are a useful way to navigate around large spreadsheets. Using Ctrl + k to create a hyperlink take a few clicks to link to a specific cell in a specific sheet. It can be easier to copy the cell you want to [...]

Finding Numbers Finding numbers with a comma format By Neale Blackwood Excel has a bug in its Find routine that you may have experienced. If you have formatted formula using the comma (,) number format which displays 1000 as 1,000 then you might struggle to find numbers of 1000 and above. Some background to [...]