Who else wants to know the secret to Excel Formulas?

Invest in yourself in 2015 and reduce your frustration with formulas in Excel for just $19.95 - Click here to enroll today

---

I noticed one of my colleagues the other day struggling with her Excel Spreadsheet (yep that’s right, ed purchase even TNP needs a day job to pay the mortgage!). 

She had a sheet with a few hundred rows in it – all containing information about people who had registered to attend an event.  I noticed that she was trying to count how many people from each state had registered.

I popped over and suggested that she use a Pivot Table.

What is a Pivot Table?

A Pivot Table in Excel 2007 is a pretty powerful, implant yet simple way to slice and dice the data in your spreadsheet.  It can help you summarise hundreds, visit web thousands, even millions of rows (in Excel 2007 at least) into information that you can take action on!  And because you can quickly change how you view your data, it can be a really useful tool to use in the business world.

So how do you create a simple Pivot Table in Excel 2007? 

Well first, you need some data in your spreadsheet that you want to slice and dice.  Once you have the data – maybe it is a list of customers, a list of products, or a list of transactions including customers AND products… you can then start Pivoting!

1. Make sure there are no blank rows or blank columns where all you data is in your spreadsheet (extra rows and columns after all your data is ok though)
2. Click on the “Insert” tab in the ribbon
3. Look for the “Tables” group. 
4. The very first button in that group should be “Pivot Table”.  Click on that
5. Select “Pivot Table”
6. A little box will appear, and Excel 2007 will take an educated guess at what data you want to include in your pivot table.  Excel is generally pretty good and picking the data, so don’t worry about that part – but make sure you look at the bottom half of that box.  Excel is asking you where you want your pivot table to appear.  I suggest you choose a “New Worksheet” and then click “OK”
7. You will now notice what looks like a “Task Pane” appear in the right hand side of your Excel window.
8. In the top half of that task pane, select the fields that you want to include in your pivot table – basically you need to pick the information you want to slice and dice!
9. In the bottom half of that task pane, play around with where those fields sit in the Pivot Table.  You can filter based on certain fields, you can add fields as row or column labels, or you can get summary values for the contents of cells – you take your pick.  It is best to play around and experiment at this stage to get the exact outcome you want, simply because there are so many ways you might want to slice and dice that data.
10. When you are done, just close that task pan by clicking the “x” at the top of it (make sure you click the one at the top of the Task Pane, and not the one on the top of the window! that will close Excel!)
11. Now play around with your pivot table, and impress your boss!

Whilst it might look a little complicated to begin with, the best advice I can give is to give Pivot Tables a go and simply experiment with some data.  Then you will see the true potential of how much time this great feature of Excel could save you!

’till next time!
TNP 😉

I noticed one of my colleagues the other day struggling with her Excel Spreadsheet (yep that’s right, purchase even TNP needs a day job to pay the mortgage!). 

She had a sheet with a few hundred rows in it – all containing information about people who had registered to attend an event.  I noticed that she was trying to count how many people from each state had registered.

I popped over and suggested that she use a Pivot Table.

What is a Pivot Table?

A Pivot Table in Excel 2007 is a pretty powerful, yet simple way to slice and dice the data in your spreadsheet.  It can help you summarise hundreds, thousands, even millions of rows (in Excel 2007 at least) into information that you can take action on!  And because you can quickly change how you view your data, it can be a really useful tool to use in the business world.

So how do you create a simple Pivot Table in Excel 2007? 

Well first, you need some data in your spreadsheet that you want to slice and dice.  Once you have the data – maybe it is a list of customers, a list of products, or a list of transactions including customers AND products… you can then start Pivoting!

