Save content
Have you found this content useful? Use the button above to save it to your profile.
AIA

Microsoft reveals ‘five ultimate Excel tips’

by
19th Feb 2015
Save content
Have you found this content useful? Use the button above to save it to your profile.

Here on Excel Zone we know that accountants love spreadsheet productivity tips - we know because of the response to the ones we’ve published over the past decade.

So we thought we’d pass on a few published on the Microsoft Office team blog just before Christmas (with thanks to the ever-informative Contextures blog for bringing it to our attention). If you know all of the following five tricks, they said they’d eat their hat.

So we thought we’d put them to the test… let us know if you weren’t aware of any of the following tips:

Press F6 to move easily between two panes - David Ringstrom recently posted advice on freezing the middle row of a worksheet, but if you just want a simpler two-way split, Microsoft advises using the scrollbar splitter. As long as you have fewer than four worksheets open, you can hit F6 between repeatedly to cycle between the panes. (Careful Microsoft Office posse - we covered this one in our 2006 Excel navigation tips article).

Where to find the 'freeze' icon in Excel

Control-] to identify what formulas reference a cell - Useful if you want to see which cells might be affected by changing a cell that may be a reference. We also had this one in our navigation tips guide.

Transpose a dataset - We’re big fans of Paste Special-Transpose on Excel Zone, but Microsoft helpfully points out that there’s a function that can do this too. For example, if you wanted to transpose two rows across four columns into two rows four columns deep. Assuming you selected the intial two-row, four column data set running from cell B3, just select a block of cells of the appropriate shape and enter the following formula into the top left cell of your selection (which should be displayed in white rather than highlighted in blue): =TRANSPOSE(B3:E4). Do not press enter. Instead, press Control-Shift-Enter simultaneously and the data will be transposedas an “array”. So any changes you make to the original data will also be reflected in your transposed cells.

Jump to the last cell in a direction  Double-clicking on any of the rectangular borders around a cell within a block will immediately move the cursor to the last contiguous cell above/below or to the right or left of your original place, depending on which line you clicked.

Autofill from a list If you regularly enter the same data in a column, Excel will automatically offer a selection of previously chosen options. After clicking in an empty cell, hit Alt-DownArrow to see a list of options taken from the rest of the column above.

How well did you do? Is there going to be mass indigestion at the Microsoft Office HQ? And if you have a favourite Excel productivity tip, feel free to share it with other members AccountingWEB members.

Replies (10)

Please login or register to join the discussion.

avatar
By dave.white.xg
20th Feb 2015 17:11

Top Excel Tips

Great article on Excel as always, John.

But I think these would have been better described as "the Top Five Least Useful Excel Tips in the world".

I am proud to say I did not know more than a couple of these existed, and I don't use any of them in my day to day work. Nor will I be changing what I do!

I agree with you that Copy Paste Transpose is a godsend for the busy Excel user. But why would anyone want to use a formula for this? Surely by it's very nature it is an ad hoc technique to get things in the right place when they are in the wrong place. Or am I missing something?

For what it is worth, my own top Excel Tip is Copy, Paste as Bitmap. I use this all the time to take pictures of different regions of spreadsheets or different data sets located on separate spreadsheets that I need to compare. It works best when the underlying formatting of the two regions or sheets is exactly the same.

Thanks for sharing and best regards

Dave White
ICAEW IT Faculty member and all-round ancient Excel nut

 

 

Thanks (1)
avatar
By dringstrom
20th Feb 2015 23:19

Excel 2013 doesn't have Scrollbar splitter

Great article, John...just a couple of thoughts:

The scrollbar splitter was removed in Excel 2013. One must click the Split command on the View menu instead, and then double-click on whichever split bar isn't wanted to remove it.

F6 does move between windows, but I prefer Ctrl-Tab. However, you can't use Ctrl-Tab in Word, and must use F6, so at least F6 gives you a consistent keyboard shortcut across the Microsoft applications.

I am happy to report that Microsoft will have to eat at least one hat, as there wasn't anything new to me on the list. 

Thanks (0)
avatar
By edhy
21st Feb 2015 15:26

