Why Should I Use Excel ?

If you've found this page, you're probably asking: Why should I use Excel ?... I've managed my whole life without it.. What is this Excel thing ?

Well, here's my list of top reasons why you might want to learn how to use it:

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

We have this:

And we want this:

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:

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:

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:

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.

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 !

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:

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:

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

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:

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:

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:

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:

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:

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 ?

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

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

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:

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

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:

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:

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:

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

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:

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:

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:

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