1. Make sure there are no blank rows or blank columns where all you data is in your spreadsheet (extra rows and columns after all your data is ok though)
2. Click on the “Insert” tab in the ribbon
3. Look for the “Tables” group. 
4. The very first button in that group should be “Pivot Table”.  Click on that
5. Select “Pivot Table”
6. A little box will appear, and Excel 2007 will take an educated guess at what data you want to include in your pivot table.  Excel is generally pretty good and picking the data, so don’t worry about that part – but make sure you look at the bottom half of that box.  Excel is asking you where you want your pivot table to appear.  I suggest you choose a “New Worksheet” and then click “OK”
7. You will now notice what looks like a “Task Pane” appear in the right hand side of your Excel window.
8. In the top half of that task pane, select the fields that you want to include in your pivot table – basically you need to pick the information you want to slice and dice!
9. In the bottom half of that task pane, play around with where those fields sit in the Pivot Table.  You can filter based on certain fields, you can add fields as row or column labels, or you can get summary values for the contents of cells – you take your pick.  It is best to play around and experiment at this stage to get the exact outcome you want, simply because there are so many ways you might want to slice and dice that data.
10. When you are done, just close that task pan by clicking the “x” at the top of it (make sure you click the one at the top of the Task Pane, and not the one on the top of the window! that will close Excel!)
11. Now play around with your pivot table, and impress your boss!

Whilst it might look a little complicated to begin with, the best advice I can give is to give Pivot Tables a go and simply experiment with some data.  Then you will see the true potential of how much time this great feature of Excel could save you!

’till next time!
TNP 😉

Say you have a nice long list of names.  But they are just all over the place.  Wouldn’t it be good to be able to sort them into alphabetical order?

Well in Word 2007 it is a piece of cake!  Assuming you have each item in your list on a separate line…

