Rename your sheet in Excel

Do you have multiple sheets in your Excel workbook? Want to have something more descriptive than “Sheet 1″, “Sheet 2″, or “Sheet 3″ so you can keep track of all the work in your spreadsheet, model, or budget? It is quick and easy to rename your sheets in Excel 2007, Excel 2010 or Excel 2013.

  1. Right click on the tab for the sheet you want to rename
  2. Click “Rename”
  3. Type your new name in… and then hit enter!

 

 

Simple as that – now you can keep track of all those sheets in your workbook with ease.

’till next time!
TNP ;)

Add, subtract or average time in Excel

If you are like me and you want to do some kind of calculation or duration in hours, minutes and seconds – I am sure you have been very frustrated with Excel totally ignoring what you want to achieve! Well did you know that it isn’t as hard as Excel makes it out to be! You can very quickly do calculations like adding two times together, subtracting times from each other, or seeing what the average time is of a range. They key is to ensure that your cells or formatted correctly first. Here is how you do it!

  1. Select the cells that you want to add your times to
  2. Right click, and then select “format cells” from the menu that appears
  3. In the “format cells” dialog box that appears, make sure you are on the “Number” tab
  4. In the category box, select “custom”
  5. Look for a Type in the list which looks like [h]:mm:ss and select it
  6. Click Ok

Now that your numbers are formatted correctly as times, you can start doing your calculations. Simply add, subtract or average like you normally would in excel using formulas like =SUM(), and =AVERAGE()

’till next time!
TNP ;)

Select difficult to get to objects in Word using the Selection Pane

Have you ever worked on a document with a lot of shapes, diagrams, pictures or text boxes?  Don’t you hate it when they overlap or are on top of each other and it is near on impossible to select the right object!  You may be like me and move the objects on top to get to the ones below … but did you know that there is an easier, much better way?  There is – using the Selection Pane.

The selection pane enables you to select the object by its name, irrespective of where the object is in your document.   Not only that, you can make an object invisible for a short amount of time so you can see everything else underneath it!

To turn on the selection pane in Word:

  1. On the “Home” tab, look for  the “Editing” group on the right hand side of the screen
  2. Click on the little arrow beside “Select”
  3. Select “Selection Pane”

The selection pane will appear as a task pane on the right hand side of your Word screen.  To select an object in your document, simply click on the name of the one you are after.  You can make an object invisible by simply clicking on the “eye”.  To bring it back, click on the “closed eye”.

’till next time!
TNP ;)

Calculate the number of work days between two dates in Excel

Ever wondered how many work days there are between two dates?  Maybe you are counting down the number of days you have left in the office before your big holiday?  Maybe you just need to know how many days you have until that project is due?  Whatever the reason, using Excel you can calculate the number of business days between two dates.

To do so, we will use the NETWORKDAYS formula.

  1. Type the two dates you want to calculate the number of days betwen into Excel – in one cell type the date you want to calculate from, and the other cell the date you want to calculate to
  2. In another cell, type =NETWORKDAYS(
  3. Select the first cell – if you typed the first date in A1 the formula will now look like =NETWORKDAYS(A1
  4. type a comma
  5. Select the second cell – if you typed the second date in B1 the formular will now look like =NEWWORKDAYS(A1,B1
  6. Type the closing bracket to complete the formula – it will now look like =NETWORKDAYS(A1,B1)
  7. Hit enter!

There you have it, the number of days you need to wait until you go away, or the number of days to countdown until that deadline!

’till next time!
TNP ;)

How to multiply two cells together in Excel

Ever wanted to multiply two different numbers together in Microsoft Excel?  The great thing about Excel is that you don’t need to use a calculator to do it – Excel can do it for you!  But we need to tell Excel what to do first!

  1. Type the numbers into Excel that you want to “times by”.  Make sure they are in two different cells (a cell is one of those little square boxes you see)
  2. Click on the cell where you want the answer to appear
  3. Start typing the following formula.  Firstly, hit the equals key, then select the first number, then add a “multiply” symbol – which in this case is the star on the 8 key (shift 8), then select the second number and hit enter
  4. Your answer will appear!

If you want to take a look at the formula we just created together, click on the cell where the answer appears.  Look up in the formula bar (just below the ribbon) you will see something like =A1*A2.  Don’t worry if it doesn’t make sense at the moment, the best way to look at it is to read it out loud.  In this case “this cell equals whatever is in cell A1 times whatever is in cell A2″.  Simple!

’till next time!
TNP ;)

Change a number to a percentage (%) in Excel

Have you ever wanted to change a number into a percentage in Excel? The easiest way is to just type the number in and add the % symbol (by holding the shift key and F5), and then hitting enter.  If you want to change a number afterwards into a percentage, here is what you do:

  1. Click on the cell that you want to turn into a percentage
  2. On the home tab, look for the number group about half way across the ribbon, and then click on the percentage symbol

That’s it?  Well not quite.  You may notice that your percentage may now have a few extra zeros on the end!  Hmm that isn’t right.  That is because Excel knows percentages to be a fraction of a single whole number.  For example if your number was 34, and you did the above procedure, the cell would now look like 3400%.  Excel thought that 34 meant 34 times, or 3400%.

To fix it up, click on the cell and delete the two extra zeros from the cell.

To avoid it happening in the future, just remember that you need to use the decimal point when you type numbers in.  For example, 0.34 (which equals 34%), instead of 34 (which equals 3400%).

It may seem a little complex at first, but once you start playing around with percentages in your spreadsheet you will pick it up very quickly!

’till next time!
TNP ;)

