Nick's blog

Nick's picture

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:

insert-alternate-blank-lines

And we want this:

Nick's picture

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:

Nick's picture

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:

copy-charts-to-powerpoint

We can copy it to powerpoint to look like this:

Nick's picture

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:

Nick's picture

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:

Nick's picture

25-May-2009 Excel 2007 vs Excel 2003


Excel 2007 vs Excel 2003 ?

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

 

Nick's picture

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 !

Nick's picture

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:

Nick's picture

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:

Nick's picture

28. VBA Tips - Selecting Things Demo


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

Nick's picture

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:

inputbox

Display the answer:

inputbox

Here's the code:

Nick's picture

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:

call-code-from-another-workbook

To replicate:

Nick's picture

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:

email-workbook

Here's the email it produces:

email-workbook

Nick's picture

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:

create-an-index-page

 

Here's the code we use:

Nick's picture

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:

Nick's picture

91. Excel Tips - Display All Range Names


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

Nick's picture

18-May-2009 Launching Excel Experts Question and Answer Forum


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

 

Why should you use this forum ?

Nick's picture

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


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

Nick's picture

23. VBA Tips - Double Click


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

Nick's picture

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:

Nick's picture

21. VBA Tips - List Files In A Folder


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

Nick's picture

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:

import-a-text-file

To this:

Nick's picture

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:

replace-the-*-character

Nick's picture

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:

log-an-audit-trail

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

Nick's picture

18. VBA Tips - Write Your Own Excel Function


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

Nick's picture

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:

Nick's picture

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:

Nick's picture

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:

Nick's picture

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

multiply-formulas-by-100

Nick's picture

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:

align-shapes

Here's how to do it

Syndicate content