Monthly Archives: June 2014
A little self-criticism is as beneficial as too much is harmful.
Anonymous
Hide Grouping Icons
Grouping icons allow you to hide and unhide rows and columns really quickly. Sometimes you might want to hide the grouping icons to maximise the Excel grid.
The keyboard shortcut to hide the grouping icons is
Ctrl + 8
The same shortcut makes them visible again.
Some great tips on communication (not just presenting) and not the type you usually expect.
Pivot Table Wizard
You may have thought the Pivot Table Wizard had been removed from Excel, but it hasn’t. You can use the following keyboard shortcut to activate it.
Alt d p
Pressed in sequence, not held down.
The Pivot Table Wizard doesn’t recognise formatted tables, and you should only use it if you need to define Multiple Consolidation Ranges.
I LOVE TED Talks, they cover so many different topics and are, on the whole, brilliant! I have been watching then for years and highly recommend you check a few out. You will be amazed. They are short and usually very well presented and often humorous.
Insert Subtotals Dialog
In a sorted list you can have Excel automatically insert subtotals based on the sorted column.
With a cell selected in the table, the keyboard shortcut for the Subtotals Dialog is
Alt a b
Pressed in sequence, not held down.
Hide the Ribbon using Double Click
In Excel you can click, then double click a ribbon tab name, to hide or unhide the whole ribbon.
Until you try, you don’t know what you can do.
Henry James
Data Validation Dialog
To open the Data Validation dialog use the following keyboard shortcut.
Alt a v v
Pressed in sequence, not held down.
Data Validation enables you to limit what a user can enter in a cell.
Move between open Excel files
The keyboard shortcut to move between open workbooks is
Ctrl + Tab
Sometimes this can be quicker than using the mouse.
Ctrl + Shift + Tab will go through the files in the reverse direction.
Don’t be too self-critical. Learn to be on your own side.
Anonymous
Anonymous
Validation in Excel using the ABS function
Avoid issues with negatives
When doing validations in Excel you often need to check values against a tolerance figure, usually 1 cent or 1 dollar.
In the middle of difficulty lies opportunity.
Albert Einstein
Set Freeze Panes
In Excel, to set or remove Freeze Panes use
Alt w f f
Keys pressed in sequence, not held down.
Freeze Panes keeps certain rows and columns on the screen as you scroll down and across.
Add New Sheet
To insert a new sheet in Excel use
Shift + F11
Advanced Excel Reporting For Management Accountants (Wiley, 2014)
In this book I share the reporting techniques that I’ve learned over the 20 years that I’ve been using spreadsheets.
You don’t need to be an advanced user to take advantage of this book.
If you have basic skills, the book with guide you through the techniques required to create advanced, automated reports.
Excel Webinars
Turn your lunch hour into a CPD hour
Online webinars have lots of advantages. They can be a great way to learn new topics. They can also be based solely on PowerPoint slides. It all depends on the topic and the presentation.
The person who says it cannot be done should not interrupt the person doing it.
Chinese proverb
View the next sheet
In Excel, if you want to view the next sheet to the right use
Ctrl + Page Down
Next sheet to the left use
Ctrl + Page Up
See all cell comments
If you want to see all the cell comments in an Excel sheet, in one step use
Alt r a
Keys are pressed in sequence, not help down. Pressing again, will hide all the cell comments.
(Cell comments are like sticky notes for cells)
Note in the most recent versions of Excel Comments have been renamed to Notes. The terms Comments is now used for threaded (linked) Comments.
If we are facing in the right direction, all we have to do is keep on walking.
Buddhist Proverb
Hide Ribbon
A shortcut to hide (or unhide) the Excel ribbon is
Ctrl + F1
Works for the ribbons in the other Office apps as well.
Speeding Up Excel Macros
Tips and tricks for faster macros
There are two commands you can insert at the top of your code to speed up your macros.
It is wonderful what you can do when you have to.
C.S. Lewis (1898–1963)
Quick Win Development
I saw a demo of this Excel Add-in and it looked pretty good. I met the developer and he is keen to develop the package further. I don’t do any work with Xero, but if you do, it might be worth investigating – it is reasonably priced. It brings in data directly from Xero into Excel tables ready for Pivot Table analysis or formula based reports.
Enter Today’s Date
A keyboard shortcut to enter today’s date in a cell.
Ctrl + ;
Hold the Ctrl key down and press the semi-colon.
This is a data entry and it won’t change.
Row and Column Grouping
In Excel to apply row or column grouping, select the range and press
Shift + Alt + right arrow
To remove grouping use
Shift + Alt + left arrow
After you use it you can use function key F4 to repeat it.
An athlete may run ten thousand miles in order to prepare for one hundred yards. Quantity gives experience.
Ray Bradbury (1920–2012)
Storytelling with Data
June 4, 2014
June 4, 2014
I nice article on alternatives to pie charts. I prefer the 100% stacked bar chart.
The Excel legend, John Walkenbach is the most respected Excel author. He has been writing about Excel for decades. I have many of his books. Look up Excel Guru and you’ll see his picture. I was honored that his review is on the back of my first book.
His site contains lots of Excel resources – his Power Utility Pack Add-in a worthwhile purchase.