Excel 2007

Do you prefer to use commas when you write long numbers?  Those commas are called “Thousands Separators”, and you can quickly add them to the cells in your Excel 2007 spreadsheet.

To turn on the thousands separator:

1) Select the cells you want to apply the commas to

2) Make sure you are on the “Home” tab of the ribbon

3) Look for the “Number” group – in the middle of the ribbon

4) Click on the button that looks like a comma!

‘till next time!
TNP

Have you ever wanted to quickly know what cells are impacted on when you change a value of a cell in Excel 2007.

By using the “Trace Dependents” feature, you can very quickly understand exactly the influence a cell has in your spreadsheet.  The best part of this feature is, that you will see big arrows that enable you to visually see the relationship, so you don’t have to decipher formulas and cell names to make sense of it all.

To turn on Trace Dependents:

1) Select the cell you want to see the dependents of

2) Make sure you are on the “Formulas” tab of the ribbon

3) Look for the “Formula Auditing” group (about 3/4 of the way along the ribbon)

4) Click on “Trace Dependents”

Now you will see arrows pointing you in the right direction!

‘till next time!
TNP

Have you ever wanted to quickly know what cells directly impact on the calculated value of a cell in Excel 2007.

By using the “Trace Precedents” feature, you can very quickly understand exactly which cells influence the output of a cell.  The best part of this feature is, that you will see big arrows that enable you to visually see the relationship, so you don’t have to decipher formulas and cell names to make sense of it all.

To turn on Trace Precedents:

1) Select the cell you want to see the precedents of

2) Make sure you are on the “Formulas” tab of the ribbon

3) Look for the “Formula Auditing” group (about 3/4 of the way along the ribbon)

4) Click on “Trace Precedents”

Now you will see arrows pointing you in the right direction!

‘till next time!
TNP

Do you want to stop people messing with your data, formulas, formatting, or all of the above in Excel 2007?  Well did you know you very quickly protect your spreadsheet from those rogue operators!

Here is how you do it:

1) Make sure you are currently looking at the sheet you want to protect

2) Click on the “Review” tab in the ribbon”

3) Look for the “Changes” group”

4) Click on “Protect Sheet”

A little “Protect Sheet” dialog box will appear that will give you a number of options, so you can be very granular in how much control you want people to have over the sheet.

For example, you can allow users to select cells, but not format them.  You can allow people to insert rows, but not insert columns.  And best of all you can add a password to the sheet to ensure only you can make any changes to what can and can’t be modified.

Now, you are really in control!

‘till next time!
TNP

Just like a header or footer in Word 2007, you can quickly add a header or footer to a workbook in Excel 2007.  You can add the date, time, filename or any other information you would like to make available to readers of your spreadsheet.

To create a header or footer in Excel 2007:

1) Make sure you are on the “Insert” tab of the ribbon

2) Look for the “Text” group – it will be on the right hand side of the ribbon

3) Click on “Header & Footer”

You will see a new tab appear on the ribbon called “Header & Footer Tools – Design”.  Using the buttons on this tab of the ribbon, you can quickly add:

• Page Number;
• Number of Pages;
• Current Date;
• Current Time;
• File Path;
• File Name;
• Sheet Name; or a
• Picture

To the header or footer of your document.  To switch from the header to the footer, simply click on “Go to Footer” which you will find in the “Navigation” group about half way along the ribbon.

‘till next time!
TNP

Are you building a spreadsheet and would like to know how many black cells you have in a given range?

There is a great function in Excel that you can use to do exactly that – count the number of BLANK cells in a range.

Simply type…

=COUNTBLANK(range)

(replace range with the range of cells you want to limit your count to). 

Note that there is one particular thing that might slip you up with this function.  When using =COUNTBLANK(), Excel is only searching for blank, empty cells.  If you have a space in a cell for example – it might look empty to you, but Excel can see that there is a space – which means it will not think it is blank, and not count it.

‘till next time!
TNP

Are you working with numbers in Excel 2007 that include decimal points? 

Did you know you can quickly increase or decrease the precision… or the number of digits to the right of the decimal point.

For example:

• Reduce the number of decimal point places in 56.923 to 56.9
• Increase the number of decimal point places in 23.4 to 23.4256

To change the number of decimal places your numbers have simply:

1) Select the cells you want to work with

2) On the Home tab of the Ribbon in Excel 2007, look for the “Number” group

3) Click on either “Increase Decimal”, or “Decrease Decimal”.  They are the buttons which have all the zero’s on them with the left and right arrows.

Simple as that!

‘till next time!
TNP

Hey everyone… welcome to October! (OMG almost Christmas!)

I am sure almost everyone who is reading this post has Excel installed on their computer at work or at university.  But how many of you really know what Excel is really capable of?

This month I am going to focus on Excel 2007 content.  One Excel 2007 tutorial each day for the entire month… 31 posts that will help you master Excel (and not the other way around!)

So are you ready to excel at Excel?  (sorry, had to do it!)  Stay tuned!

‘till next time!
TNP

If you follow @thenewpaperclip on Twitter (or @paulwoods – the guy behind The New Paperclip), you would have known that I was presenting at TechEd Australia the past week.

My session (with Microsoft Australia’s Andrew Lowson) was titled “Personal Productivity for the IT Pro” and was one of the level 100 sessions in the Office System track.

It was never meant to be a deep dive – simply a session to highlight some quick productivity wins you can get simply by getting to know the tools you already have in front of you – in this case, Microsoft Office.

The best part of the session was that about about half way through we handed it over to the audience and started crowd sourcing productivity tips.  It was obvious that there are some very smart, very passionate people out there – and plenty of knowledge was shared

For those of you who could not make it to TechEd, or are unable to access the session I delivered via your TechEd Online subscription, whilst on site at the Gold Coast Convention Centre I did record a TechTalk just for you.

Titled “The Productivity Myth”, this interview with Alistair Speirs from Microsoft Australia will give you a taste of what the session was all about.

If you want to read more about “The Productivity Myth” – check out this blog post. 

Looking forward to seeing you all at TechEd next year!

‘till next time!
TNP

Are you always having to resize your columns in Excel 2007? 

Wouldn’t it be great if you could reset the default column width so whenever you open a spreadsheet you didn’t have to resize those columns once you start filling them with data!

It is very simple to do…

1) In Excel 2007, on the “Home” tab look for the “Cells” group

2) Click on the “Format” button”

3) On the menu that appears Click on “Default Width…” (about the fifth one down)

4) Type in your preferred default column width and click “OK”

So there you have it – no more unnecessary column dragging!

‘till next time!
TNP