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.
Excel 2013 added the Data Model to Excel.
The Relationships option (Data ribbon) is part of that model. It allows you to create relationships between tables so that you can use a PivotTable to report on multiple tables.
See my December 2016 INTHEBLACK article for an example.
The keyboard shortcut to create or edit, a Relationship is easy to remember – it is
Alt a a
Pressed in sequence, not held down.
A short book with short chapters meant to get you thinking differently about work.
Much of what the book covers goes against the “common” advice out there.
It gets you thinking (sometimes differently) and that’s one thing a good book is all about.
David Heinemeir Hansson has recently been featured on two Tim Ferris podcasts – two excellent episodes.
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.
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.
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.
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.
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
Just because you can, doesn’t mean you should. This applies to many thing in Excel and especially to charts. With charts the “less is more” philosophy works well. Have a look at the four charts in the image below.
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
In Excel you can us Save As to save a file as a pdf, but it isn’t quite as effective in Excel as it is for MS Word. Often you only want to save a single sheet or a few sheets to pdf. Try this.
Another nice email
Always great to start the day with an email like this.
This one followed a free webinar I ran in October 2016.