Add two cells together in Excel

Have you ever wanted to figure out what the total would be if you added two cells together in Excel?  There is a really simple way to do it – without your calculator.  Let me explain how….

  1. Make sure you have typed your numbers into Excel in different cells
  2. Select the cell you want to have the total appear in (it may make sense to be below the numbers you just typed in)
  3. Once you have clicked on the cell, type an equals sign, select the first cell, type a plus sign,  then select the second cell, then hit enter
  4. You should now see the total of the two cells!!!

If you click on the cell with the total in it, you will notice something we call a formula.  In this case it might look like “=A1+A2 ”.  The best way to figure out what the formula in the cell is calculating is if you read it out loud.  In this case it says “this cell equals whatever is in A1 plus whatever is in A2″.

So there you have it – the simple way to add two cells together in Excel (and your introduction to formulas!)

’till next time!
TNP ;)

How to Undo in Excel

So… you have been working on that spreadsheet for hours and all of a sudden you deleted the wrong thing.  Now you need to undo what you did in Excel 2013? Here is how to do it:

  1. Look in the top left hand corner of the Excel screen – above the File menu.  You will probably see a few small icons there.  Look for the arrow pointing backwards – that is the undo button.
  2. Click on it to undo what you just did
  3. If you want to undo a few steps, you can click on the little drop down arrow on the button, and you can undo the last 20 or so actions you have done

There you go – your formulas are still safe!

’till next time!
TNP ;)

What is the annoying Yellow Bar in Word, Excel or PowerPoint?

If you have been using Microsoft Office 2007 or 2010 for a while, you might have come across what most people call the “Annoying Yellow Bar”

If you don’t know what I am talking about – open up a word document that someone has send you via email – or a spread sheet that you might find on a website.  Chances are when you open that Word, Excel or PowerPoint file, you will see the “Annoying Yellow Bar” – and you will not be able to edit, print, or save your document.

Well it may be annoying, but it is actually really important.  The yellow bar shows you that Word, Excel or PowerPoint is running in what we call “Protected Mode”.  Any time you open a file that isn’t on your computer, or from somewhere that you can’t trust (like the internet), the document will be opened in protected mode, to (as the name suggests) protect your computer from harm.

It opens up the document so you can take a look at it, and then if you are sure it is what you are looking for, and you trust that it will not do any harm, you can then click on the “Enable Editing” button, which sits on the yellow protected view bar.

Now there are ways to stop Protected View from happening, but trust me, it is worth putting up with because if you accidently open a document that causes damage to your computer, it is a LOT more annoying!

‘till next time!
TNP ;)

Rugby Fan? Use Excel to track the Rugby World Cup

If you are like me and follow Rugby Union (Go the ALL BLACKS!!!), you will love something that the team at the Excel Blog posted the other day.

It is a spread sheet that you can use to track your team’s progress during the world cup.

Read more about the Rugby World Cup score tracker and get your hands on the free Excel spread sheet on the Excel Blog