If you've been following me for any amount of time, you know I'm all about learning how to use your tools, like Microsoft Office, to save yourself time and become more productive. Tools aren't meant to slow you down, but I see it too often where people struggle and waste time because they don't know how to do things properly or they don't know how to use certain features and functions in their tool - just like Excel.
So, I've put together this list of 3 Excel Tips that will Save You Time!
1) Delete Blank Rows - with the Go To Special function
I've shown various ways to do this in my previous posts and videos, but here's a simple way that everyone can learn.
Some of you may know about the Go To function, Fn F5, but the Go To Special function has many additional features to help you - including the "Blanks" option shown here.
You can use the Fn F5 or you can add the Go To Special option to your Quick Access Toolbar. If you use the Fn F5, simply click "Special" to open the above dialog box.
Select Blanks and click OK.
This will locate and highlight all Blanks in your Excel spreadsheet.
Now, you can do whatever it is you want to do with the Blank cells all at once!
I've worked with thousands of spreadsheets over the years, along with the thousands my students have had me help them with, and all too often, it seems like people want to either Delete these Blank Rows of data or highlight them in some way.
If you want to highlight them, you can simply select the Format or Highlight Style you want from the Ribbon as the Blank Rows are already highlighted from our actions we just completed above.
If you want to Delete these Blank Rows, simply Right-Click on one of the highlighted selections and select Delete and in the pop-up dialog, select "Entire Row." If you only select "Shift Up," you will alter your database, so be careful and make sure you know what you are trying to delete.
2) Freeze Panes
When using Excel for large data files, it can be very easy to get lost. I've shown thousands of people to use Freeze Panes, but still see people scrolling up and down to remember what data they are looking at in a particular column.
To use Freeze Panes, follow these simple steps:
Place your cursor in the cell directly below the row you wish to freeze and directly to the right of the column you wish to freeze. Let's say you wish to freeze Row 1 (Header Row) and all columns to the left of Column D ("Discount Band"). In this case, the cursor would be placed in Cell D2.
Click the "View" Tab and click Freeze Panes. You have some options, so in the image below, it shows clicking the Freeze Panes drop-down arrow. The first option is the Default and will Freeze Cells to the Left and Above where your cursor is placed.
This example would freeze Row 1 and Columns A,B, & C.
As you scroll through the data, you will always see those cells for reference.
If we scroll down to line 438, you'll still be able to see the Header Row to know what is contained in each column of that row's data.
In this example, we can easily see that Column H is the Gross Sales, which is $724,850 for this segment (Line 438).
It's so simple, so you really need to start using this function and save yourself time from scrolling up, down, and over in your spreadsheets.
3) Quickly Sum Numbers
I think the most common function I see students using is the =SUM function because often our spreadsheets contain data that needs totaling.
Many people either type =SUM and then the rest of the function (the range of cells they need to total) OR they click the "AutoSum" function on the "Home" Tab.
Often a faster way of summing numbers is to never remove your hands from the keyboard.
To do this, simply place your cursor in the cell you want to place the =SUM function and press ALT + to entre the =SUM formula. Press ENTER as you normally would and you've just saved yourself maybe 5 seconds for every single time you enter the =SUM formula for all of your spreadsheets. Remember, it's the little things... Save a few seconds here and there and before you know it, you've saved an hour!
I hope you start using these time saving Excel tips because they really will make a difference in your productivity.
If you'd like to find more time saving tips, download my "65 Ways to Use MS Office to be More Productive" eBook.
