Increase Decimal

I found a good shortcut today to increase the decimal places.

Alt H 0

For example you might use these two shortcuts one after the other to format a range as a percentage with one decimal place.

Ctrl + Shift + %
Alt H 0

Making Subtotals Bold

When you use the SUBTOTAL feature in the Data ribbon tab it automatically inserts subtotals in your list – see blog post on it here.

One problem with this is that is only makes the cell with the word Total bold – it doesn’t make the whole row bold.

If you want the whole row to be bold it isn’t hard to fix.

  1. Select the whole range involved.
  2. Use the grouping button 2 top left corner. See image below.
  3. Then hold the Alt key down and press the ; (semicolon key) – this selects just the visible cells.
  4. Then press Ctrl + b to bold it.
  5. Click another cell to reset the range and you are done.

Finding Those Pesky Links

Bill Manville

Thanks to Bill Manville for sharing the add-in.

This is a free add-in that’s been around for a long time and it finds most of those frustrating links that may have been created by

  • deleted files or folders
  • moved files or folders
  • renamed files or folders
  • sheets copied between files
  • charts copied between files

The add-in has been updated over the years so it now handles Power Query.

Printing Separate Print Areas

If you need to print a number of separate print areas from the one worksheet in one step there is an option that allows you to create a print area that includes additional ranges.

Create your first print area as per normal.

Select another print range and click the Page Layout ribbon and use the Print Area drop down and choose Add to Print Area. Keep adding ranges as you require.

You can also use the Ctrl key and mouse to select multiple separate ranges and then use Set Print Area.

The above technique allows you to add to that Print Area if you miss a range.

Downside

The only problem with this option is that each separate range is printed on a separate page(s).

Export a sheet as a PDF

It takes a few clicks but it is possible

I have previously posted about using CutePDF to create pdfs from Excel sheets. There is another way, but it takes a few clicks and it only works in Excel 2010 and later versions. (It may work in Excel 2007 but I have taken that version off my PC so I can’t test it.)

Pasting a Filtered List in a Formatted Table

I have been recently working with some very large (500,000+ rows) tables. As part of the process I had to filter one Formatted Table, copy it and then paste it in another Formatted Table. Excel would sit there processing for a long time – but I found a technique to speed up the process.

After you copy the filtered list, simply paste it in a blank sheet. This is virtually instantaneous. Then copy that interim list and paste in the other Formatted Table – again almost instantaneous. Two quick pastes is a lot quicker than paste and wait.

In case you didn’t know, when you copy a filtered list, you only copy the visible cells – the filtered ones. The hidden cells are omitted from the copy.

So if you are experiencing delays in the pasting of a filtered list, just use an interim paste and then another copy to speed up your copy and paste.

For more information on Formatted Tables, check out the links below.

Format As Table in Excel Part 1

Excel Format as Table Part 2 [VIDEO]

 

Inserting Data into Formatted Tables

I was recently working with a large Formatted Table in excess of 100,000 rows with Power Query.

I was copying in new data to a temporary workings table and then manipulating it with Power Query to get the required output. The data was varying lengths. I found that if you pasted data into a Formatted Table that was a lot longer than the Formatted Table it can take a long time for Excel to process the paste (I am talking tens of thousands of extra rows).

To get around this delay I found that if you first expanded the Formatted Table using Insert Rows, the paste was virtually instantaneous. Inserting the extra rows was also very quick.

So if your Table has sufficient rows the paste is quick, if Excel needs to expand the table to fit the new data, it can be slow for large data sets. Make sure you insert sufficient blank rows to speed up the paste.

You can learn more about Formatted Tables at the two blog posts below. I have also covered the topic in numerous free webinars.

Format As Table in Excel Part 1

Excel Format as Table Part 2 [VIDEO]

Sheet Protection – No password

When you protect a sheet with a password, you must make a note of the password, otherwise you create problems for yourself.

Did you know you don’t have to supply a password?

To apply sheet protection, right click the sheet tab and choose Protect Sheet.

When the Protect Sheet dialog displays just press Enter or click OK.

This protects the sheet with no password.

This stops accidental changes and you can easily unprotect the sheet to make changes.

You can right click the sheet tab to unprotect as well.