Share via

My 3 favorite timesaving tips for Excel

  • Article

I have a love/hate relationship with Microsoft Office Excel. It's a great tool for so many things—analyzing data, organizing a lot of information, and keeping track of details are just a few things that come to mind. But it's not always easy for a devoted Microsoft Office Word user to become skilled at a spreadsheet program. I'm getting there, and slowly learning the true power of using Excel and how much it can really help me. In my effort to become more proficient in Excel, I've spent a lot of time on the Excel 2003 Assistance site. In addition, here are some of my favorite Excel tips that have helped save me time.

1. Hide rows and columns

Too often the spreadsheets I use look like just a mass of data. To make the information easier to digest, I like to clear the information I don't need to see. Oftentimes, the data I'm interested in might just be in two or three columns. In those instances, I find it much easier to hide and unhide rows. It's also a huge timesaver if you're inputting data into just select columns.

To hide a column:

  1. Highlight the columns you want to hide. In the image below, I want to hide Columns B through F.

    Image of Excel spreadsheet with columns highlighted

  2. On the Format menu, point to Column and click Hide.

    Image of Excel spreadsheet with procedures circled

  3. Your columns should now be gone.

    Image of Excel spreadsheet with columns hid

To unhide the columns:

  1. Select the columns adjacent to either side of the columns you want to display. In our example, highlight columns A and G.

    Image of Excel spreadsheet with columns highlighted

  2. On the Format menu, point to Column and click Unhide.

To hide and unhide rows, take the same steps as above but highlight rows instead. And on the Format menu click Rows.

2. Filter data

Another great way to reduce the amount of data you see and work with is to filter your columns. A filtered range displays only the rows that meet the criteria you specify. For example, I can filter my columns so that I only see data relevant to a certain person or place. It's a great way to condense a large spreadsheet and get only the targeted information you need. It's also extremely easy.

To filter your columns:

  1. Create or start with a spreadsheet with clearly marked column headings. The example below has columns for Place, Date, Time, and Person.

  2. Highlight the data you want to filter and on the Data menu, point to Filter and click Autofilter.

    Image of Excel spreadsheet with highlighted columns and procedures circled

  3. AutoFilter arrows will appear to the right of the column labels in the filtered range.

    Image of Excel spreadsheet with filtered columns

  4. Click the arrows to select the criteria on which you want to filter the data. For example, if I want to filter on all the information on Seattle, I click the Place arrow and choose Seattle.

    Image of Excel spreadsheet with filtered column being selected

3. Lock columns and rows

If you want to freeze a row of titles or categories so that you can keep them in sight as you enter data further down your spreadsheet, Excel 2003 has a handy Freeze Panes function. For example, if I'm looking at a budget spreadsheet, I keep the results budget projections locked in one pane so I can see the bottom line impact of expense and revenue forecasts I make in another pane.

To lock a pane, use your mouse to select the row below or the column to the right of where you want to freeze. On the Window menu, click Freeze Panes.

—Jason Kozleski

Comments

  • Anonymous
    April 25, 2006
    good , nice suggestion , it will help me in formatting my excel documents

  • Anonymous
    April 26, 2006
    this tips are great, especially the filters one.

  • Anonymous
    April 26, 2006
    Just great.  Use Excel often and this info was new to me and will be useful from now on.  Thanks so much.  

  • Anonymous
    April 29, 2006
    URL?? forgot that when i didn't need to rember.
    am still 'baby'..that's why i am on this site.
    enuff..of that.
    i use excell for many things,
    at present for a list of
    1)my vcr tapes
    2)list of tv series.
    3)episode guides
    4)phone # list
    5)bill records.
    etc.
    am i 'miss' using it?

  • Anonymous
    April 30, 2006
    After looking at your excel tips. That's so great.  I hope to use this feature on my next spreadsheet task.  I have really enjoyed reading your website and all the benefits it has to offer users.

    Thanks so much.

  • Anonymous
    May 01, 2006
    great.. the fileters tip is very use ful..

  • Anonymous
    May 03, 2006
    I've used the "hide" feature, but when I highlight the adjacent rows and click "unhide", nothing happens??????     I've tried closing the document and reopening, logging off and back on again, and trying it on different days.  I assume because the "unhide" feature is still visible, the information is still there.  Any ideas???/

  • Anonymous
    May 04, 2006
    Actually I think those are just a few of some of the best ways to get the most out of Excel.
    Don't forget that if you tend to hide/unhide the same columns/rows quite often to save them as a "custom view" (Views|Custom Views). Then, if you add the CustomViews drop down to a toolbar (Tools|Customize)you can quickly go from one view to another without selecting.

    Personally, my favorite 3 "tips" are:
    1- Ctrl+; to insert todays date as a value
    2- F4 key in a formula to make the cell reference "absolute"
    3- F4 key elsewhere in Excel, Word or PowerPoint to "repeat" the last command.
    Thanks for sharing. -Q

  • Anonymous
    May 09, 2006
    I learned these tips along time ago and they have help me greatly in asset management and to be used for quick referencing items.  They are great!!

  • Anonymous
    May 21, 2006
    Thanks for the tip, id like to see more likt this ... Also remember, using the "TAB" key saves alot of time in excell.

  • Anonymous
    May 24, 2006
    Its very usable on your tips but Im wondering what bout page merging,2 pge in 1 worksheet?

  • Anonymous
    May 27, 2006
    I find that Excel and Access love to communicate. If you have Access, it's worthwhile to learn how to store your data there, and then to export a query (slice or view) of your data into Excel where you can analyze it without being overwhelmed by amounts of data you don't need. It also works to set up an ODBC connection to your access database. That way you can see and work on your data in real time right from Excel. See OBDC Connections in Control Panel, and see Excel Menubar, Data/Get External Data/New Database Query. It looks hard at first. It's not!

  • Anonymous
    July 18, 2006
            to chery kay ,if you want to hide the cellsgo to the formating bar choose windows then on drop down menu choose hide.click it and voila!! they r hidden. to unhide go to the formating bar, windows then click unhide. bye  if you don't understand contact on www.nalian05@yahoo.co.nz see ya

  • Anonymous
    November 08, 2006
    Good stuff but by far the fastest tip I have ever used was to modify the toolbars at the top. In two rows I can have 90% of what I use so I don't have to go searching through menus.  Make sure you back it up too.

  • Anonymous
    May 31, 2009
    PingBack from http://woodtvstand.info/story.php?id=7004

  • Anonymous
    June 02, 2009
    PingBack from http://woodtvstand.info/story.php?id=48192

  • Anonymous
    June 16, 2009
    PingBack from http://workfromhomecareer.info/story.php?id=2171

  • Anonymous
    June 16, 2009
    PingBack from http://topalternativedating.info/story.php?id=8323