How to make Excel spreadsheets as neat and tidy as possible

Welcome to your weekly 3 tip Friday update, it’s just a short dose of Excel help before you start the weekend. Each week we cover a tip, a shortcut and a function - making it super easy for you to get better at Excel.

If you find this useful or have requests or ideas for future editions, send me a message or comment at the bottom of this post! Now, on with the tips. 

A quick win you can learn today -

When I’m presenting spreadsheets to an audience, often senior management, I try to make them as neat and tidy as possible. 

One way to do this is to hide grid lines. Go to the View menu ribbon and uncheck the grid lines box - you have to do this on each sheet that you want to apply it to. 

Another method is to shrink the sheet area to just those cells you want to show, by hiding unused rows and columns. 

In the first available column, select the column and Ctrl+Shift+Right Arrow to select to the far right of the sheet, right click and hide columns. Do the same for rows by selecting the first blank row and Ctrl+Shift+Down Arrow, and hide the unused rows. 

This way you can present a spreadsheet to fit the data on display, it looks much tidier.

A keyboard shortcut I'm adding to my list - 

I think I’ve cheated a bit here by including a couple of keyboard shortcuts already - so let’s stick with the arrow keys, which can be super useful for productivity. These are commonly known shortcuts but in case you don’t know these already:

Ctrl+Arrow Key will make the selected cell jump to the final non blank cell in the Arrow direction. 

Ctrl+Shift+Arrow Key will select from the current cell to the final non blank cell in the Arrow direction. 

So, one easy way to select an entire data table is to select the top left cell and use Ctrl+Shift+Right followed by Ctrl+Shift+Down. This will work well so long as there’s not gaps in your table. 

A function I've found super useful this week - 

I’ve recently been creating IF statements that are reliant on more than a single criteria - for example, add a Yes flag if Sales in column A > 1000 AND Product Category in column B =‘Office Furniture’. 

One way to do this would be with nested IFs, for example =IF(A2>1000,IF(B2=“Office Furniture”,”Yes”,”No”),”No”). 

Nested IFs are cumbersome and slow to process. Using AND would make this much simpler:

=IF(AND(A2>1000,B2=“Office Furniture”),”Yes”,”No”)

AND creates a true or false outcome based on if all of the criteria are met. Worth giving it a try!

 

Hope these were useful, until next time all the best. 

 

- Martyn Blythin'

Edinburgh, Scotland

May 2018

Get More Exclusive Content

Sent Directly to your inbox with Excel 3 Tip Friday. A weekly dose of Functions shortcuts and tips for getting more out of Excel.

We won't send you spam. Unsubscribe at any time. Powered by ConvertKit