Shortcuts, Great tips?

Shortcuts should not be classified as great tips.

Great tip is something one does not realize useful in some other way at first sight, The good example is given by Dave, till now for me pasting bitmap was to embed pictures only.

Zubair Edhy

 

Thanks (0)
avatar
By listerramjet
22nd Feb 2015 10:02

don't you just love excel tips!

If you put everyone's tips into a book, I suppose you might have a (very large) manual.  We all know that manuals are things that no one ever reads!

I love excel to bits - have been using it for years, but still stumble on something new every few months - which I use once and then quickly forget.  Which leads to my biggest gripe about Excel - why is it so unintuitive?  At least we have the best excel tip in the world - google!

BUT what I get most from excel is when I find a new solution to a problem, using features in (to me) novel ways.  

Thanks (0)
avatar
By alan.falcondale
23rd Feb 2015 12:57

shortcut/tips

It's great when you get shortcuts that make you appear God-like to your colleagues...I bask in the glory but do share all the tips I get which kind of reduces my glow somewhat.

Ctrl+@   copies the contents of the cell above

Select a range of data or just select one cell of a group of data...press F11 to get an instant chart of basic settings (looks good when director asks "what would this look like in a pie chart?" and then BAM!!! a chart appears immediately)

To have the same or similar heading fields over multiple worksheets - select all of the worksheets and then just type the data in on the current worksheet. voila - time saving typing or cut and pasting on all other sheets. Just change the unique piece on other sheets to suit

 

Rightmouse the sheet navigation arrows in the bottom left to see a list of available sheets in the workbook.

Thanks (0)
Replying to Matrix:
Morph
By kevinringer
23rd Feb 2015 13:26

CTRL+D

alan.falcondale wrote:

Ctrl+@   copies the contents of the cell above

CTRL+D is similar except CTRL+@ is in input mode so you can add more text or hit F2 to edit. Useful when inputting a column of items which don't auto-prompt eg dates and amounts.

Thanks (1)
avatar
By alan.falcondale
23rd Feb 2015 15:05

not only but also

Ctrl+;   enters the current date

Ctrl+:  enters the current time

 

Using these keystrokes you could enter both date and time into the current cell relatively quickly (good if you need to keep track of data entry)

Ctrl+; space Ctrl+:

results in:

23/02/2015  15:05:00

Thanks (1)
avatar
By SeaPea
23rd Feb 2015 21:12

Microsoft "reveals" little

No reflection on AcctgWeb, but as usual Microsoft did not reveal much. I learned more from the comments than the article. Thank you to everyone with tips in your comment.

"Jump to the last cell" was the only thing useful, and thank you for it.

F6 just closes my spreadsheet. I wouldn't call it cycling through, more like cycling out, with or without split screen activated.

Ctrl-] isn't as useful as 'trace dependents' on the Quick Access Toolbar.

I knew about the Transpose formula but Paste Special is easier.

I use Alt-down arrow all the time!  I LOVE Excel shortcuts! 

Thank you AcctgWeb and everyone for your contributions.

Thanks (0)
avatar
By alan.falcondale
24th Feb 2015 14:23

Last one...honest

Perplexed by numbers that just don't seem to 'fit the norm' when looking at a whole bunch of 'em?

Use Ctrl+`  (that's the small 'apostrophe' usually on the top left key of the keyboard and below the Esc key)

 

Using that key combination switches between formula and results mode so you can see if any of the numbers have actually been hard typed rather than letting the formula work it out.

Use the same combination Ctrl+` to switch back to the results.

 

Thanks (0)
Replying to The Dullard:
avatar
By dringstrom
24th Feb 2015 15:46

The small apostrophe is the grave symbol

When I teach Excel classes I tell users Ctrl-~ as that's a more identifiable character. It's tough to remember every keyboard shortcut in Excel, so the alternative is to toggle Show Formulas on the Formulas tab of Excel 2007 and later. There's also the new FORMULATEXT worksheet function in Excel 2013 as well. But the formal name for the small apostrophe is the grave symbol. Now that's some trivia! :-)

Thanks (0)