January had a slow start as everyone was on holiday, but the new year picked up quickly, and everyone came out wanting new spreadsheet systems.

Most encouraging this month has been the huge increase in total visitors up almost 40% on last month. Can we sustain this increase ?

Well... if we continue to offer good content, don't spam people, and bring Excel and VBA jobs to people who need them, of course we can !

Going forward, we are particularly interested in large complex projects. If you know of any, please refer ExcelExperts.com.

Excel Tips - Sort Columns (Excel 2007)

We all know how to sort rows, but did you also know that you can sort columns ?

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

And here's what happens when we sort columns:

Excel Tips - Camera Tool (Excel 2007)

• This is an excellent time saving tip to get screen shots from Excel into outlook
• Use Camera Tool

I have the Camera Tool added to my custom toolbar. See this tip for more info on how to customise your toolbar.

Once you have the camera tool on your toolbar:

What can't be done in Excel ?

When I ask customers why they're not using Excel for more things, they often say to me: "Well, we'd like to do XXXX, but you can't do that in Excel can you ?"

Often, the answer is: "YES YOU CAN !"

So my question to all of you, is this:  What do you think can't be done in Excel ?

Nick

Keyboard Shortcut To Open Excel

There's no actual pre-defined Keyboard Shortcut To Open Excel, but you can set your own one up.

Here's how:

Go:  Start => All Programs => Microsoft Office

Then Right click on the Excel entry, and select Properties

Excel VBA Jobs section

ExcelExperts.com is pleased to announce a new section for jobs.

Recruiters looking for Excel / VBA experts will be posting jobs here.

Excel / VBA Recruiters:

Cause Of Big Excel Files

We've all been there happily developing our spreadsheet, and all of a sudden, the file size balloons.

Q: How did that happen ??!!

A: Most likely, it's uneven formatting that's causing the problem

Turn Off Autofilter

Turning off autofilter using VBA is easy and quick, but you need to know how it's done.

Here's some code to toggle the autofilter on and off:

Happy new year !

This year should be an exciting one with ExcelExperts.com making a big push for business.

The first large scale system is in production at a canadian company. They are very pleased so far, and have requested 2 more systems to compliment their sales operation.

We have taken on board feedback that the site was confusing and cluttered, and have changed the layout to be more intuitive.

Convert Text To a Number using VBA

 - use: EVALUATE()

Here's a step through demo sub routine:

Hi all

This month has been very busy with ExcelExperts.com officially opening the doors to customers.

We have been swamped by requests for quotes, and it seems people realise that Excel is an increasingly popular business tool.

If you are a business using Excel, we guarantee that we can save you time and money with a little investment from you. Tell us your business processes, and we can advise on and implement changes.

We are looking for Excel Experts to join the team.

Do you have a large amount of data held in an Excel Spreadsheet ?

• If so, you will be well aware how difficult it is to find things

This tip demonstrates a way using VBA that enables you to search through your data really quickly, and helps you to find what you are looking for.

What I have written is a demo I used for searching through flight details.

Yep, that's right, ExcelExperts.com is looking for Excel Experts to get paid for Excel consultancy.

...but this is no ordinary interview.

To apply for this job, you need to prove the following:

1. You must be an Excel Expert, or be smart enough to learn quickly.
2. You need excellent communication skills. (Written more important than verbal)
3. You have to prove that you have the tenacity to see problems through to completion.

How do you prove this ?

ExcelExperts.com is excited to announce that our Excel VBA Consultancy service is open for business, and we have completed the first Excel VBA consultancy job.

The task involved analysing the valuation of a large development site by way of complex discounted cash flows prepared by property valuation experts.

The work included:

You've probably been wondering what I have been up to for the last few months given that there have not been many updates on the site.

Wellll... I've been looking after my new daughter, redecorating the entire house, and making photography books...

Here's the first one:

Create Hyperlinks

Download starting Sheet

So, we have added data, pivot tables, and charts. We're now going to create an index page.

Steps:

Create A Chart

Download starting Sheet

Now we are going to create charts of our pivot tables so that we can get a visual representation of all this data.

Steps:

Create A Pivot Table

Download starting Sheet

And now the lesson you've all been waiting for - How to create a pivot table !

A pivot table is an ingenious invention that allows you to slice and dice your data in as many ways as you can think of.

Inserting And Renaming Worksheets

Download starting Sheet

We have finished with the data entry sheet now, so lets create an index page. We now need to insert a worksheet.

Insert A Worksheet

Steps:

June 2009 Stats update from ExcelExperts.com

Find And Replace

Download starting Sheet

Suppose I've changed my mind and now prefer to use the term "Entertainment" instead of "Eating Out"

How do I do this ?

Easy..

VLOOKUP Formula

Download starting Sheet

Now, it has occurred to me that I can save time when entering future expenses because I have realised that I can pretty much categorise the expenses.

Filter Data

Download starting Sheet

Now you want to know how many of those expenses occurred in the last 1000 days.

Autofilter is perfect for this!

Steps:

Sort Data

Download starting Sheet

So you've entered a load of expenses, and now realise that you have entered them in the wrong order. It would be nice to sort them.

Steps:

Freeze Panes

This lesson is on how to freeze the panes. Freezing panes is about keeping the headings in view whilst being able to scroll down to enter data at the end.

To demonstrate why you want to do this:

Protection

When designing a spreadsheet, it is important to differentiate between areas to enter data, and areas that are calculating values based on that data.

Think:  Inputs    /    Outputs

As a general rule, cells containing formulae should be protected, and coloured differently, so lets do that here.

Copy And Paste Special Values

Download starting Sheet

We've just learnt how to enter a formula, so this lesson is on how to convert the formulae to values.

Steps:

Absolute References

Download starting Sheet

Now we want to enter a formula to calculate how many days ago we spent the money. 

Steps:

Insert A Column

Download starting Sheet

I now want to calculate how many days ago I spend the money, but I forgot to add a heading for that. I'll need to insert a column.

Steps:

Copy A Formula

Download starting Sheet

In the last lesson, we entered a formula. We now want to apply that formula to all the dates in column A.

There's a quick way to copy a formula that saves us from entering it again and again.

Steps: