Pasting Charts in Word and PowerPoint

When you paste Excel charts into Word or PowerPoint you may also be pasting all the underlying data that created the chart.

To get around that problem, you can use the Copy as Picture option.

This option is on a drop down on the Copy button on the Home ribbon – see image below.

copy_chart_1

You have a few options to choose from on what and how to copy.

copy_chart_2

This treats the chart as a graphic, which breaks any links to the underlying data. It also makes it much easier to re-size the chart when you paste it in the destination document.

It is not dynamic at all – it is a point in time capture.

Format part of a text string

Many people don’t know that you can format part of a text string in a cell.

This can be useful if you want to highlight, or emphasis, a particular word or phrase in a text string.

You can double click a word in the Formula Bar or while editing in a cell and a small menu pops up – see image below.

test

The options are reasonably limited, but you can use Bold or Italic, change the font; the font size and font colour – see examples below.

text-format

Show or Hide Cell Comments

Cell comments (they are like a sticky notes for cells) are great for instructions and documentation.

To show all the comments press, in sequence, not held down

Alt r  a

This is a toggle, so you can use the same sequence to hide all the comments.

alt-r-a

Reapply a Filter

Let’s say you have applied a filter to a list.

The list is dynamic and the values have been updated by a refresh – the filter is out of date.

You don’t have to go into the filter drop down to re-apply the filter.

You can use a keyboard shortcut whilst in any cell in the table.

Ctrl + Alt + L

 

reapply

Calculation Issue

There are two reasons your Excel file won’t calculate.

The first, and easiest to fix, is that Calculation is turned off – see a previous tip on that issue.

The other reason is that there is a Circular Reference in the file. Circular References can stop calculation.

You can confirm if there is a Circular Reference in a file by looking at the bottom left-hand corner of the screen – see image below.

circ

A Circular Reference means that a cell is referring to itself in a calculation. A simple example is shown in the image above, where cell A1 has a formula =A1.

Excel cannot resolve this calculation, so zero is displayed.

In the Formulas tab, on the right-hand side, the Error Checking icon drop down will help you identify the Circular References in the file.

circ_2

Learning Excel’s Function Arguments

When you start to use a function it can take some time to learn the arguments required and understand what Excel expects for each argument. Eg should it be a cell or a range or either?

When you have the start of a function in the formula bar, you can either press Ctrl + a or click the fx symbol on the left of the formula bar – see image below.

function_1

In the image above, the argument in square brackets [range_lookup] is optional. Square brackets around an argument mean it is optional.

This will display a dialog with a listing of the arguments required by a function. The bold names are required, the non-bold names are optional.

function_2

This listing provides a lot more detail on what Excel is expecting for each function argument. This helps you learn more about how to create and use the function.

[related_posts_by_tax taxonomies="category,post_tag"}

You Can Undo After You Save

I amazed how many people are unaware of a change that happened way back in Excel 2007.

In Office 2007 and later versions you can use Undo after you have saved the file.

In the old days (Excel 2003 and earlier) saving used to clear the undo list – it no longer does.

So undo to your heart’s content.

Remember there is a keyboard shortcut to undo.

Ctrl + z

undo1

 

Summing a range with Errors

If you have a column of values with errors, but you want to see what the values add up to, use the AGGREGATE function (added in Excel 2010).

If column A has the values and errors use

=AGGREGATE(9,6,A:A)

The 9 means SUM. The 6 means ignore errors.

Working with Quarters in Excel

You might not know that Excel knows about quarters. That’s 3 months, not 25 cents.

If you type Q1 in a call and drag the cell with the Fill Handle (bottom right corner of cell) you will see that Excel repeats the sequence Q1, Q2, Q3 and Q4. It doesn’t do Q5 or above.

This works for Qtr1 as well.

 

Selecting Ranges

When selecting a range with the mouse or keyboard, keep an eye on the Name Box – top left corner of the screen, just above the grid.

It tells you how many rows (R) and columns (C) you have already selected – see image below.

Select_RangeAs soon as you release the mouse, or stop using the keyboard, the values will disappear. You have to be quick to see it.

Date Entry Tricks

When entering dates from the current calendar year you can leave out the year. Excel assumes the current year.

Entering 27/7 and pressing Enter will enter 27/7/16. I wrote this post in 2016.

Whatever you enter is first considered as dd/mm. If that doesn’t make sense eg 9/15 then mm/yy if used.

If you enter a month and year combination it assumes the first of the month.

For example entering 9/15 will enter the 1/9/15.

You can use the forward slash / or the dash – between values in a date and Excel will recognise the date.

Excel treats 9/15 the same as 9-15.

To entry today’s date use Ctrl + ;

Always Refer to Cell A1

If you need to ALWAYS refer to cell A1, regardless of whether row or columns are inserted or deleted, then use the following formula.

=INDIRECT("A1")

This will always display the entry in cell A1 on the current sheet.

Another formula that always refers to cell A1 on the current sheet is

=INDEX(1:1048576,1,1)