I wanted to offer a solution to a common problem I see in Excel. It relates to creating totals in data that isn’t structured that well.
Monthly Archives: November 2016
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.
You have a few options to choose from on what and how to copy.
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.
Excel Full Screen
Maximise your grid area
There are times in Excel when you want to see as much of the Excel grid as possible. You may be reviewing or presenting a dashboard or looking at a large PivotTable. Excel has a Full Screen mode that shows just the grid and the sheet tabs.
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.
The options are reasonably limited, but you can use Bold or Italic, change the font; the font size and font colour – see examples below.
Using Lists in Excel
Make the most of Format as Table
It is common to work with lists in Excel. Lists of departments, names and other categories you frequently use. This blog post covers a few techniques that work really well together to create robust reporting systems.
Excel Cell Comments and Documentation
Macro to Create a Comment Report for a File
I was watching a video a while back and some Excel experts were lamenting the lack of a documentation standard in Excel. They mentioned that the cell comments system could be used for documentation, but there was no way to centralise all the comments. Well, I have written a macro to do just that.
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.
Note: Comments have been renamed Notes in later versions of Excel. Comments now refer to a new features of threaded comments.
Shortcut to Re-Apply Sort
To re-apply the current sort – select a cell inside the table and press in sequence
Alt a s s Enter
Could be called the donkey shortcut.