1. Select the text you want to sort alphabetically
2. Make sure you are on the “Home” tab in the Ribbon
3. Look for the “Paragraph” group
4. Click on the sort button (that is the one with an "A on top of a Z”
5. A box will appear – if you want straight alphabetical order on your paragraphs just hit ok.
6. If you want reverse alphabetical order, surgery
   make sure you select “Descending”

Using the sort button you can also sort numerically, order
or by date!  Simply change the “Type” of sort you want to do in the “Sort Text” box.

‘till next time!
TNP 😉

I noticed one of my colleagues the other day struggling with her Excel Spreadsheet (yep that’s right, purchase even TNP needs a day job to pay the mortgage!). 

She had a sheet with a few hundred rows in it – all containing information about people who had registered to attend an event.  I noticed that she was trying to count how many people from each state had registered.

I popped over and suggested that she use a Pivot Table.

What is a Pivot Table?

A Pivot Table in Excel 2007 is a pretty powerful, yet simple way to slice and dice the data in your spreadsheet.  It can help you summarise hundreds, thousands, even millions of rows (in Excel 2007 at least) into information that you can take action on!  And because you can quickly change how you view your data, it can be a really useful tool to use in the business world.

So how do you create a simple Pivot Table in Excel 2007? 

Well first, you need some data in your spreadsheet that you want to slice and dice.  Once you have the data – maybe it is a list of customers, a list of products, or a list of transactions including customers AND products… you can then start Pivoting!

1. Make sure there are no blank rows or blank columns where all you data is in your spreadsheet (extra rows and columns after all your data is ok though)
2. Click on the “Insert” tab in the ribbon
3. Look for the “Tables” group. 
4. The very first button in that group should be “Pivot Table”.  Click on that
5. Select “Pivot Table”
6. A little box will appear, and Excel 2007 will take an educated guess at what data you want to include in your pivot table.  Excel is generally pretty good and picking the data, so don’t worry about that part – but make sure you look at the bottom half of that box.  Excel is asking you where you want your pivot table to appear.  I suggest you choose a “New Worksheet” and then click “OK”
7. You will now notice what looks like a “Task Pane” appear in the right hand side of your Excel window.
8. In the top half of that task pane, select the fields that you want to include in your pivot table – basically you need to pick the information you want to slice and dice!
9. In the bottom half of that task pane, play around with where those fields sit in the Pivot Table.  You can filter based on certain fields, you can add fields as row or column labels, or you can get summary values for the contents of cells – you take your pick.  It is best to play around and experiment at this stage to get the exact outcome you want, simply because there are so many ways you might want to slice and dice that data.
10. When you are done, just close that task pan by clicking the “x” at the top of it (make sure you click the one at the top of the Task Pane, and not the one on the top of the window! that will close Excel!)
11. Now play around with your pivot table, and impress your boss!

Whilst it might look a little complicated to begin with, the best advice I can give is to give Pivot Tables a go and simply experiment with some data.  Then you will see the true potential of how much time this great feature of Excel could save you!

’till next time!
TNP 😉

Say you have a nice long list of names.  But they are just all over the place.  Wouldn’t it be good to be able to sort them into alphabetical order?

Well in Word 2007 it is a piece of cake!  Assuming you have each item in your list on a separate line…

1. Select the text you want to sort alphabetically
2. Make sure you are on the “Home” tab in the Ribbon
3. Look for the “Paragraph” group
4. Click on the sort button (that is the one with an "A on top of a Z”
5. A box will appear – if you want straight alphabetical order on your paragraphs just hit ok.
6. If you want reverse alphabetical order, surgery
   make sure you select “Descending”

Using the sort button you can also sort numerically, order
or by date!  Simply change the “Type” of sort you want to do in the “Sort Text” box.

‘till next time!
TNP 😉

Do you like to have a copy of your schedule with you all the time?  Would managing your time be simpler if you could have a printed copy of your Outlook calendar with you as you travel from meeting to meeting?

Printing out your calendar is a piece of cake in Outlook 2007!

1. Click on “Calendar” so you are looking at your calendar, no rx
   and not your email!
2. Select the view you want to print out – it could be a day, a week, a month, or a custom date range
3. Click on the File menu, and select “Print”

Simple as that!  And remember if you are not sure of which calendar print out you want, that you can Print Preview to see exactly what you are going to get before doing the final print!

By the way, printing a blank calendar in Outlook 2007 on month view is a really nice, and inexpensive way to put a calendar up in your office cubicle, or on your fridge at home to keep track of family events!

‘till next time!
TNP 😉

I noticed one of my colleagues the other day struggling with her Excel Spreadsheet (yep that’s right, purchase even TNP needs a day job to pay the mortgage!). 

She had a sheet with a few hundred rows in it – all containing information about people who had registered to attend an event.  I noticed that she was trying to count how many people from each state had registered.

I popped over and suggested that she use a Pivot Table.

What is a Pivot Table?

A Pivot Table in Excel 2007 is a pretty powerful, yet simple way to slice and dice the data in your spreadsheet.  It can help you summarise hundreds, thousands, even millions of rows (in Excel 2007 at least) into information that you can take action on!  And because you can quickly change how you view your data, it can be a really useful tool to use in the business world.

So how do you create a simple Pivot Table in Excel 2007? 

Well first, you need some data in your spreadsheet that you want to slice and dice.  Once you have the data – maybe it is a list of customers, a list of products, or a list of transactions including customers AND products… you can then start Pivoting!

1. Make sure there are no blank rows or blank columns where all you data is in your spreadsheet (extra rows and columns after all your data is ok though)
2. Click on the “Insert” tab in the ribbon
3. Look for the “Tables” group. 
4. The very first button in that group should be “Pivot Table”.  Click on that
5. Select “Pivot Table”
6. A little box will appear, and Excel 2007 will take an educated guess at what data you want to include in your pivot table.  Excel is generally pretty good and picking the data, so don’t worry about that part – but make sure you look at the bottom half of that box.  Excel is asking you where you want your pivot table to appear.  I suggest you choose a “New Worksheet” and then click “OK”
7. You will now notice what looks like a “Task Pane” appear in the right hand side of your Excel window.
8. In the top half of that task pane, select the fields that you want to include in your pivot table – basically you need to pick the information you want to slice and dice!
9. In the bottom half of that task pane, play around with where those fields sit in the Pivot Table.  You can filter based on certain fields, you can add fields as row or column labels, or you can get summary values for the contents of cells – you take your pick.  It is best to play around and experiment at this stage to get the exact outcome you want, simply because there are so many ways you might want to slice and dice that data.
10. When you are done, just close that task pan by clicking the “x” at the top of it (make sure you click the one at the top of the Task Pane, and not the one on the top of the window! that will close Excel!)
11. Now play around with your pivot table, and impress your boss!

Whilst it might look a little complicated to begin with, the best advice I can give is to give Pivot Tables a go and simply experiment with some data.  Then you will see the true potential of how much time this great feature of Excel could save you!

’till next time!
TNP 😉

Say you have a nice long list of names.  But they are just all over the place.  Wouldn’t it be good to be able to sort them into alphabetical order?

Well in Word 2007 it is a piece of cake!  Assuming you have each item in your list on a separate line…

1. Select the text you want to sort alphabetically
2. Make sure you are on the “Home” tab in the Ribbon
3. Look for the “Paragraph” group
4. Click on the sort button (that is the one with an "A on top of a Z”
5. A box will appear – if you want straight alphabetical order on your paragraphs just hit ok.
6. If you want reverse alphabetical order, surgery
   make sure you select “Descending”

Using the sort button you can also sort numerically, order
or by date!  Simply change the “Type” of sort you want to do in the “Sort Text” box.

‘till next time!
TNP 😉

Do you like to have a copy of your schedule with you all the time?  Would managing your time be simpler if you could have a printed copy of your Outlook calendar with you as you travel from meeting to meeting?

Printing out your calendar is a piece of cake in Outlook 2007!

1. Click on “Calendar” so you are looking at your calendar, no rx
   and not your email!
2. Select the view you want to print out – it could be a day, a week, a month, or a custom date range
3. Click on the File menu, and select “Print”

Simple as that!  And remember if you are not sure of which calendar print out you want, that you can Print Preview to see exactly what you are going to get before doing the final print!

By the way, printing a blank calendar in Outlook 2007 on month view is a really nice, and inexpensive way to put a calendar up in your office cubicle, or on your fridge at home to keep track of family events!

‘till next time!
TNP 😉

Is your inbox overflowing like mine?  Want to know how you can you quickly focus on what you need to action?  Well here is a nifty way to do it.

In Outlook 2007, medical you can organise your emails using views.  What is a view you say?  Basically think of it of a different way to look at all the emails you have.

In Outlook 2007 there are a number of views you can “view” your emails with:

• Messages
• Messages with Auto Preview
• Messages from the Last Seven Days
• Unread Messages in a particular folder
• Messages sent to me
• Messages in a timeline (very cool!)
• and some more complicated views like Outlook Data Files, medicine
  and Documents

So how do you change the view of your inbox?

Simple. 

1. Click on the folder you want to organise (ie your Inbox).
2. Click on the tools menu
3. Select “Organize”
4. In the window that appears, Hemorrhoids
   click “Using Views”
5. Select your favourite view

If you want to get really tricky you can create your own custom view by clicking the “Customize Current View” button in the top right hand corner of that box.  You can customise your view with different fields, group by’s, sorts. filters and other settings.

How cool is that!

‘till next time!
TNP 😉

So you are about to do a presentation – maybe it is at an event or a tradeshow – and instead of using a projector, population health your are presenting your slides using a plasma or LCD tv.

When you practice your presentation on your computer or on a projector, pilule your slides look fine.  But as soon as you try your deck on the plasma or LCD, your slides stretch and look horrible!

How do you get your slides to look perfect on the plasma?

Did you know that you can change the size, shape, and orientation of your slides in PowerPoint 2007!

You can set up your presentation for different “On Screen” sizes, such as 4:3 (which is the default), or 16:9 and 16:10 aspect ratios, which are perfect for widescreen plasma panels!

(just as an aside, you can also select different paper sizes as well if you want to build your slides specifically for printing as opposed to being used on screen – but we can talk about that another day!)

To change your slides from standard to widescreen

1. In PowerPoint 2007, click on the “Design” tab on the Ribbon
2. Look for the “Page Setup” group
3. Click on “Page Setup”
4. Look for the “Slides sized for:” drop down box, and select either “On-screen Show (16:9)” or “On-screen Show (16:10)
5. Click “OK”

Now you will have noticed the shape of your slides has changed from being close to a square, to more like a rectangle – and you will find that your graphics and text will no longer distort when you show your presentations on those fancy plasma screens!

Now get out there and impress those customers!

’till next time!
TNP 😉

Well… hasn’t 2008 gone quickly!

Now that Christmas is just past us, search now is the time to start thinking about your new years resolutions.

Most people usually say the same thing every year… I am going to exercise more… I am going to work less etc etc.  But most of the time by mid-February you are already way off track.

The key to a good new years resolution is to be specific, dosage and to write it down somewhere so you are reminded of it.  What better place to do that than in OneNote 2007!

Here is how to make a record of your resolutions… AND be reminded to actually work on them.

1) Write your resolutions in OneNote

2) Tag your resolutions with a specific tag from (you guessed it!) the “Tag” button.  If your resolution is to do a generic task, buy information pills tag it with a “To Do” tag.  If it is a little more specific – say, watch a movie… then tag it with the “Movie to See” tag.  There are dozens of tags you can choose from to give you a visual cue regarding your resolution.

Easy so far right?  Well, now with a little help from Outlook 2007, we can now be reminded throughout the year to work on our resolutions!

3) In OneNote 2007, you will see a “Task” button just to the left of the “Tag” button in the menu.  By selecting one of the “Task” options for your resolution, it can now appear as a task whenever you open Outlook.  So now whenever you are checking your email, your calendar, or your task list… you will have a gentle reminder to work on your new years resolutions, throughout the year.

 

