Excel - Time Calculation

What if you want to calculate a time difference in hours as a real value? I have seen some rather complex solutions to this, but a simple formula is all that is necessary.

If cells cell A1 and B1 contain the earlier and later times respectively, with the cells formatted as time, the difference between them […]

Excel: Select all cells

Want to select every cell in an Excel spreadsheet?

Click the rectangle at the top, where the row numbers and column labels meet.

This selects all the cells in the sheet.

Excel: Dropdown list in cells

You can set up ca cell (or of course a range of cells) in excel so that when you click on the cell, it offers a dropdown list of items to select from.

The best way is as follows:

Create the list itself by typing into a range of cells, usually in a different sheet, then give […]

Excel: Copy cell above

If you do a lot of this, learn this keyboard shortcut it could save your clicking finger a bit
Ctrl ,

That is, hold down the control key and hit the comma key. Useful?

Excel: Sum above cells

There is a button to enter a formula giving the sum of the cells above, it looks like this

But smart computer users use keyboard shortcuts because it’s quicker (I am still learning to do this though!) The shortcut for this is:
Alt  +

That is, hold the Alt key down and press the ‘+’ key.

Excel: Creating custom buttons

If you want to create a custom button in Excel (or indeed any of the Microsoft Office applications)  versions 2003 and below, you can do this manually, but you are offered a dismal range of options when you click the ‘Change Button’ option.

Using a VBA macro, you can  use any of the images available in […]

Excel: Keeping titles visible

If you have a list in Excel with titles at the top, and the list is longer than can be shown on the screen, scrolling down can be a problem as you can no longer see the titles. To get round this, you can freeze the first row so you can always see the titles. […]

Excel 2003 menu in Excel 2007

So many of us have avoided upgrading Office 2003, partly  because of the completely different ribbon menu systems. Ubit offers an Excel add-in to allow you to display the classic 2003 menus.

Moving to Office 2007/2010 creates a problem with custom menus and toolbars developed in 2003. Although they appear in the ‘Add-in menu’,your custom options […]

Excel - Enter today's date

Do you get fed up with typing today’s date into Excel? Try this:
Ctrl  ;

Hold the Ctrl key down and hit the “;” key


Format a cell in Excel

To do this, use menu option Format > Conditional Formatting

For example in Excel 2003, set the cell to red if it contains the work ‘Aardvaark’….

Select the cell
Choose Format > Conditional Formatting
Select Cell Value Is : equal to: “Aardvaark”
Click Format then the Patterns Tab and choose Red.

You can see how you can do slightly more useful […]