mthomas.co.uk - IT Tips, Tricks & Tutorials

But having to use Windows (at work anyway) gives me a good excuse

Today I was adding some new functionality to an Excel file. The file is stored on our network and when I came to save it, I got a message box saying “File not saved”. We had earlier experienced a network connectivity outage so, thinking that this was the problem, I tried to save it to the local C: drive - same problem.

I couldn’t simply copy and paste into a new file for several reasons - the file contains over 20 sheets, a large number of “Range Names” and several complex VBA procedures.

And the last straw came when I read the Microsoft Knowledgebase - Restart Windows in Safe Mode (which of course means I lose the changes to the file) and then try to save the Excel file to your local hard-disk drive.

Agggghhhh!!

Absolute cell references allow you to “fix” part or all of a cell reference in a formula. This can be a great time-saver when copying formulas, however, there is more to it than meets the eye… Read More

Using a combination of the ROW and MOD functions and Conditional Formatting, you can easily shade alternative rows in Excel, making a list of data easier to read. Read More

The MOD function in Excel returns the remainder after a number is divided by a divisor.

As an example, start a new spreadsheet and enter 172 into A1. This represents the number of minutes that a piece of machinery has been running (this figure is manually entered after the user has noted the value by reading the machine’s “Activity Log” which outputs a value in minutes).

Enter a formula in A2 to calculate the number of whole hours:

=INT(A1/60)

As we only want to display whole hours, the INT function is used to return the Integer part of the result (172 / 60 = 2.866667).

The formula in A3 which calculates the number of remaining minutes is:

=MOD(A1,60)

In other words, 172 divided by 60 is 2 remainder 52

I was recently presented with an Excel file which displayed a message every time it was opened indicating that it contained links to other files. I was assured by the owner of this spreadsheet that all links had been removed.

Thanks to officearticles.com for helping me to solve the problem:

• Open the file with the problematic link in (MyFile.xls in this example).
• Save the file to a new name (like MyFile1.xls).
• Select Edit >Links and choose to link it to MyFile.xls.
• Save it and close.
• Delete MyFile.xls and rename MyFile1.xls to become MyFile.xls again.

Of course if you have Excel 2003 or later you can just select Edit > Links and there is a Break Link button in the dialog box!

In Word, selecting some text and pressing Shift+F3 will change the case of the selected text. It works on a cycle through the following - lower case , CAPITALS or Sentence Case.

Excel does not have this feature but you can select a cell and use =UPPER, =LOWER or =PROPER.

The format is =FUNCTION (CELL) where FUNCTION is the required function (upper, lower or proper) and CELL is the cell that contains the text.

For example, if A1 contains MIKE THOMAS, and in B1 you enter =PROPER(A1), B1 will contain Mike Thomas.

You can then use Copy and Paste Special / Values to copy the contents of B1 into A1, thus overwriting the original text.

I’ve used this feature many times for clients who have been given a list of, for example, names, that have been pulled off another system (say a payroll system) where the original data has been stored as capital letters.