So there you have it, you really have no excuses not to achieve your resolutions this year!

’till next time!
TNP 😉

Keeping up to date with the latest Office 2007 Help, no rx Tips, page Tricks and Tutorials from TheNewPaperclip.com is easy.  There are three options you can choose from to receive regular help and productivity tips!

1) Subscribe to the email newsletter!

To get a regular newsletter packed full of help, tips, tricks and tutorials for Word 2007, Outlook 2007, PowerPoint 2007, Excel 2007 and the rest of the Microsoft Office 2007 suite, simply fill in this registration form below!  PLUS you get a free copy of my ebook when you confirm your registration!

2) Subscribe to the RSS feed!

Simply open up your favourite feed reader, and subscribe to the feed – http://feeds.feedburner.com/TheNewPaperclip

3) Follow The New Paperclip on Twitter!

That’s right, you can how get Office 2007 productivity tips as soon as they are published simply by following @thenewpaperclip on Twitter!
Keeping up to date with the latest Office 2007 Help, pulmonologist Tips, Tricks and Tutorials from TheNewPaperclip.com is easy.  There are three options you can choose from to receive regular help and productivity tips!

1) Subscribe to the email newsletter!

To get a regular newsletter packed full of help, tips, tricks and tutorials for Word 2007, Outlook 2007, PowerPoint 2007, Excel 2007 and the rest of the Microsoft Office 2007 suite, simply fill in this registration form below!  PLUS you get a free copy of my ebook when you confirm your registration!

