Nick's blog

95. Excel Tips - Insert Alternate Blank Lines

Here's a tip on how to insert alternate blank lines in about 20 seconds.

We have this:

And we want this:

94. Excel Tips - Sheet Navigation With Lots Of Sheets

Navigating around your sheets can be very fiddly when you have a lot of sheets, but this little tip simplifies things massively

Simply right click on the sheet navigation arrows at the bottom left of your worksheet. (Bottom left of screen shot below)

You'll get this:

93. Excel Tips - Copy Charts To Powerpoint

This tip is about the second stage of creating a model in Excel:

• Getting it into a powerpoint presentation.

Here's a screen shot of our data in Excel:

We can copy it to powerpoint to look like this:

26-May-2009 NEW UTILITY: Compare 2 Data Sets

Compare 2 Data Sets

• Have you ever spent hours reconciling 2 sets of data ?
• Is it your job to reconcile data ?

If yes, then this utility goes out to you. I have done that job, and it's really hard work.... This will get you home early.

You can use this utility for:

26-May-2009 Free Property Calculators

ExcelExperts.com is pleased to launch a variety of of Free Property Calculators designed to answer your important property decisions:

Excel 2007 vs Excel 2003 ?

I have now been using 2007 for a while, so here are my observations on 2007 vs 2003.

22-May-2009 NEW UTILITY: Merge 2 Data Sets

This ulility can save you an enormous amount of time if you have a task involving merging 2 data sets.

If you work in Data or Operations, this could be the file for you !

22-May-2009 NEW UTILITY: Analyse A Workbook

This utility is aimed at working out how complex a spreadsheet is

• Simply enter the full file path of the file, press the button, and you will be provided with an analysis of your file.

You'll get:

29. VBA Tips - Find Out If A Cell Is Within A Range

This tip is about finding out whether a cell is within a range.

• It's basic set theory... we use VBA's INTERSECT

What we want to write is some VBA code that tells us whether we have selected a cell within the data table:

28. VBA Tips - Selecting Things Demo

In this VBA tip, we'll learn how to select various things on a worksheet:

27. VBA Tips - Inputbox

A way to ask a user of your system a question is to use an Inputbox:

Here's what we're trying to achieve:

Ask the question:

Display the answer:

Here's the code:

26. VBA Tips - Call Code From Another Workbook

In this VBA tip, we'll learn how to call code from another workbook:

• The way to do this is to use Application.Run

Here's the code:

To replicate:

25. VBA Tips - Email Workbook

There's an easy way to email a workbook with a single line of code:

• Use "Activeworkook.SendMail"

Here's the code:

Here's the email it produces:

24. VBA Tips - Create An Index Page

This tip shows you how to create an Index page with hyperlinks to each sheet:

Here's a screen shot of what we will produce:

Here's the code we use:

92. Excel Tips - Save As Web Page

Don't go writing code to convert Excel sheets to HTML:

• Use Save As Web Page instead.
• It's quick, easy, and does a lot of work for you.
• If you have an intranet at work, and want to use it to share information without worrying about sharing workbooks, this is a good way.

Here's a screen shot of our data in Excel:

91. Excel Tips - Display All Range Names

Managing Range names can be fiddly, but this tip makes it a lot easier:

ExcelExperts.com is pleased to formally launch the Excel Experts Question and Answer Forum

Why should you use this forum ?

15-May-2009 NEW UTILITY: List Files In Folder

This utility will list all the files in a folder and subfolders.

23. VBA Tips - Double Click

Here's a VBA tip on how to capture the Double Click event and do something useful with it.

22. VBA Tips - Pause Code

This VBA tip is about how to pause code.

• There are many reasons why you might want to pause code:
• You might be waiting for a file to arrive and need to pause before rechecking
• Maybe you've launched a batch file and it needs 3 seconds to run before you can continue

This is the code we'll use for this tip:

21. VBA Tips - List Files In A Folder

This VBA tip will demonstrate how to list all the files in a folder.

20. VBA Tips - Import A Text File

A lot of VBA code is written to import files into spreadsheets.

• Today, we'll look at how to import a text file

Here's our text file, and we want to import it onto sheet1 of our spreadsheet starting at Range B7.

From this:

To this:

90. Excel Tips - Replace The * Character

The * character needs special attention if you want to replace it in your spreadsheet

• Replacing * with something will replace everything (not just the * character)

Here's a screen shot of our data in Excel:

19. VBA Tips - Log An Audit Trail

Logging an audit trail of your changes can be useful, and can be done with a small amount of VBA...

Here's our data:

Here's a screen shot of what we're trying to achieve:

18. VBA Tips - Write Your Own Excel Function

Writing your own Excel function is remarkably easy as this tip will show you.

17. VBA Tips - Save A Copy Of A File Daily

Often during your working environment, you will need to track the history of a spreadsheet.

• This tip will teach you how to seemlessly create a daily backup of your file
• The file will be date stamped so that you can sort by date and easily find a file you're looking for when you need

Here's a screen shot of our VBA code:

16. VBA Tips - Get A File Path

Using VBA, there is a quick and easy way to obtain the file path of a file and return it to a sheet

• Use GetOpenFileName
• This brings up a dialog box that allows you to browse your files and choose a file
• You can then use that file path how you want

Here's a screen shot of our setup in Excel:

15. VBA Tips - Track Changes By Adding A Time Stamp

Using VBA Events, there's a clever way to add a time stamp when cells change so that you don't need to remember whether you have updated it or not:

89. Excel Tips - Multiply Formulas By 100

Most people know that you can multiply numbers by 100 using paste special, but not many people know that you can use the same methodology to multiply (or divide) formulas by 100 (or any number) as well

Here's what we have: number2 =  number1 +3

88. Excel Tips - Align Shapes

This Excel Tip is about how to align shapes:

• Suppose you have added 3 buttons and they're out of line.
• How can you align them ?

Here's a screen shot of what we have, and what we want to have:

Here's how to do it