Skip to main content

The Eight Most Impactful Excel Shortcuts That You Should Master

Image
By
fast-fingers-1529553-640x480
If you’ve ever gone online to research improving your Excel skills, you’ve undoubtedly come across a post or two listing all of Excel’s keyboard shortcuts.  In the latest version of Excel, Microsoft has made it easier than ever to learn shortcuts, by assigning shortcuts to nearly every function and making the discovery of the input sequence very transparent.
While memorizing Excel shortcuts will generally improve your productivity, not all shortcuts are created equal.  Shortcuts that you never use are not inherently not very useful and not worth memorizing.  Your focus should be on the shortcuts that have the most impact – either by the amount of time it saves you, the frequency that you’ll use them, or the behavior it encourages.
If you’ve already started using Excel or just haven’t utilized shortcuts heavily before, review the top eight shortcuts below.  For any that you don’t know already, I would suggest memorizing them and incorporating them into your Excel working style – I guarantee that doing so will provide an immediate boost to your productivity.

Sorting Tables

ALT → A → S → A    (Sort Ascending)
ALT → A → S → D    (Sort Descending)
Sort Shortcut
Knowing how to sort tables quickly is an extremely useful shortcut because of how often you’ll use it.  Sorting is generally the first task you’ll perform to orient yourself with data and easily see if there are duplicates within the field you just sorted.  Additionally, any time before you finalize a table to be presented to others, you’ll likely want it to have some type of sorting logic applied to at least one field.

AutoFilter

ALT → A → T
    or
CTRL + SHIFT + L
AutoFilter Shortcut
The AutoFilter shortcut is a powerful shortcut because, like sorting, you’ll use it frequently whenever you need to conduct data analysis.  With AutoFilter on, you can quickly limit your view of the data based on a set of filtering criteria.  Knowing this shortcut is also useful when you inherit a workbook and need to quickly remove AutoFilter, as leaving it on can easily cause errors.  Just type in the same shortcut sequence to remove the AutoFilter labels.

Inserting and Deleting Rows and Columns

ALT → I → R    (Insert Row)
ALT → I → C    (Insert Column)
ALT → H → D → R    (Delete Row)
ALT → H → D → C    (Delete Column)
Columns Rows Shortcut
As with any spreadsheet tool, Excel’s interface is intended to be extremely flexible, allowing you to place data, charts, tables, and even objects any way you like on the spreadsheet itself.  Because of this flexibility, one thing you’ll be doing often is inserting and deleting rows.  There have been countless times when I’d built a spreadsheet, realized that I needed either more or less space within the sheet, and manually performed the action by right clicking the mouse.  Had I been using these shortcuts all along, I would have saved myself a ton of time.

Inserting a PivotTable

ALT → D → P → F
    or
ALT → N → V → ENTER
PivotTable Shortcut
PivotTable is another great analysis tool that is sometimes underutilized either because people don’t know about the feature or they get confused by the steps required to insert a PivotTable.  Using this shortcut allows you to insert a PivotTable the quickest way possible.  By having this shortcut in hand, you’ll undoubtedly start using PivotTables more frequently and eventually become more proficient with the tool itself.  To prevent errors, just make sure that you are selecting a proper table (all headers filled with heading values, no random breaks of rows or columns) before you start the shortcut sequence.

Formatting Numbers

ALT → H → N → A → ENTER    (Accounting format)
ALT → H → K    (Comma separated format)
CTRL + SHIFT + 4    (Currency format)
CTRL + SHIFT + 5    (Percentage format)
CTRL + SHIFT + 1    (Two Decimal Place format)
Number Formatting Shortcut
If making your Excel files visually appealing is even slightly important to you, then changing theformat of number in Excel is something you’ll do constantly.  Using shortcuts is a way to make this process much more efficient.  There are a couple of other number formatting shortcuts available in Excel, but I’ve listed the ones you’ll likely use most frequently.
Note in the examples shown above that the first three formats add a small indent to the right side of the cell while the last two do not.  This is one difference between the comma separated and two decimal place format.

Inserting Comments

ALT → R → C
Comments Shortcut
I’ve stated before that not including documentation is one of the biggest mistakes people makeboth when creating and reviewing Excel files.  Always put yourself in the shoes of the person you may be handing off your file to and think about what information would be important to that person.  The comments shortcut allows you to add valuable context to your file without interfering with any of the data elements.  By knowing the shortcut combination, you’ll start to add comments more frequently and the workbooks you build will become more transparent.

Workbook Navigation

CTRL + PAGEDOWN    (Select next tab)
CTRL + PAGEUP    (Select previous tab)
Tab Navigation Shortcut
During my consulting career, there have been countless times when I’ve been I’ve been tasked with reviewing a client’s Excel workbook to prepare for a meeting.  Because of the nature of my work, these workbooks would often have a ton of tabs.  It was important for me to be able to get through the workbook quickly, and doing so with shortcut keys was always faster than using the mouse.

Cell Selection

