Six clicks: Excel power tips to make you an instant expert

Six clicks: Excel power tips to make you an instant expert

Summary: Excel isn't just for number crunchers with accounting degrees. In this gallery, I present six of my favorite hidden features to make you more productive when working with Excel spreadsheets.

SHARE:

 |  Image 1 of 7

  • Thumbnail 1
  • Thumbnail 2
  • Thumbnail 3
  • Thumbnail 4
  • Thumbnail 5
  • Thumbnail 6
  • Thumbnail 7
  • Tips and tricks for Microsoft Excel

    Spreadsheets don't have to be ugly, and the process of creating a list or analyzing numbers doesn't have to be tedious. In this gallery, I present six shortcuts and hidden features that can make you more productive in Excel 2010 and Excel 2013.

  • Convert formulas to their results, instantly

    This is probably my single favorite Excel shortcut.

    Worksheets commonly contain one or more calculated columns: averages of a range of data, converted text, and so on. What if you want to eliminate those formulas and convert the contents of the column to the results?

    The secret is to copy the range you want to convert and then use the Paste Special function (Ctrl+Alt+V), which has an option to paste just the values from the formulas you copied.

    If you do this often enough, you can even streamline the process with keyboard shortcuts. Select the range to be converted, and press Ctrl+C to copy it to the Clipboard. Now, without changing the selection, press Alt+E, S, V, and then press Enter. The formulas in the selected range are now replaced with their results.

Topics: Tapping M2M: The Internet of Things, Microsoft, Software

Kick off your day with ZDNet's daily email newsletter. It's the freshest tech news and opinion, served hot. Get it.

Talkback

35 comments
Log in or register to join the discussion
  • Pivot tables

    To take raw tabular data and cube it into meaningful bits. On the Insert tab on the far left.

    You drop the fields you want to slice and dice into the "Row labels" and "column labels" bucket, and the field you want to sum into the "Values" bucket.

    Has saved me a lot of SQL overthinking, just to see data quickly.
    Mac_PC_FenceSitter
    • "Pivot tables" is a cute, but very non-descriptive and confusing name...

      ...for a great feature. If it were named "Data Summarizer" or something like that, maybe more people would look into using them and finding out how great they are.
      Userama
      • Goes way back

        That term goes way back, and it make sense if you've done a lot of database. Normally a table or query result has fixed columns with rows of data. You can only aggregate down combining rows using GROUP BY with sum, count, whatever. With the pivot you've turned that so you can aggregate to the right using data values and ranges to create columns.
        Buster Friendly
        • Thanks for the history

          I wondered why it was called a pivot table.

          Data summarizer is a good name.
          otaddy
          • It's just less accurate

            Trained users that know exactly what pivot means from their classes. Summarizing would also apply to a lot of other methods.
            Buster Friendly
    • PivotTables are awesome

      I use them all the time, but there is no way I could do justice to them in the space available here. That's why I didn't include them.
      Ed Bott
  • brain pain

    I have huge respect for the guru's of the spreadsheets. I see the people at work work magic on these things and it boggles my brain. I guess you would have to do this type of work every day, all day to get these. I actually rarely have to even open one.
    Godmocker
  • "...my favorite hidden features..."

    That, in a nutshell, is the problem with Office. Too many features are "hidden"---unless you're a "guru". Excel is a great app, but I have the feeling that no one person in the whole world is familiar with ALL of it's features.
    Userama
    • I don't know of any useful product where that's not true

      Even the revolutionary, magical, easy-to-use iPad is packed with hidden features. Try searching for "hidden iPad features" sometime. You'll be amazed or amused.

      The thing is, all of those products reward continued learning and discovery. You start with the basics but keep learning. After 20 years I still learn new stuff occasionally.
      Ed Bott
    • Despite the title...

      ...none of these features are actually "hidden". It's not like any take some Nintendo cheat code (e.g., up, down, start, down, down, left) to use or even find. What would you propose - all the features be placed on the toolbar? You would need 24" monitor in portrait orientation, and even then, you'd be left with three or four rows for data entry.

      Never lament getting more than you can/will use unless those functionalities preclude you from doing something you need to do.
      Nierteroth9
    • With the ribbon, they are even less hidden

      It's a complicated tool, but I think MS does a good job with it.

      Yeah, I get frustrated here and there but it is an essential tool for my work.
      otaddy
      • not really

        The ribbon only makes items more visible in the currently selected ribbon tab. Everything in other tabs is as well-hidden as before. And now that Office defaults to the ribbon being autocollapsed, it's a worse menu than Office 2003 and prior.

        Quick: where do you find the command to unhide hidden sheets in Excel?
        hrlngrv 
        • I had to look it up, I don't use it often.

          One goes to 'Format' on the 'Home' tab of the ribbon, there you can find the hide/unhide command.
          dhays
          • Right Click

            One can also right click on the tabs and it comes up in the popup menu. Choose Unhide and it will give you a list of what's hidden.
            rtshort
    • I know some people

      I know some people that I bet know every little feature. I don't because I only need to know enough to make sure my code is doing what I expect to the database. The business analysts are the people that really get into all the advanced features.
      Buster Friendly
  • something new

    great tips! Thanks for sharing.
    djohn017
  • Good start, Ed...

    ...but many of these are fairly basic in terms of "raw" power. Even without getting into the possibilities of using VBA (Visual Basic for Applications), the built-in formulas can be put to great effect. I use Excel daily to slice/dice/summarize/organize/analyze huge data sets. Much as FenceSitter opined, it's often quicker and more intuitive than configuring, populating and querying a database, especially when dealing with frequently ad hoc data needs.

    My contribution to the "hidden" tricks goes out to VLOOKUP users - Learn how to use the INDEX() and MATCH() functions, separately and together. I can virtually guarantee you'll never go back to VLOOKUP, with all its limitations and overhead.
    Nierteroth9
    • Also...

      ...it should be noted that there is a wealth (!) of information on the web for any given Excel feature, both from MS and from the user community. Pick a feature/formula, search it and dig in!

      I'm constantly amazed at how many of my colleagues use highly manual and error prone methods to work with data. They are amazed, in turn, when I replicate their activities in minutes vs. the hours they were spending.
      Nierteroth9
      • I think it is because they are afraid

        To go down a different path. It takes an investment up front. And there can be little pitfalls and mistakes made early on.

        Rather, they go with the manual way because they know that in the end, they will get their work done.
        otaddy
        • Undoubtedly true...

          ...but extremely short-sighted and significantly more at risk of error.

          One example... many people know how to work with VLOOKUP to link bits of data from one 'table' to another. But they frequently have to manipulate the tables to meet VLOOKUP requirements so that their reference values are to the left of the lookup values. And deal with the overhead when there are a number of columns between the reference column and the lookup value(s).
          Both of which increase the amount of time to get to the end result, and (in the case of rearranging columns) increases the chance of error/data loss/unintended results.

          Mastering the INDEX() and MATCH() function (used in tandem) replicates the VLOOKUP function without the limitations and overhead of column placement. With this combination, you can look "behind" the column you need to reference without moving it. And you can target specific columns for lookup without counting the offset from the reference column, resulting in significantly less processing overhead (especially when dealing with large data sets).
          Nierteroth9