2) Subscribe to the RSS feed!

Simply open up your favourite feed reader, and subscribe to the feed – http://feeds.feedburner.com/TheNewPaperclip

3) Follow The New Paperclip on Twitter!

That’s right, you can how get Office 2007 productivity tips as soon as they are published simply by following @thenewpaperclip on Twitter!

Wow… I was busy working away today (on New Years Eve!) and discovered a really easy way to get nice looking borders on the cells you want in Excel 2007.

Instead of selecting the cells, therapy going up to the Font Group and playing with the Borders buttons, did you know you can simply DRAW your borders onto your spreadsheet!

All you have to do to draw your borders in Excel 2007 is:

1) Make sure you are on the “Home” tab in the Ribbon

2) Look for the “Font” group

3) Select the drop down arrow beside the “Borders” button

4) Select one of the “Draw Borders” options from the bottom of the menu.

My three favourite Draw Border tools in Excel 2007 are Draw Border, which allows you to quickly get a border around a group of cells, Draw Border Grid, which draws you (as the name suggests) a grid, and finally Erase Border, which you guessed it, helps you erase your border!

So next time you want to make your spreadsheet more professional with borders the EASY way, make sure you check out the Draw Border features.

’till next time!
TNP 😉

So, implant the new year is just a few hours away… time to crack open the calendars your relatives gave you for Christmas right!

But what if you didn’t get any?

Well did you know you can create your own calendars nice and quickly with Word 2007!

All you need to do is to open a new document by clicking on the “Office Orb” (the old file menu in the top left hand corner of Word), what is ed and then clicking “New”

On the New Document dialog box that appears, prescription you will see down the left hand side a wide range of templates that are available to you.  Down in the “Microsoft Office Online” section, you will find a “Calendars” option – click on that!

Word will connect to Microsoft Office Online, and give you a wide variety of calendar options.  For your 2009 calendar, click on “2009 calendars”.  For other options such as multiple year calendars, simply click on those.

Once you click on “2009 calendars” you will see a few dozen different options to choose from – different calendar structures and different calendar designs.

My favourite, especially if you want to add your own pictures to the calendar is the “2009 Picture Calendar Featured at Office Online”.  Great for family pictures!

’till next time!
TNP 😉

Share this tip: on Twitter on Facebook on Google+