CTRL + DIRECTIONAL KEYS    (Move to end of filled in values)
CTRL + SHIFT + 8    (Select entire table based on current cell selection)
SHIFT + SPACE    (Select entire row)
CTRL + SPACE    (Select entire column)
Cell Selection Shortcut
Similar to moving between tabs, cell selection, or moving around within the tab itself, is also a very important skill to have.  Being able to navigate and select cells quickly will improve both your efficiency at Excel and your confidence in using the tool.  Additionally, if you’re ever put in a situation where your colleagues are watching you use Excel, it’s a great idea to have these shortcuts handy if you need to impress them.
Coming up with just eight Excel shortcuts is difficult because there are so many good ones out there that were left off of this list (like ALT → E → S → V → ENTER).  What are your favorite, most impactful, Excel shortcuts?  Let me know in the comments section.

Comments

Post a Comment

Popular posts from this blog

How ad-free subscriptions could solve Facebook

By
At the core of Facebook’s “well-being” problem is that its business is directly coupled with total time spent on its apps. The more hours you pass on the social network, the more ads you see and click, the more money it earns. That puts its plan to make using Facebook healthier at odds with its finances, restricting how far it’s willing to go to protect us from the harms of over use. The advertising-supported model comes with some big benefits, though. Facebook CEO Mark Zuckerberg has repeatedly said that “We will always keep Facebook a free service for everyone.” Ads lets Facebook remain free for those who don’t want to pay, and more importantly, for those around the world who couldn’t afford to. Ads pay for Facebook to keep the lights on, research and develop new technologies, and profit handsomely in a way that attracts top talent and further investment. More affluent users with more buying power in markets like the US, UK, and Canada command higher ad prices, effectively...
1 comment
Read more

Here’s how to keep track of Elon Musk’s Roadster and Starman in space

By
Elon Musk’s Starman, the mannequin driver of the Tesla Roadster SpaceX launched aboard its Falcon Heavy rocket, is taking a trip around our solar system, in a large elliptical orbit that will bring him relatively close to Mars, the Sun and other heavenly bodies. But how to track the trip, now that the Roadster’s onboard batteries are out of juice and no longer transmitting live footage? Thanks to the work of Ben Pearson, a SpaceX fan and electrical engineer working in the aerospace industry, who created ‘Where is Roadster,’ a website that makes use of JPL Horizons data to track the progress of the Roadster and Starman through space, and to predict its path and let you know when it’ll come close to meeting up with various planets and the Sun. The website tells you the Roadster’s current position, too, as well as its speed and whether it’s moving towards or away from Earth and Mars at any given moment. It’s not officially affiliated with SpaceX or Tesla, but it is something Elon...
Post a Comment
Read more

Engineering against all odds, or how NYC’s subway will get wireless in the tunnels

By
Never ask a wireless engineer working on the NYC subway system “What can go wrong?” Flooding, ice, brake dust, and power outages relentlessly attack the network components. Rats — many, many rats — can eat power and fiber optic cables and bring down the whole system. Humans are no different, as their curiosity or malice strikes a blow against wireless hardware (literally and metaphorically). Serverless software deployment to the cloud, this is not. New York City officially got wireless service in every underground subway station a little more than a year ago, and I was curious what work went into the buildout of this system as well as how it will expand in the future. That curiosity is part of a series of articles I’ve written on an observed pattern known as cost disease, the massively inflating costs of basic human services like health care, housing, infrastructure, and education. The United States spends trillions of dollars on each of these fields, massively outspending sim...
Post a Comment
Read more

South Korea aims for startup gold

By
Back in 2011, when South Korea won its longshot bid to host the 2018 Winter Olympics, the country wasn’t widely recognized as a destination for ski and snow lovers. It wasn’t considered much of a tech startup hub either. Fast forward seven years and a lot has changed. For the next 10 days, the eyes of the world will be on the snowy slopes of PyeongChang. Meanwhile, a couple of hours away in Seoul, a burgeoning startup scene is seeing investments multiply, generating exits and even creating a unicorn or two. While South Korea doesn’t get a perfect score as a startup innovation hub, it has established itself as a serious contender. More than half a billion dollars annually has gone to seed through late-stage funding rounds for the past few years. During that time, at least two companies, e-commerce company Coupang and mobile-focused content and commerce company Yello Mobile, have established multi-billion-dollar valuations. To provide a broader picture of how South Korea stacks ...
Post a Comment
Read more

The CC Aurora is actually pretty fun, as far as projectors go

By
I don’t review projectors. Projectors are boring. Even the good ones. They remind me of vacation slideshows and the film strips we had to watch in health class — neither of which I’m in a particular hurry to revisit in my adult life. That said, I’ve always harbored some germ of a notion that some day I might buy one, to compensate for being one of those weirdos without a TV set. There’s something undeniably appealing about a big screen TV you can break out during movie night and then stash back into the closest of your one-bedroom New York City apartment. XGIMI’s CC Aurora is the closest I’ve seen to fitting the bill — or, for that matter, being a projector that I could actually muster any reasonable amount of excitement about. From the looks of it, it’s kind of the perfect package for the apartment dweller: it’s compact, self-contained with a built-in speaker system and plays nicely with mobile devices. Clearly I’m not alone here, either. The product scored $170,000 on Indi...
Post a Comment
Read more