Blog

 
#7 Excel Tip a Day: Display your numbers as millions

#7 Excel Tip a Day: Display your numbers as millions

Here is tip #7 in a series of an Excel Tip a Day for December 2015 – Display your numbers as millions. Tips range from the obvious to the simple to some more complex tips. Hopefully there is something you find helpful!

If you work with spreadsheets with large numbers (we all love that, right?), it is often unsightly to show those numbers in full. “$12,158,168.05″ really doesn’t say any more than “$12.16m”, particularly on an output sheet. In order to change $12,158,168.05 to $12.16m, follow these steps:

  1. Select the cell(s) you wish to format.
  2. On the Home ribbon, press the little down arrow in the bottom right of the number format window. It’s the one with “Currency” at the top.
  3. In the Category list on the Number tab, choose Custom.
  4. In the text box under the word “Type:”, type in $0.00,,”m”

Done! If you want more or less decimals, add or subtract 0’s after the decimal in the above format.

I hope you find this helpful!

#6 Tip a Day: Multi-format within a cell

Here is tip # 4 in a series of an Excel Tip a Day for December 2015 – Multi-format within a cell. Tips range from the obvious to the simple to some more complex tips. Hopefully there is something you find helpful!

Occasionally, you may have a desire to format a text string with different formats for different parts of the string. Like this:

CaptureIt is actually quite easy to do.

  1. Type your text.
  2. Double click on the cell.
  3. Choose the subset of the text string you would like to format. You can format this text independently of the other text in the cell.
  4. Repeat for the other subsets of the text string, as desired.

I hop you find this helpful.

#5 Tip a Day: F4 – repeat action

Ok, so this one is a quick one. We are on the 5th day of the Excel Tip a Day for December 2015.

Forever, I have been using the format painter to copy the formats of cells, but I recently learned there is a better way!

If you format a cell in a way that you want to copy, you simply have to highlight the cells you want to copy the format to and hit F4. That should save you some clicking!

Hope you find this useful!

#4 Tip a Day: Colours in your workbook

Here is tip # 4 in a series of an Excel Tip a Day for December 2015. Tips range from the obvious to the simple to some more complex tips. Hopefully there is something you find helpful!

I come across many spreadsheets created by others that look like they were taking acid when they put them together… So may colours! Having cells coloured is a good idea, providing one thing:

The colours have to mean something.

In order to mean something, those colours should be spelt out on a control sheet, such as this:

colours helper

 

 

 

 

This small addition will help anyone who picks up your spreadsheet to determine what is going on. It should also make your life easier – you don’t have to think about the colours you are going to use, they are set up front based on the reason you are colouring the cell. If there is no reason – don’t colour it!

#3 Tip a day: Excel Camera Tool

#3 Tip a day: Excel Camera Tool

This tip is # 3 in a series of a Tip a Day for December 2015. Tips range from the obvious to the simple to some more complex tips. Hopefully there is something you find helpful!

If you’re ever put a spreadsheet together that has an output or report page, you might have come up against a situation where things just didn’t look right. I guess it is one of the downsides of the Excel grid, you are somewhat constrained in your capacity to place things where you want them – or are you?

The Camera tool allows you to highlight a range and then take a photo of that range to create a picture that updates automatically as the information in the range changes. You can place the picture anywhere on a page and it will display that information independent of the formatting of the cells below the picture. Fundamentally, you are no longer constrained by the width/height of cells when trying to lay out your spreadsheet.

So how do you get the Camera tool?

  1. Right click on the ribbon.
  2. Choose “Customize the Ribbon…
  3. Change “Choose commands from:” to All Commands.
  4. In the list below, you will find Camera.
  5. Select it and add it to the ribbon you would like to use it in.
  6. Highlight an area, then hit the camera icon you just added to the ribbon. You’re away!

I hope you can use this to remove the restrictions of columns and rows!

#2 Tip a Day: Ctrl + 0

#2 Tip a Day: Ctrl + 0

This tip is # 2 in a series of a Tip a Day for December 2015. Tips range from the obvious to the simple to some more complex tips. Hopefully there is something you find helpful!

If you’re working with spreadsheets that stretch out to the right beyond the current frame, chances are you jump out to the right a fair bit. Usually, this is done by positioning the active cell on a row with data, using control->arrow to jump to the end of the row’s data.

To save a little time, if you go one column to the right of your most right-hand data, control->arrow to the right and then press Ctrl+0, all columns to the right will be hidden. They’re blank anyway, so you don’t need to hide them. Now, you can get to the most right-hand column from anywhere, not just rows with data.

This tip will only save you small amounts of time per use. But over time, those time savings will add up!

P.S. You can also do the same with the rows below the bottom used cell using Ctrl+9