Tag Archives: group

More Essential Excel Keyboard Shortcut Combos

When you get used to using keyboard shortcuts with Excel, you start to realise that you use some shortcuts together all the time. We talked through my Top 5 Excel Keyboard Shortcut Combos before, but what other combos are there? What narrowly missed the (short)cut?

1. Change font type and font sizeAlt+HFF [type font name], Alt+HFS [type font size]

Sometimes I forget to paste formats, or notice that my worksheet looks messy due to different font types and font sizes that may have been pasted in over time. I often find myself using the shortcuts above (usually with something like Arial and then 10), to quickly tidy things up.

2. Ungroup all grouped rows and columnsCTRL+A, Alt+AJ

Every now and then I inherit a spreadsheet from someone else that has clearly had a lot of work go into it. They’ve spent a lot of time building something really in depth – so much so that they’ve made it more concise by grouping columns and rows together so you just see the key information. But I need to understand how it works, so I have to go through and unhide all of the grouped columns, group by group. This shortcut unhides all of these groups in one fell swoop (N.B. it’s not for unhiding hidden columns).

3.  See what part of a formula works out toF2, [optionally select part of the formula] F9, Escape

Big formulas can be hard to get your head around, and if they’re not working you need to break them down to see where the problem lies. F2 dives straight into the formula – you may then choose to highlight a particular element of it that may not be working. Pressing F9 will calculate just that one part of the formula. Often I will have each part calculated so I can see the big picture and check that my logic is correct. Pressing Escape is important as it means you exit the formula without changing it – otherwise it stays with all of its parts calculated and is no longer dynamic.

4. All borders, with a thick border around the outsideAlt+HBA, Alt+HBT

I think small tables of data look tidy with thin borders all over, with thick border around the outside. This combo does that really quickly.

5. Close a workbook and re-open itAlt+FC, Alt+FR1

Sometimes if I make a mistake and want to go back to the latest version quickly without saving, I’ll want to quickly close and re-open the workbook I’m in. Alt+FC closes the workbook (if you’ve made changes press N at this point to quickly tell Excel not to save them – be sure that you really want to do this), and Alt+FR1 opens the most recently opened file. Clearly if it was the second last workbook that you opened, you’ll need to press Alt+FR2.

So there you have it – five more easy but useful shortcut combos.

HR Dashboard Tip: Dealing with Charts, Pictures & Shapes in Excel

 

A good HR dashboard or Balanced Scorecard is clear and well presented conveying a snapshot of key data quickly. Charts are one way of doing this, and you may want to use pictures or objects to be consistent with corporate branding. And if you’re using lots of pictures, charts or other objects in Excel (and especially if you’re hiding them or they’re overlapping) things can get a bit fiddly.

Handling Lots of Objects
That’s why Excel have provided us with the Selection and Visibility pane. It lists each of these objects and allows you to quickly select them or hide / unhide them. It also works for grouped objects…

[showmyads]

Grouping Objects
Say you had several objects that work together – a chart, surrounded by a background picture, with an explanatory text box and topped off with a word art title. Once they’re all positioned correctly in relation to each other, you can group them so that are easy to move around all at the same time. To do this, select the first one with your mouse, then keep holding down control and select all the other objects that you want to group together. Once you’ve selected them all you can let go of CTRL. Right click one of your selected objects and select Group then Group again.

[showmyads]

A Handy Shortcut…
Remember that Selection and Visibility box we mentioned earlier that you can use to handle lots of pictures? To bring it up quickly the shortcut is Alt PAP. How do I remember this? Who takes lots of pictures? Paparazzi – also known as PAPs! So if you need pictures use Alt PAP (there’s plenty more Keyboard Shortcuts where that came from…).

Hopefully these tips will help save you time, but don’t forget you can get more tips on this site, or by liking XLCalibre on Facebook or following @XLCalibre on Twitter.

New XLCalibre Group on LinkedIn

If you an HR professional and use Excel and LinkedIn, come join our group on LinkedIn.

Its a forum for you to ask questions and share tips with other HR professionals on how you can best use Excel in your work. We’ll need to authorize your request to join, but only to minimize spam.

So come join the XLCalibre group and learn something new about Excel!