Boost Your Excel Skills: Top 3 Tips You Can’t Miss

In this blog we’re going to talk about a three simple tricks in Excel which might make your life as an Excel user easier. You may have used these before, or you may have never heard of them, but applying them consistently will make a few of those simple tasks just that little bit easier.

Ctrl + Arrows

Ok first one, this trick is favorite of many Excel MVP’s as well. The concept is simple. In a big table it’s sometimes hard to find the last row or column, and any actions you do on the table can be a bit tedious/time intensive. Holding down Ctrl and using the arrow keys allows you to jump seamlessly to the edges of the range. This is already super helpful especially when trying to understand something someone else made, but there’s more.

Adding in the shift key allows you to select the entire range! For example, if you want to select all the cells in a certain column of a table, just select the top cell, hold down Ctrl + Shift, and press the down arrow. This will select all the adjacent cells in that column, if there are spaces or empty rows you can press the down arrow again to jump further down.

Now that you have a whole range selected you can do all kinds of cool things. The information at the bottom right of Excel will tell you what the SUM, AVERAGE, and COUNT of the selected cells are.  You can apply a format, or remove the range, whatever you need to do. This trick also works in formula edit mode! If you want to do a sum of a full range, select a cell, and CTRL + Shift + Arrow in the direction you want to select. If you didn’t know this before, it’s like magic! Try it out and let us know what you think.

View Gridlines

This is a simple one, but it will make your first sheet, or documentation sheet look so much better! Some Excel MVP’s even claim you should always have gridlines turned off. Gridlines are helpful for identifying which cell you are selecting, but let’s be honest, they don’t look great. If you’re using table formats and your data is structured considering turning off gridlines. Simply navigate over to the view tab in the ribbon and uncheck the box Gridlines.

This is just one of several great visual upgrades you can do when working with an Excel sheet. A few other to try are:

• Freeze panes: This keeps certain rows or columns in view even when scrolling left or right on a sheet.
• New window: Super helpful if you’re updating inputs and trying to see the impact on outputs. New window simply opens another view of the same spreadsheet, so if you update an input cell on one sheet you can instantly see what changes on another sheet.
• Zoom to Selection: It’s in the title, but if you’re zooming out to find something on a big/busy sheet, then this can be helpful to zoom back in to the section you’re actually wanting to look at or change.

The way a user experiences a spreadsheet is in some cases just as important as the contents of the spreadsheet.

Format painter

I hope you’ve found/used the format painter before. This  feature simply copies the format of a cell (not the contents) and lets you paste it somewhere else. For example, some spreadsheets have a lot of totals/subtotals which should all have the same format so they are easy to spot. What you can do is format one cell just the way you want it, then use the format paints on the home tab to copy that format and paste it somewhere else.

This is super useful, but what many users don’t know is that it’s much more powerful than that. If you are an avid user of the function you might know that when you use it you almost always want to use it more than once, re clicking it every time can be a hassle. The less known trick is that you can double click on the format painter to lock it meaning you can select multiple ranges or cells and give them all the format of your source cell in seconds!

This is one of those tricks that might not have a huge impact, but totally makes you feel like a hero, and when you tell your colleagues you will certainly blow their minds. Such simple things can make us feel so good when we apply them successfully.

Conclusion

All three of these tricks are simple and easy to share with Excel users at any level. If you’re looking for some more heavy duty functionality, PerfectXL offers an extensive audit suite with tools to help you unravel, repair, and understand even the most complex Excel models. Contact us to find out more about what we offer, and don’t forget to mention us when you share these Excel tips with those around you!