what’s the coolest Excel trick you know?

by Alison Green on December 5, 2013

A reader writes:

From reading the comments on AAM, it seems many of your readers are Excel gurus. I use Excel a lot at work but feel like I’m only scratching the surface of what the software can do. I’m familiar with basic formulas (think =SUM and =AVERAGE) and recently learned how to subtotal and reference other worksheets, but what are some other tricks that readers find most useful?

I’m so glad you asked this, because I notice all the Excel experts here too and I want to know what they know.

We had a post early last year about how to get Excel skills, but I like this better: Readers, tell us the Excel tricks you find most helpful!

You may also like:
{ 526 comments }

{ 526 comments… read them below }

  1. Apollo Warbucks*

    I’d recommend taking a look at vlookups and pivot tables they are really helpful the sumif and countif functions they also handy to be able to use.

    2. kdizzle*

      vlookups are incredibly useful and easy to do. Each time I start a new job it seems that I end up showing people for the first time.

      “What strange magic is this? She’s a witch!” they say.

      1. Gail L*

        +1 ““What strange magic is this? She’s a witch!” they say.”

        I don’t use vlookup a lot, but the one time I did it was perfect.

      2. Carrie*

        I use pivot tables more than the vlookups, but totally agree… if you have any amount of data, they are absolute MAGIC!

      4. Greg*

        I agree that vlookups are powerful, but disagree that they’re easy. I find them incredibly non-intuitive, and still have to take a moment every time I do one and make sure I’m setting it up properly.

        Incidentally, you know that factoid that humans only use like 10% of their brain power? I don’t know if I believe it, but I totally believe that we only use 10% of Excel’s capabilities.

          1. David*

            Yes. YES. YYY… EEE…SSS… Fifteen years ago I used Excel using Macros to write my own accounts receivable program for my grounds keeping business. WOW. When I learned how to use an array formula, I was psyched to make all sorts of interactive data entry macros. I DID.

            But, sold the business, didn’t use Excel for years, and now, the only thing I remember about array formulas is that they need to be enclosed in brackets, AND, use cntrlEnter to activate.

            Yes, Excel is WAY COOL in what you can do with it but if you don’t….I don’t use it, I lose it.

            Wish I was an EXCEL Guru. I’m thinking I could get a fairly high paying job (75k-160k) if I could BLAST anything in Excel.

            2. eric*

              control + shift + enter for entering array formulas :)

              For myself, INDEX() and MATCH() make any of the pickurLOOKUPs() look like child’s play and are awesome for dynamically referencing in multi-dimensional arrays using multiple criteria.

        2. Tim*

          Vlookups are great when looking through a large database. If you take the time to set them up correctly then in just one cell you can type in what you wish to find and one of your functions (if more than one are necessary) will find the information you need.

      5. Michel*

        Forget about any LOOKUP formulas in Excel, or use it only exceptionally.
        I have seen so many people using these lookup and messing up everything.
        If you need that, then use a real database.

        Otherwise, ok for PivotTables.
        That’s the best part of Excel.
        I am used to say that good Excel files contain no formula and only pivottable or pivotcharts.
        (and lookups are formulas and are the worst of all)
        I do that myself, very often.
        The introduction of the Data Model in excel 2013 make PT even more powerful.

    3. Ellie H.*

      Vlookups changed my entire life. My boyfriend taught me a bunch of Excel tricks at the beginning of the summer when I started having to use Excel a lot more. I loved it and went on to explore a lot more on my own.

      My method for learning tricks is just to google “Excel how do you ___”. Sometimes you have to do it a few times before you figure out how to articulate what you want. I read a couple books too (of the Excel for Dummies variety) but I find that ad hoc trying to see if you can do something and then how to do it is more salient than reading it in the book.

      1. Fiona*

        Oh. My. God. I just Googled VLOOKUP and this does *exactly* what I’ve been wishing I could do with my parents’ small business invoicing for nearly 10 years. I’m so excited to play with this.

        1. Ellie H.*

          Aren’t they great?! I had been doing things like that more or less manually for some time and couldn’t believe how much time I could have saved.

      2. straws*

        For a minute, I was concerned that I was posting without my own knowledge. This is exactly me and SO true.

        I also love converting ranges to tables for the filtering functions. It’s handy when a pivottable is overkill.

            1. ExcelGuru*

              Assuming the first row of your range has column headers, highlight that row, and in the Data tab on the Ribbon click the Filter button (the big funnel).

    4. the gold digger*

      Remember with vlookup that if it isn’t an exact match, the formula will pick up the nearest match. I always run a check to make sure that I am picking up the right data.

      For example, if I want to break the world in to regions and I have a list of countries, rather than just believe the formula that the Dutch Antilles have $350K in revenue, I insert an extra column to look up the country name and then check for a match with an =if().

      (Yes, I know the Dutch Antilles are no longer a country, but my company has it in the database. And we have really dirty data, so I have to be very careful.)

        1. Khushnood Viccaji*

          And, if it doesn’t find a matching value from the lookup table, it returns the #N/A error, instead of an ‘approximate’ matching value (which could be disastrous, if you’re looking for ‘exact’ matches).

          1. mm*

            If you want to get rid of the sloppy looking #N/A you can combine vlookup with an “if” and value null. Example:

            =IF(ISNA(VLOOKUP(A4,$I$4:$I$9,1,FALSE)),””,VLOOKUP(A4,$I$4:$I$9,1,FALSE))

            I am a data analyst and I use pivot tables and vlookup on a daily basis.

            1. Len*

              It’s been mentioned below, but if you don’t care if you filter out all errors, you can just use the marginally tidier IFERROR() function in excel 2007+, as so:

              IFERROR(VLOOKUP(A4,$I$4:$I$9,1,FALSE),””)

              It’ll return the result of the vlookup function if it’s not an error, and otherwise it’ll return the second value.

        2. David*

          Whoa!!!! I don’t think I’ve ever entered “false” in a formula an knew what I was doing. So cool. I’m… or I should say, I was just on the cusp of really grasping the “false”/
          true thing, but then I stopped using excel.

          I’d love to go to an Excel summer camp. Is that weird or What?

      2. Darcy*

        Vlookup is only not an exact match if you use “true” for the range lookup. If you use “false” instead it will return on an exact match.

      1. Marcy*

        Pivot tables and Pivot charts are great for connecting to a large database and pulling out exactly what you need. My group uses these every day and when new data is added to the database, just click refresh in the pivot table and it comes right in.

    6. Emily K*

      Yes, yes, yes. I made a big workbook where I use one hidden sheet that contains all our businesses transaction, and every other sheet is a different pivot table with editable filters for viewing the transactions in a certain way, e.g. by day, by product type, against projections. The hidden sheet also uses formulas to create data columns, like a column that just contains the month of the transaction so that month can be a filter in the pivot table, or a column that just has the product prefix so you can subtotal by product type in the pivot table.

      If you want to be able to strip pieces out cells and combine them with pieces of other cells, I’d also familiarize yourself with the Right() Left() Mid() and Concatenate(). (Concatenate is perfect for turning First Name Middle Name Last Name Suffix into Name, for example.)

      1. tcookson*

        This sounds like what I was thinking about doing with my budget spreadsheet: having one page (not hidden, though) where I enter every single budget transaction, with columns including the cost center and category. Then having separate sheets which are pivot tables for each separate cost center. Each cost center has a certain budgeted amount, so I would want the pivot table for each cost center to show the running balance.

        Also, I would want to have separate sheets showing the projected monthly expenses for each cost center’s budget for a whole year (based on the prior year’s expenditures).

        I’m about to dig into this project as soon as things slow down at work. I think I can do it based on what I’ve learned about pivot tables. Does anyone think this isn’t the best way to go about it and if so, have a suggestion for a better way?

        1. Julie*

          It sounds good. You can also use Data Consolidation to create a “totals” sheet from all of the individual sheets.

        2. tcookson*

          I got carried away last night with the excitement of all the Excel tips in this post, and stayed up until 11pm working on my practice spreadsheet.

        3. tcookson*

          Now I’ve gone more toward having a separate entry sheet for each cost center, and then the first sheet is a dashboard (which I learned about from folks in this thread — thank you all very much!) and then googled up how to make it so. The dashboard will have the annual budget for each cost center with pivot tables showing the amounts spent by category (travel, honoraria, office supplies, etc.) and pivot charts showing the percentage of the annual budget spent so far. It will be an at-a-glance overview of the status of each cost center’s budget.

    7. Kendry Loufier*

      Forget lookup; invest a little time in understanding how to nest offset and match functions. So much more versatile, they are like a leatherman to the lookup function’s letter opener. Want to lookup a value corresponding to the lookup values relative row and/or column positions on arrays from different sheets? not a problem! wanna return dynamic ranges? done deal! trust me, your mind will be blown with the possibilities!

      1. CharmedGeek*

        Agree with this! VLOOKUP is great when you want to perform a simple match up, but a combination of Index/Match and Offset will open up so many more possibilities!

        Although I love building complex formulas, sometimes the simplest ones are best. Such as =UPPER(), =LOWER() etc, when you want to change the case of the letters (particularly if you’re using VLOOKUP with a FALSE parameter as it then becomes case sensitive). Another useful one is =TRIM() – used when you’ve copied in hundreds of lines and some have an annoying space at the end of the character string. Again, this will affect how a VLOOKUP works.

        One trick I cannot do without is how to remove blank rows from a long column of data. Select the column you want by clicking on the header letter, press F5 and a ‘go To’ box will pop up. Click ‘Special’, select ‘Blanks’ and click OK. All the blank cells in that column will be selected. Then press CTRL and – (the minus symbol) to bring up the Delete box. If you select ‘Entire row’ then it will remove all the blank rows and shift the rest up. It’s saved me so much time!

        1. Kendry Loufier*

          Nice, didnt know about the delete blank rows trick!
          One more thing i thought of (thats especially useful if someone else is going to use your document) is to use dropdown lists for data validation. Can save quite a lot of time and protects you from users (including yourself) inputting typos and messing up your sumifs, etc. Offset is great for selecting the ranges for these!

        2. ExcelGuru*

          Another thing to remember about VLOOKUPs and HLOOKUPs is that you can’t load up your spreadsheets with many of them because they’re costly in terms of system resources. If you need to do a lot of LOOKUPs, use INDEX and MATCH instead.

  2. Erik*

    I would also recommend learning VBA, which is useful for macros and other automation tasks in Excel.

    2. Elkay*

      Lots of people are snobby about VBA but I love it. My Excel/VBA skills helped me snag my current job.

  3. Mishsmom*

    usually the weird/crazy stories are my favorites, but this is going to be invaluable – thanks for this question!! :)

    1. pgh_adventurer (OP)*

      Absolutely! Only wish I’d asker earlier–so many great time savers here. Thanks everyone!

      Now I’m inspired to play around with pivot tables, vlookups, and countifs and see if they can make my life easier…

  4. E.R*

    The most useful one i know is a VLOOKUP. Sames so much time, makes me look like an expert. I want to learn how to do macros better (im also entirely self-taught and friend-taught in excel)

  5. Anon*

    Workspaces are very handy when you always seem to be working with a related set of multiple spreadsheets.

    Chart functions are awesome for when your coworkers/boss need your raw data presented in visual way.

    And format painter can save an incredible amount of time.

    1. Frieda*

      Charts! If you need to share whatever you are doing in Excel with others, learn not only how to make the different charts but also the design principles behind data visualization. It’s not just about being “pretty”; there are empirically tested best practices for charts/graphs that are based on harnessing human visual perception. Good places to start are the books and blog of Stephen Few (Show Me the Numbers; Now You See It) and the Peltier Tech Blog.

      If you want to get deep into data viz Edward Tufte is a must-read, but I find his work to be much more about theory and high-end concepts, while Stephen Few is more about “this is the best way to format a bar chart and this is the reason why.”

      And never use pie charts.

      2. Anon*

        Oh my YES! My degree is in hard core sciences, and it drives me bananas when I see people using charts that look colorful and eyecatching, but don’t display the data in ways that make sense (or that even detract from the point they are trying to make).

      3. Anon*

        Although I do disagree about pie charts as long as they are used appropriately. For instance, they are ideal for showing ratios of a whole or for showing how percentage allocations change from one year to the next.

        1. Frieda*

          Not necessarily. Humans visual perception is not great at comparing relative angles, which is what a pie chart shows. We are VERY good at comparing relative lengths, as in a bar chart. I can’t think of a situation in which a 100% stacked bar chart isn’t better than a pie chart.

          Consider this: You are comparing sales of two products across two years. In year 1, Product A = 37% and Product B = 63%. In year 2, Product A = 40% and Product B = 60%. What would more clearly and immediately show the difference: two pie charts? Or two 100% stacked bar charts?

          1. AO*

            I’m super late to this party, but thanks for the info Frieda!

            This explains why I’ve always found pie charts useless – I keep reading/hearing anecdata about how humans process information better visually, but I understand what 38% represents better if someone just says it than if it’s in a pie-chart.

  6. Anon - 345*

    Second the Pivot Table Ideas.

    To change a pivot table out put into a table…
    copy and paste it into a new sheet and then push”ctrl A” “ctrl G” then “Alt S” then “k” then “=” then up arrow then “ctrl enter.” To finish it off copy all the data and paste special values. I do this function every day to create higher level reports for mgmt.

    1. Gail L*

      Someday I’m going to get Excel 2010 and be able to make a dashboard with a slicer. It’s the true power of PTs.

      And I’ll have a job where this functionality is needed.

      1. PJ*

        I haven’t understood one thing in thos post. Embarrassing how little I know about a tool I use so often. Dashboard with a slicer? To me that means chopping onions while driving.

        1. voluptuousfire*

          Same here. I use Excel every day in pretty much every job I had but never used any of the advanced functions. I still use Excel every day for my job search log but it’s kind of amazing what it can do and how daunting it can be until you see how something’s done and it’s like “that’s it? This is all the malarkey those ‘must have advanced Excel skills’ job ads I see all the time?”

        3. Gail L*

          Sorry! You can look up a dashboard for a visual. It’s basically a page filled with charts, graphs, all gathered together. Usually “behind” it is a lot of data that you update frequently.

          Say, you have a big database of all your sales info. But you want to look at monthly volume, monthly growth, seasonal trends, etc. And you want to look at it on a regular basis. You dump all the data into a spreadsheet. Create the PT on another. Then create a front page with all the nifty graphs. You or your boss can then look at the graphs and instantly tell that sales are down or that the seasonal hike is higher than normal.

          The “slicer” is something that is new in 2010… it’s actually a button. You can actually create a button that would allow someone to just click to change the info they are looking at. Say, just 2012 instead of all years.

          The end result is a “dashboard” – a single page of informative graphs that you can interact with and push the buttons on to change what you are looking at.

          It’s really neat, but I think it’s only useful if you have a lot of data and you are trying to communicate trends with it.

        4. mm*

          A slicer is just a fancy pivot table. It has a much nicer appearance than the standard filter buttons on a pivot table.

      2. DATAnerd*

        That would be the powerpivot add-in you’re talking about. It makes big data analysis possible within excel and it’s truly impressive when you know how to use it. Here you’ll get into DAX/MDX/Cube formulas and your life will truly be changed for the better. Powerpivot does everything that you’ve always wished excel could do times about 100. If your a data analysis nerd like me, you will be extremely grateful for what the tool can do.

      1. Khushnood Viccaji*

        @Anon-345 – if you have Excel 2010 or higher, you can do this more easily.
        – Click inside a PivotTable
        – Then on the Design tab (in the Ribbon), click on the Report Layout drop-down, and
        – Select the option Repeat All Item Labels.

        No need to select the entire pivot table, copy-paste values, select blank cells, enter formulas, use ctrl-enter, and paste them back as values :)

      2. Khushnood Viccaji*

        @Frieda,
        Excel doesn’t record a series of keystrokes as a macro, but as a sequence of commands in the VBA ‘language’.

        I have this standard bit of code, which you can try.
        Please try this on a copy of your original file, just in case something goes wrong :)

        Copy-paste the text below (from Sub… to …End Sub ) into a VBA module in the file containing the PivotTable.
        Then switch to the Excel window, and select a cell within the PivotTable.
        Then press Alt+F8, select this macro in the dialog box that pops up, and click Run.

        Sub CopyPastePivotTableDataAsValues()
        ActiveCell.CurrentRegion.Copy

        Workbooks.Add
        Range(“A4”).Select
        ActiveSheet.Paste

        With Selection
        .PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
        .PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False
        .PasteSpecial Paste:=xlPasteFormats, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False

        .SpecialCells(xlCellTypeBlanks).Select
        End With

        Selection.FormulaR1C1 = “=R[-1]C”

        With ActiveCell.CurrentRegion
        .Select
        .Copy
        End With

        Selection.PasteSpecial Paste:=xlPasteColumnWidths, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False

        Application.CutCopyMode = False
        End Sub

    3. Wakeen's Teapots Ltd.*

      Hey Anon – 345,

      Have you looked at just doing PDFs of the pivot table with whatever filters you want on for the management reports?

      They are tidy and pretty and might save you the work of changing into tables. If you save as PDF it saves only that view of the pivot table you are running at the moment and none of the underlying data.

  7. LT*

    Conditional formatting (where you can make a cell turn red when its value is over 100, for example) is central to my Excel use.

      1. Steve*

        Excel 2007+ made Conditional Formatting so, so, so, so, so much better. Definitely one of those things you didn’t know you needed before existed, but couldn’t live without now. The gradiated color scales are pure genius.

    2. Yuu*

      Conditional Formatting to show duplicates is great to make sure you don’t enter something more than once as well.

  8. kdizzle*

    Easy one…If I have a spreadsheet with a lot of formulas, hitting “Ctrl” + “~” allows me to spot check the formulas for consistency.

    2. kdizzle*

      Plus it’s essential if you’ve inherited someone else’s spreadsheet and you’re trying to figure out their process.

    4. samccrory*

      And then you can use Find & Replace to FIX THEM.

      Seriously, F&R is a very simple tool, but I use it ALL the time and people are constantly going “huh, I never knew you could use F&R for that” and I have even had people say “I thought there was no use for that tool”. People. Simple sometimes is the best thing.

      1. Kelly O*

        Find and replace is my very best friend. I use it all the time.

        Also conditional formatting because it really does help quickly identify the anomalies (and I blow everyone’s mind when I can quickly see if we have anyone with really high hours.)

      2. TychaBrahe*

        Ctrl+~ displays the formulas in each cell instead of the values. Ctrl+~ again changes it back.

        If worse comes to worse, close without saving.

  9. Anonymous*

    The coolest thing I had to do recently is set up a workbook so that whatever cell was selected was automatically highlighted. (Multi-page workbook, with hyperlinks to specific cells on one sheet, but it was hard to tell what you were looking at.)

    Right click the tab, select View Code, then paste this into the editor and save. (Note: “Color index=6” means it’s a yellow highlight. You can google the other colors if needed.)

    Private Sub Worksheet_SelectionChange(ByVal Target As Range)
    Application.ScreenUpdating = False
    ‘ Clear the color of all the cells
    Cells.Interior.ColorIndex = 0
    ‘ Highlight the active cell
    Target.Interior.ColorIndex = 6
    Application.ScreenUpdating = True
    End Sub

  10. Laura*

    I’ve got two that help me a lot in data-entry.

    Ctrl+D can make entering a lot of similar information really easy. It’ll duplicate the information in the cell directly above. I frequently have lists where the information for a row of cells is similar except for one cell and this saves me a ton of typing. It doesn’t save the information to your clipboard, either, so you can still use Ctrl+V if needed.

    Also, I have two monitors and frequently need to use information from one spreadsheet to create a second spreadsheet. In Word, you know how you can reduce the document you’re working on and drag and drop it to the other screen? Excel doesn’t make it so easy. However, you can get it to work this way. Open the first document you need and then go and open a second Excel from the Program menu. The second program will open a new spreadsheet and from within that second program, go to the File button and open the second worksheet you want to work from. You can minimize the size and drag and drop one of the open Excel files and then be able to work side by side easily.

    1. amaranth16*

      One tricky thing about this, though, is that sometimes Excel will not allow you to do VLOOKUPs or copy and paste between two different Excel workbooks if they’re open in different sessions of Excel. I use Excel in multiple monitors all the time and find running 2 sessions indispensable, but it can be a PITA if you need to actually manipulate the data instead of just referring to it…

      1. iseeshiny*

        Same here. My workaround for this is to make my excel program stretch across both screens and then minimize (not actually minimize, but the icon with two windows that’s between minimize and close. What is that even called?) the two workbooks, and position one on each screen. It’s a pain but it gets the job done.

        1. Emily K*

          Ctrl+Tab is a handy way to scroll through all your open workbooks. It took a couple days for me to get used to, but now I do it so quickly that if I’m copying and pasting from one worksheet to another it doesn’t slow me down to hit Ctrl+Tab any more than if I had to move the mouse over to an adjacent workbook. It doesn’t solve the problem where you want to look at both at the same time, but it at least lessens the frustrations when you’re copying and pasting back and forth.

        2. Anonymous*

          View – Arrange All – Vertical will maximize the size of each Excel workbook within the Excel window you have open stretched across multiple screens.

    4. JMegan*

      Ctrl+R does the same thing as Ctrl+D, except it duplicates the information in the cell to the left. I love these two!

    5. Marcy*

      I’ve always used “View”, “Arrange All” then “Vertical” to place the spreadsheets next to each other. Then you can copy and paste from one to the other with no problem (or drag and drop).

    7. Julie*

      I think CTRL+’ (apostrophe) is the same as CTRL+D. I’m going to have to check out CTRL+D & CTRL+R!

      1. Elkay*

        Ctrl+D will do multiple cells whereas Ctrl+’ will only do the one directly above. Say you want to populate three cells with duplicates you’d highlight those and do Ctrl+D but if you just wanted to populate the cell you had active Ctrl+’ works.

  11. TL*

    =if(…
    is my go to formula, because I’m changing a lot of data into yes/no values and it saves so much time.

    Also, yay for Excel thread!

  12. Gail L*

    Ooo! I love Excel. Some of my favorites..

    *If you highlight a cell, you can click and drag the little black box in the lower right corner of the cell. This will fill in all the “dragged” cells. Then there is a little floating button next to it that you can click to tell it to fill a series or copy the cell. You can copy like this, or if you want to do something like fill month, it will auto-fill those months. Maybe everyone knows this, but I was using Excel for years until I found out about it.

    *Creating a drop-down menu in a cell is actually not too hard. Google “data validation” to learn how to make the list and set up the cell.

    *I always end up using “sumif.” You can google this. But it’s really handy if you have a big database and want to add up some component of it. E.g. you have 5 types of customers and you want to see the % of revenue from each. You are basically telling Excel, “for every cell labelled ‘online customer’ add up all the cells showing their purchase amount.’ It’s so handy, because you can just label everything once and Excel does all the adding. And if you change a label – auto-update.

      2. Apollo Warbucks*

        You don’t even need to drag you can just double click and it fills the column oh the end of the range

    2. kristinyc*

      For your first one – I often have to fill a formula to the bottom of a column with many, many (60k+) rows. I’ve been dragging from the bottom corner, but it takes forever. I use a Mac. I know there’s a shortcut for this on a PC – do you (or anyone else on here) know what it would be on a Mac?

      2. Gail L*

        I hate Mac + Excel.

        But yes, possibly. Go to the bottom of the spreadsheet and type an X or some nonsense into the bottom cell (or something far down). Go back to the top of the column and copy the cell you need. Then press ctrl + shift + down arrow (I presume on a Mac it might be squiggle button + shit + down arrow, you might have to experiment). It should highlight all the cells down to the X. Paste.

        That should copy all the cells. Also, it might crash your computer.

      3. Chrissi*

        I think that you just doubleclick on the little box in the lower right hand corner of the cell (that you said you use to drag it) and it will automatically copy it down to the bottom row of the worksheet.

        2. Alicia*

          ^^ That! I worked with data that sometimes maxed out spreadsheet limits… just double-click on that little right-hand bottom corner black box when you have the formula you want. And voila!

      4. Sydney Bristow*

        Does double clicking on the bottom right corner of the cell not work on a Mac? I mainly use Excel at work but have only Macs at home and joke that we need to get a PC solely for the purpose of using Excel.

        The dragging/double click feature is one of my favorite things. That and column filters.

      5. samccrory*

        I’m not sure about a Mac, but in Excel 2013, you can just double-click on the fill handle in the lower corner, and it will automatically fill down (without dragging) until it hits what it thinks is a natural stopping point.

      6. Anonymous*

        If you are using data sets this large (60k cells!), Excel is the wrong tool for the job. Yes, it technically can do what you are asking it to do; however, there are much better tools for the job at that point.

        You have strayed far into programming territory. Excel is a fantastic tool, but it is very limited in certain respects. It’s meant for business applications. It can handle hundreds, even a couple thousand, of entries gracefully, depending on what you are doing. It can handle simple mathematics and logic well.

        Excel is not meant for large-scale data analysis though, and you are hobbling yourself if you don’t pick up a more appropriate tool. Obviously I have no idea what you are doing, so it is hard to offer up alternatives. R (a programming language) is good for statistics work. Python is a scripting language good for flexible and quick repetitive computations over a large data set, and free. Mathematica is my personal favorite for science work. Matlab is very similar. Octave is a free program similar to Matlab, for companies on a shoestring budget. There are also traditional programming languages like C++, fortran, etc. if you do extensive computation work. In any of these, you’ll find that there are ways to do your computations more quickly and effectively over 60k entries than Excel can manage. These options will also let you graph your results (excel tends to die a horrible death if you ask it to graph that much info), perform appropriate error-checking on data, and more.

        1. hamster*

          Or you can use a reporting tool like QlickView or SAP BO XI or something or that sort. Or plain old SQL.
          Powerful stuff there. :D

        2. Marcy*

          I have to agree. I love Excel but we were spending two days refreshing pivot tables to create our 200 page monthly report (don’t ask). We switched to Crystal Reports and now it takes a few minutes- just open the report, hit the refresh button, pick the date of the data you are reporting from the drop down and that 2-day report takes just a couple of minutes, charts and all.

          2. Frieda*

            I’ve started learning SPSS Statistics, which I’ve found works much better than Excel for a lot of things with larger data sets. A license is expensive if you’re just an individual but not too bad as a business expense. I also found it pretty easy to learn.

            1. kristinyc*

              We have a data warehouse/team of data scientists (but the data I need isn’t in the warehouse – yet), so hopefully I’ll be doing all of this in SQL in the very near future. :)

    4. ellex42*

      People I explained the dragging trick to have been thrilled – until I got the temp who claimed her mother was a computer programmer, but didn’t know what I meant by “right click”.

  13. Dave*

    pivot tables scare people off. but it seriously takes 30 mins of tinkering to get the hang of them.

    in my opinion, the hardest part of getting pivot tables done is finding the damned button. they’re so incredibly useful & powerful.

    1. Marcy*

      I put the button on my quick access toolbar. You can also bring up the old wizard by pressing “Alt” , “D” then “P” (not simultaneously). The wizard box will pop right up.

  14. Jeffery*

    My favorite trick is the CTRL+SHIFT+Directional Arrow (e.g. up, down, left, right) to highlight a data set. Rather that using the mouse to click and highlight all the way down a long spreadsheet, you can simply “CTRL+SHIFT+Down Arrow” to highlight the entire column, and then “CTRL+SHIFT+Right Arrow” to catch the rest of the table as a whole.

    1. A Jane*

      YES!! This one. My boss thinks he’s great at excel, but I watched him scroll through 1000 rows. It was awful.

      1. Laurie*

        Haha yes! It takes all my willpower to look on calmly instead of manually taking over and Ctrl+Shifting that selection.

    3. anonn*

      Similar is END and then a direction arrow. it takes you to the last consequetive cell in that direction with no data in it. Helps you find the end of data if you have a database set on one side and a formulas to the right. Then you can wipe out the extra formulas if there are any.

  15. Joshua*

    My coolest excel trick is looking for and color coding duplicates across multiple worksheets. It is done like this:

    Make sheet 1 the data source you want to compare against, like a list of users. select the column you want to compare against and name it in the upper left, you’ll use this name later.

    Then on each subsequent sheet, select the column you want to compare to your primary column and name it, in the same way, for example I’ll name it list1, list2, list3 on three sheets respectively.

    now comes the fun part, using the countif formula. Go back to sheet 1 where you have your source data you want to compare for duplicates, select the field and select conditional formatting > New rule. You can create a rule like this:
    =COUNTIF(list1,A1)>0

    I can then pick format, give the result a color, and be on my way. I can create a similar rule for the list on page 2 and select a different color.

    =COUNTIF(list2,A1)>0

    the thing to remember is that you are applying all of these rules to your master list that you want to compare the sublists to. This means, when you are done, you’ll have this awesome color coded source list of all duplicates, and by color you can tell which sheet/list they are on.

    This is the absolutely best way to sort/compare huge data sets that are across multiple sheets. The only thing is, that the data in each column you want to use has to be formatted the same… For instance, if you are using names, it needs to be first, last / last, first or whatever in both sources.

    Here is a brief how-to, although he doesn’t go into the color coding and uses a selected range of cells rather than a whole column.

    http://www.techrepublic.com/blog/microsoft-office/mark-duplicate-entries-automatically-with-excels-countif-function/

  16. amaranth16*

    Agree with other commenters that pivot tables and VLOOKUP are essential. Even better than VLOOKUP is INDEX(MATCH), which allows you to look for a value in another workbook by row and column at the same time. It’s a little hard to explain, but here’s a tutorial: http://www.randomwok.com/excel/how-to-use-index-match/

    If you wrote a VLOOKUP and it failed, check:
    Is the target array is unlocked? Use F4 to lock cells while you’re writing formulas, or write in dollar signs before the row and column labels.
    Is your workbook set to Manual calculations instead of Automatic? (Formulas tab, Calculations option) — this will cause other formulas to appear to display the wrong value, too.
    Are there leading or trailing spaces in your array? Use =TRIM and =CLEAN formulas to remove them.
    Do you have numbers formatted as text somewhere? There isn’t a one-click method to convert numbers-formatted-as-text to numbers, but you can create a new column and multiply them by 1.

    PowerPivot is also a really helpful Excel add-on… it’s easier to learn once you have some proficiency with pivot tables.

    1. AnotherAlison*

      Yep, INDEX rocks, ’cause than you can do stuff like this:

      {=IFERROR(INDEX(Competitors, SMALL(IF(($B3=RawOpID)*(COUNTIF($AG$3:AH3, Competitors)=0), ROW(RawOpID)-MIN(ROW(RawOpID))+1, “”), COLUMN(B2))),””)}

      I’m not even going to explain what this does, but basically it lets me extract data from some horrendous database export that has tons of duplicate records.

      I <3 Excel, but the formulas get complicated if you want theme to work for every possible data case (i.e. null, #, text, numbers). Most people [in my department] don't want to take the time to learn how to build the formulas so things work right all the time, but it saves you so much wasted time waiting for corporate to allow the programmers to build you the tool you need.

      1. amaranth16*

        Yeah, I find that the 80-20 rule (or maybe it’s a 90-10 rule, or 92-8) is particularly pertinent in Excel. It’s not worth it to me to spend the time to write a formula that will work WITHOUT FAIL, because I can write one that will get me 92% of the way there, and then with cursory cleaning I can have exactly what I want.

      2. Josh S*

        I was going to bring up Array Formulas. They are the best.

        But they’re difficult to explain without having a spreadsheet up in front of you, and all the examples I can find online are really lame and don’t show the power of them.

        Suffice it to say that they allow you to combine formulas in ways that you otherwise cannot. But if you make them too complex, you can seriously freeze your computer. SO MUCH POWER!

        1. KC*

          +1 to arrays.

          Agreed that too many formulas will slow down Excel. That’s when manual calculation mode can be helpful.

      3. Jo in OKC*

        Highlight the single column that you want to be numeric and choose Text-to-columns from the Data ribbon. Choose delimited and accept the standard delimiter that is selected. Select General and click Finish.

        Presto! All the numbers are back to numbers, not text.

        If you have the opposite situation (like part #s or customer #s that you want to be alphabetic), then choose Text instead of General as the last step.

      1. amaranth16*

        Oh, totally, but if I’m compiling info from multiple sources, especially outside the organization, it invariably comes up.

      2. hamster*

        Actually. I worked briefly for a company that sorta based their business on reporting data done in excel. I had a boss there incredibly talented at making excel look like a dashboard , buttons, macros, dynamic graphs, etc. Really pushing the limit on excel. One think she was a sticker with, was displaying numbers as text, in order to strictly control the formatting . If done right, with the correct conversion mask and all, it works flawlessly

  17. Kristine*

    ” Control + F ”
    It opens a find field on excel (and just about any program) which enables you to search for a certain word / phrase, etc. Best shortcut ever.

    1. ellex42*

      Still trying to teach this to my boss…sigh. And it’s just a list of map numbers and well pad names.

    2. Khushnood Viccaji*

      And to repeat the last find, you can use Shift+F4 to search forwards, or Ctrl+Shift+F4 to search backwards :)

    3. Khushnood Viccaji*

      One more :)
      In the Find dialog box, there’s a ‘Find all’ button, which shows all the results in a list within the dialog box.
      You can click on any result and Excel will move the cell selector there.

      Btw, the Find dialog is re-sizable in Excel 2010 (not sure about previous versions).

  18. SAK*

    Filter by color. Example – when reviewing a list of customers I highlight the ones that need a follow up yellow. After finishing I use the filter by color option to view just those rows.

    Filters in general can be very powerful. Filter by color was added in Excel 2010 and I was quite excited (sad but true).

    1. Colette*

      Also, filtering based on some or part of the data – for example, filtering an e-mail column based on whether the e-mail domain is @chocolateteapots.com. (Text Filters -> Contains is how you get there, once you’ve got a filter on the column).

    2. LucyVP*

      Me too!

      I color code all my worksheets and when I stumbled on the new addition of filter by color – I yelped with glee and then called a similarly color obsessed friend to share my excitement.

    3. BigLaw Refugee*

      You can use Excel in almost any job where you do any kind of tracking of anything. Most lawyers I know barely use Excel, but I’ve used it daily for years – ever since I realized that Word was not up to the tasks I needed to do with a production log that the legal assistants had been maintaining in a Word table. The only problem is, once you really learn it, you’ll end up wanting to do other people’s jobs for them because you can do them so much faster!

      E.g. everyone is talking about VLOOKUP. At my old law firm, we would make long lists of documents that we didn’t want to turn over to the other side. The lawyers would put together the list with document description and reason for not producing. Then a copy vendor would apply a unique number to the documents, and legal assistants would manually enter the thousands of unique numbers into the list.

      I realized that I could generate two lists – the unique number list plus a pre-computer-generated number, and the chart plus a computer-generated number – and use VLOOKUP to match the unique number with the description for each document. I did in 15 minutes what was taking the legal assistants 2 days. But now do I teach the legal assistants to do this, or just keep doing it myself even though it’s not my job? (I tried to teach them, but there was so much turnover that it wasn’t worth it….)

      1. Katie*

        Could you write an idiot proof word-doc tutorial for them? Screen shots and all? You might still have to demonstrate the first time but then they could use your instructions to go through it all the other times.

  20. Cube Ninja*

    I work with a lot of dates that are generated from system actions. When this goes into Excel, it includes a time stamp, which is often quite useless if I want to look at a specific date.

    So, my timestamp in cell A1 looks like: 12/05/13 13:33:45

    =int(A1) will strip the time stamp and leave the date. Sorcery!

    Another little sneaky trick I use occasionally is using Excel as a calculator. =sum() doesn’t require the standard formatting if you’re just doing two numbers. This means that in a pinch, you can use “=52/455” in an empty cell to quickly calculate any two numbers together with the standard mathematical operations.

    Last one from me… if you need a chart with non-linear axis labels (for example, using a series of dates that aren’t exactly 7/14/21/30 days apart), you can plug your data into a simple table and use a PivotChart instead of Excel’s normal chart/graph functions, which have some really silly limitations on how you can configure your axes.

    2. Anonymous*

      I learned on my current job that you can also have Excel add (or subtract) a number of days to a date, which I use a lot for deadline stuff. I just do it with the date in one cell (say, A1), and then in B1 put an equals sign, click cell A1, and then +# of days.

      So, if you want to know what date will be 15 days from the date in A1, the formula is: =A1+15. Very simple, but handy if you have a lot of weird deadlines to calculate.

        1. Windchime*

          Geez, I could have used this one like 15 years ago when I had to do audits. We were tracking how many days it was taking for a charge ticket to get from point A to point B, only we just wanted to use work days. So I had to write some horrible big long formula for that. Love this one!

        2. Elkay*

          You can also create a list of holidays and have the formula reference that during the calculation. I put a list of UK bank holidays in a separate sheet then use that as a reference.

  21. Andrea*

    I am also very excited about this thread! I use Excel daily and heavily. My newest favorite is using F4 to cycle through absolute, relative and mixed cell references. When you’re typing in a formula that references another cell, hitting F4 allows you to quickly change from a relative reference to an absolute reference, to a column-locked reference to a row-locked reference, without typing in any ‘$’ yourself.

    1. pgh_adventurer (OP)*

      WOW! this is awesome. I just learned what $ does and this makes it even easier! Thank you!

        1. Marcy*

          You know how when you drag a formula to the next cell, it changes the cell references? The “$” keeps the original cell reference.

            1. KC*

              Yes, if you “lock” both the column (designated by a letter) and row (designated by a number) reference.

              For example, $A$2 will always refer to the A2 cell. $A2 will refer to A2 in row 2, but when the formula is dragged down a row, it will refer to A3.

              1. tcookson*

                Aha! So A$2 would refer to A2 in row 2, but when the formula is dragged down a row, it will refer to B2. This is good to know; I’ve copied formulas when googling things to do in excel, but I never understood what the $ was for. Now I feel like I can write my own formulas using it.

  22. AMG*

    I was all excited to post but I can only second some of the other comments. I love pivot tables, if/else, vlookups, ctrl/shift/arrow. I also use filters to quickly look up info and sum/average/analyze/answer questions without chopping up my data. I alos like to select a group of cells and look in the bottom right corner to see the count, sum and average real quick. Helps to get a quick number without stopping the flow of conversation.

    1. Samantha*

      It’s a way of organizing and sorting massive data sets. I never really got it until I actually HAD enormous data sets to organize and analyze (because it’s hard to create enough dummy data to make a pivot table worth it).

      Let’s just say that pivot tables make easy work of analyzing the Google Analytics data that you’re hiring an intern to do.

    2. kristinyc*

      It’s a way of looking/grouping data that’s more advanced than sorting/filtering.

      For instance, I use them to look at email reporting. I have columns in my spreadsheet for “Email Campaign”, “Open Rate”, and “Click Rate”. I might have 20 or more emails for each campaign (for example, I may have a campaign called “Holiday 2013” and 25 different email sends for that campaign, and another campaign called “Surveys” that has 10 email sends.) Each send would have its own row of data with open and click rates.

      If I wanted to look at overall open and click rates by campaign (in this case, Holiday 2013 and Surveys), I would create a pivot table that would result in a small table that looks like this (rather than 35 rows of data):

      Open Rate Click Rate
      Holiday 2013 ##% ##%
      Surveys ##% ##%

    3. amaranth16*

      A pivot table is a way for you to summarize a lot of data quickly without having to write a lot of formulas. When you create one, you can generate small tables that calculate the averages, counts, % breakdowns, or other summaries of several fields at once. You can also add filters to them so that you’re only looking at specific rows (e.g., I want to know average values for several variables, but only for rows tagged as belonging to a certain client). They’re incredibly helpful.

      If you have a workbook open with a dataset in one tab, open a new tab, go to the Insert menu and choose Pivot Table. Once you select your data range, you’ll get a field list showing all the variables in your dataset, and windows into which you drag and drop values for table rows, table columns, filters, etc. Once you add a variable to a pivot table, you can right click on it to change the field settings to average, count, etc.

      My explanation here is probably a little pale, although my enthusiasm for pivot tables is boundless. The best way to learn is by playing around with them.

      1. Ask a Manager* Post author

        I know! I am sad about it too. I rarely need to use Excel in my work for anything particularly complex so there’s never been impetus to learn, but I wish I knew more.

    5. jasmine*

      The key feature of Pivot Tables is that you can drag/drop rows and columns of data into and out of the table to do what-if kinds of analyses. For example, I can start by displaying average salary by department, but then, to understand the data better, I can drag in “job category” (another column from my original spreadsheet) and see each department’s data broken down by job category (or each job category broken down by department, with a flick of the mouse). I can add the new variable either as a row of my table or as columns (one for each category). I think the “pivot” in “Pivot Table” comes from the ability to sort either vertically or horizontally. You can also use “Pivot Charts” to do the same kind of analysis with graphs.

      1. Emily K*

        Yes, and as you add new rows of data to your dataset, you can just right-click and “Refresh Data” in the pivot table to update every single value in the table to instantly reflect the added rows.

        This means you can create one pivot table and/or chart and create a standard and fairly attractive reports from it on a regular basis just by pasting in new raw data, without having to create the table itself and all its formulas again.

        1. Marcy*

          It is also helpful to double-click in the “values” section on a specific entry and it opens a new tab that shows you the full database entry with all of the columns for that particular entry.

      1. Julie*

        Keep in mind that to create a PivotTable, your labels need to all be in the top row of your data – no labels in the left column.

    7. Ethyl*

      I <3 pivot tables. I actually didn't know until pretty recently you could use them in Excel — I had primarily used them in Access before. They are SO useful and you can play around with them much easier than writing a bunch of formulas and stuff. Which is also fun, but can be tedious with a lot of data.

    8. BigLaw Refugee*

      Here’s another example of how they can be used. In my current job we often get bank records and analyze them to find out what someone has been up to – are they spending money the way they should be or are they stealing it? Who are they doing business with?

      We get the date, amount, and payee of each transaction from bank data or account statements. It could be thousands of transactions a year and we might analyze up to five years’ records at once.

      Using a PivotTable, we can quickly summarize this data by both payee and time period. So that allows us to see both how the money is being spent, and whether that has changed over time. We can identify the largest payees quickly and focus our analysis on them.

      Without PivotTables, staff would spend a week poring through the individual transactions and manually adding up these amounts.

  24. Anonymous*

    SUMIF and COUNTIF are super duper easy to learn and use but they make you look like a genius. I’ve used them to create a sheet of statistics analyzing another sheet of data. For example, money raised. If you have five sponsorship levels and you want a tally of what amount and percentage of your total funding comes from each of those levels, COUNTIF will let you have a list of how many of each level there are, and SUMIF will make it easy to put an amount raised column next to it.

    1. pgh_adventurer (OP)*

      I love it when people say what the formula does instead of just listing the ones they like :) Thank you!

  25. Bee*

    This might be kind of basic for some people, but learn how the Paste functions work. There’s a bunch of different ways data can be pasted beyond just using Ctrl+V (which pastes cell basically exactly as it appears in the formula bar – which when you have equations or different formatting can cause issues).

    My favorites are Paste Values, which pastes the *answer* to the equation rather than the equation itself, and Transpose, which pastes a column of data as a row or a row as a column. Very useful for manipulating data, particularly in spreadsheets you’re creating on the spot.

    You can also use the Paste Special menu to paste formats, column widths/row heights, or paste without changing the border lines.

    (Note that most of these only work for copied data – if you Cut the data it won’t let you do anything but good old Ctrl-V.)

      1. Kelly O*

        Me too.

        I also use the “paste values” and then “paste formats” so I can avoid passing along all the giant formulas to people who don’t care about them, and my spreadsheets load faster, so the more basic functions I use that data for don’t clunk up the works.

      2. Windchime*

        Yep, I use Paste Values a lot. I will do a calculation on fields, and then if I want to just analyze the results of the calculations, I can copy and Paste Values into a new worksheet. Love it.

    2. Lillie Lane*

      Yes, yes. And this one is SO basic but…..I have so many coworkers that don’t use the Ctrl+C copy function. Can’t tell you how many times I’ve been standing by somebody’s desk, helping them with a spreadsheet, and they go up to the ribbon every time they need to copy…..I keep my mouth shut, but I’m *screaming* in my head, “please use Ctrl+C, please use Ctrl+C, PLEASE USE Ctrl+C ALREADY!!!!”

      I guess I have some “Ctrl” issues of my own….

      2. tcookson*

        I know! I can’t even watch when people are doing that! I just have to avert my eyes and take a deep breath. It’s the same reaction I would have when I had to monitor my kids’ timed multiplication exercises. The agony of watching them mosey slooooowly through the problems was excruciating. I would have to leave the room until the timer went off.

      3. Marcy*

        I don’t use the ribbon much and I know about Ctrl+X, Ctrl+C and Ctrl+V but I prefer to right click and copy from there. I’m just faster doing it that way but I use Excel all day long and should probably just have the mouse surgically attached to my hand.

      4. Nicole*

        That makes me crazy too! We use a program extensively at work that, when you right click, opens a popup box that relates to the program. For the longest time, my coworkers thought you just couldn’t paste into it, which would be horrible. I got to teach everyone the magic of keyboard shortcuts.

    3. evilintraining*

      Absolutely! And all one has to do is to hover over the paste options to see what they’re going to get.

    4. Spacecowboy*

      Also, you can paste special values and formats using the shortcut “alt” + “e” + “s” which will bring up a menu to where you can then press “v” for values, “t” for formats and so on.

  26. Victoria Nonprofit*

    Can I ask for elaboration on a bunch of these? It’s awesome to see something like “vlookups are SO helpful!” but way more helpful to see something like “vlookups are for looking up Vs. To do them, you type =vlookup + lookupallvsplease into a cell.” <—- obviously I have no idea what a vlookup is.

            2. amaranth16*

              Select the rows you want to be able to collapse. On the Data tab, click “Group” and then “Groups.” To the left of the worksheet, you’ll see a bracket with a minus sign. You can click the minus sign to toggle the cells into hiding. Especially helpful if you have rows that look like:

              TOTAL
              subtotal
              subtotal
              subtotal

              and you want to be able to collapse those three sub rows.

              1. Chrissi*

                That is very cool! Thank you both! It’s humbling how simple these are, but they will be infinitely useful.

        2. Windchime*

          How about Freeze Panes? Has anyone mentioned that, and if not, I’ll mention that in case people don’t know about it. Freeze Panes allows you to keep certain rows/columns in place, while the rest of the spreadsheet scrolls around. You can also just choose Freeze Top Row, and then scroll around while your headers stay in place. I use this one all the time, too.

          1. Ethyl*

            YES PLUS ONE MILLION TO FREEZE PANES!!!

            Oh also — under Page Setup, you can choose rows or columns to repeat on each page when you print. This is really useful if your table will run to multiple pages, so you can keep the column or row headers in place when printing, and then you don’t have to keep flipping back to the first page to remember which row was which analyte and which column was which sample. Or whatever. ::ahem::

          2. ellex42*

            I use freeze panes all the time, because it seems like I’m always using Excel for lists and indexes. Without being able to see the headers, everyone who uses my Excel workbooks would be inputting info in the wrong places.

      3. amaranth16*

        A VLOOKUP is a way to import values from one worksheet into another by matching a specific values. It’s significantly less error prone than copying and pasting data, because it ensures that data stays matched to the correct record – you don’t have to worry about something being missing in one list and everything below that being off by 1.

        So let’s say I have two worksheets that have information on all of my clients in them. Sheet1 has client name + contact, title, phone, mailing address, and Sheet2 has client name + email address. I want one worksheet that contains everything. I can write a VLOOKUP formula in Sheet1 that tells Excel: “Matching off the value in cell A2, look for a match in Sheet2 in A2:B200, and bring me the value in the second column.”

        Or, in Excel-ese: =VLOOKUP(A2,Sheet2!A2:B200,2,0)

        HOWEVER, for this to work, client name has to be unique – no duplicates within each workbook – and it has to be identical across workbooks. If either of those things isn’t true, add a column with a unique identifier that you can use to match across workbooks.

        1. amaranth16*

          And, the beauty of this is that it will still work if not all of my clients are in Sheet2 – it’s a way for me to get the information I do have into Sheet1 without having to pick through all the records to see who I have email addresses for and who I don’t.

        2. Ellie H.*

          Oops, should have refreshed. Amusing that both our examples used email address. It must be a particularly fruitful use of vlookup :)

      4. Ellie H.*

        Say that you have one spreadsheet that has a huge list of all the students in an entering class, and in the columns next to their names it lists their email addresses and a bunch of other data like their gender, birthdate, etc.
        You have another spreadsheet that is a list of students who signed up for a particular orientation session. In this one list, you just have their names (you unfortunately forgot to ask them to write down their email address when they signed up), but you need to email them to tell them where the orientation session will be. What to do? You enter a vlookup formula in the cell next to each student’s name. It will search your first table (the one with all the data) by the student’s names, pull out the email address listed in the same row and deliver it for each student. So basically, it’s a way to pull out information contained in a table when you have one particular index (included in that other table) to search by.

      5. The Other Dawn*

        Our staff accountant used vlookups every month to update a spreadsheet containing our bank’s loan customers. She had to keep track of all of our customers’ loan balances. She created a list of all loan customers. Each month she would download and dump the loan trial balance into another Excel spreadsheet and then do a vlookup. What would happen is it would look up the customer’s name on the trial she brought into Excel and it would find the balance of the loan, then it would plunk that balance into her list of all the customers. That way she didn’t have to create the list from scratch each month.

    3. fuyu*

      I think sumif is better. It’s much easier to understand and use, if you have excel worksheets over 5 MB using vlookup takes longer to calculate, and if someone accidentally inserts columns into the data the vlookup is getting the information from, the vlookup formula won’t update.

      1. amaranth16*

        I’m not sure I understand the comparison. SUMIF and VLOOKUP have very different purposes. SUMIF isn’t used for moving information from worksheet to worksheet, which is what VLOOKUP does. VLOOKUP won’t summarize data, which is what SUMIF does.

        1. fuyu*

          If all the values are unqiue, then sumif will work like vlookup when values in the sum range are numbers. Sorry, I forgot that people also use vlookup when the values are text.

      2. amaranth16*

        Also, VLOOKUPS can indeed dynamically update if there is new source data – the data just has to be inserted within the existing range. For instance, it won’t read new rows/columns you add to the end unless you extend the formula’s range manually, but if you insert them (e.g., insert a new column in the second-to-last position), the VLOOKUP will read the new data just fine.

        1. fuyu*

          I meant you might have to go back to the vlookup formula and change the column number if you were to insert a new column within the existing range and you wouldn’t have to for sumif.

          1. amaranth16*

            Ah, gotcha. You can write in dynamic column numbers (think N+1, or a cell reference), but if it wasn’t done in the first place, it sure won’t help after the fact! :-) This is another reason I like INDEX(MATCH) – as long as the data you’re matching from is still in the array, it doesn’t get screwed up if columns are reordered. But in large workbooks, or if you have a lot of active formulas, INDEX(MATCH) will take up a lot of memory…

    4. AnotherAlison*

      I feel like offering my services as an Excel freelancer now.

      Vlookup is used when you want to add a piece of data from one data table to another data table.

      For example, yesterday, I exported a mapping dataset that did NOT export the city. So, I exported the entire database from somewhere else in the system and used vlookup to match the “PPKey” field from the mapping data to the “PPKey” field in the full database and return the City to include it with my mapped data set.

      looks something like =vlookup(N4, wholedatabase!A1:Z5000, 5, false)

      It takes N4 from one location, looks for its match in a second location, and returns the 5th piece of data in that row. It scans the data vertically, hence vlookup, not to be confused with hlookup.

    5. A Bug!*

      I agree with this. There are lots of things in Excel that are probably extremely helpful, but may not be helpful to me because those things aren’t relevant to how I use Excel.

      That said, from what I am reading about vlookup, it may be the solution to a problem I’ve been having.

      (For Excel smartheads in here: my “problem” is that I use a spreadsheet to track certain items, with one row for each item, with several columns containing information about those items – say, “type” “description” “date created” “creator name” – and what I would like to do, is create a worksheet that self-populates with data from the main worksheet. Say, I want a master list with everything, and then a sub-list made up of only the entries with a specific “creator name”, so that when I add something to the master list, if it matches the “creator name”, it automatically gets added to the sub-list as well. Is this something that might be do-able with vlookup? I’m not clear that it is useful when there are multiple matches to the vlookup-ing term.)

      1. amaranth16*

        A straight VLOOKUP won’t work if there are multiple matches, but is there a reason you need a separate sublist rather than using Excel filters in the master list to do this? For instance, if you add a column to your master list where the value is 1 for, e.g., “preferred creators” and 0 for not, you could simply apply a filter to that column to view all the members of the sublist. You could certainly write an IF statement in the new column that would say “if creator name is Mrs. Smith or Mr. Jones, fill in 1, otherwise fill in 0″ — in Excel: =if(or(A2=”Mrs. Jones”),(A2=”Mr. Smith”),1,0″

        It’s generally good Excel hygiene not to have the same data live in two places if you can help it – much harder to maintain data quality that way, as it sounds like you’ve experienced! – so you might want to consider whether it would be OK to use filters within the master list instead of a sublist approach.

        1. A Bug!*

          I want the sublist to essentially be a filter, one that makes it so data shows up in multiple places but I only have to maintain it in one.

          The reason I’d like a second spreadsheet with the subset, is that I have a word document that links to the spreadsheet and populates that with the contents. It currently links to the “master” list, but it means I have to maintain a second list that includes items that need to be left out of the Word document – as you say, having data in multiple places, which lends itself to mistakes.

          I guess if there were, alternatively, a way to populate a master list with the contents of two or more separate lists, because there’s no overlap between the subsets. As long as there’s only one place I need to enter a given piece of information, and it results in having both a combined list and separate lists, it will suit my needs.

          1. Frieda*

            When you say that the Word doc links to the spreadsheet, what do you mean? Are you populating fields in the Word doc (like a Mail Merge) or embedding a chart or something?

            There may be a way to solve this problem on the Word end is what I’m thinking.

          2. amaranth16*

            I gotcha. You could write a mirror worksheet into the workbook (set A1 in the worksheet to = A1 in the master list, and then copy into the rest of the worksheet), and you could nest that within an IF statement that would say only to do it if creator name = X or Y, but then you’d have a worksheet with gaps in it. I feel like there should totally be a simple way to do what you’re trying to do, but it’s not coming to me. I’ll think about it a little more!

            1. A Bug!*

              Oh, please don’t put yourself out for my sake! I appreciate how much thought you’ve already put into it.

              It really does sound like a thing that shouldn’t be that complicated, so I was just hoping that someone else more familiar with the software would be able to say “Oh, yeah, you want this feature, used with this other feature.”

              I’m generally competent with Excel, and even though my setups can get kind of , uh, Rube-Golberg-y, I can usually make them accomplish what I need, with Google’s help. But this one’s stumped me for a while and I’d basically given up on it. At this point, I’ve probably spent more time trying to figure it out than I’d ever save by having it.

              (Thanks also to OmarF and Frieda for your input. I’ll look into your suggestion, OmarF. Frieda, it’s a pretty simple arrangement. Say I have an xlsx with columns A-G, with all plain text content, no formulas or whatever. For the docx, I selected columns C-F, and stuck them in as a linked worksheet object. In the docx it ends up just looking like any other Word table. Unlike the other issue, this one was pretty intuitive.)

              1. Khushnood Viccaji*

                @A Bug! – have you tried using a PivotTable ?
                This sounds like you need just a “Report Filter” (or Page) field at the top of the sub-list, for each category that you need on a separate worksheet.

                This would be a one-time setup, after which you can link this PivotTable sub-list into your Word file.

              2. Frieda*

                I wonder if you could set it up as a Mail Merge? You can write If…Then…Else statements in mail merge, and nested ones too. So you can add a field to a Word doc that says “If ABC is in column A of this Excel spreadsheet, put the Column B data here;if ABC is not in coulmn A, put the data in Column C here” (or no data, or put another If/Then statement there).

              3. Rachel*

                This sounds like a Pivot Table to me, too, though I’m by no means an Excel whiz. (I am so excited about this thread. I’m reading everything and bookmarking links for later.)

                I don’t make Pivot Tables enough to know how to do them intuitively, so I would just start the wizard and click around until you get what you are looking for (that usually works for me).

          3. OmarF*

            You can do it with Advanced Filtering, but that can be difficult to set up until you get the hang of it.

            Basically, you have to set up a criteria range to determine what the filtering should be, and tell Excel you want to have the results in a different spot (not filtered in place). Because the advanced filter can change so you have to manually adjust the settings, I sometimes set up a macro for this.

      2. Jules*

        I would utilize MS Access for that since the query would do all the work for you. If you have access to Lynda.com, they have a great tutorial on how to utilize query to extract data you want. I use to utilize Excel a whole lot but now I find it easier to extract information from Excels by utilizing Access. Especially useful if you need the same set of data regularly. All you have to do is refresh the source table and your query will produce the result you want.

  27. Malissa*

    sumif is the best function ever! Best way to take raw data and get a quick summary.
    Also you can edit/format multiple tabs at once if you use control to select them all.
    Text-to-columns to break up data that congregated in one column. Also if you want only the first three of a string of numbers =RIGHT(cell, 3) Works with left as well.

    1. LadynMax*

      Another helpful tip for extracting part of data from data lumped into one cell…for instance, if you need all data except the first 3 digits/letters from each cell, but the data varies in length…use =RIGHT(A1,LEN(A1)-3). Essentially, LEN counts the number of characters in a cell (including spaces), so excel then extracts all characters/digits except for those three.
      So:
      123ABCDEFG becomes ABCDEFG
      456HJKLSMDSA becomes HJKLSMDSA

    2. T.*

      Thank you for the tip on formatting multiple tabs! I needed to do that this morning and was thinking there had to be an easier way!

  28. Khushnood Viccaji*

    One of the coolest things I learnt long ago was using Excel VBA to write macros, which automate many of my frequent tasks.
    And then save all those macros into a ‘special, auto-loading workbook’ called PERSONAL.XLS.
    I used to get funny looks from people when I’d tell them to use this feature, because they thought I was willing to share something ‘personal’ with them ! :p

    People who’re interested in this can visit this link –
    http://chandoo.org/wp/2013/11/19/secret-agent-kvs-chops-whats-in-his-personal-macro-workbook

    (ps: I’m the ‘KV’ referred to in that post :) )

    1. Windchime*

      Yes, and then you can assign your macros to a button and voila! Every time you press the button, the macro runs. I used to have to take files, put them into Excel, format them (bold the headers, freeze the panes, format the numbers, etc) and then send them off to the bigwigs of the company. My life got a whole lot easier after I made a bunch of macros to do this work for me. All that formatting can be so tedious.

      And someone up-thread mentioned that Excel can’t record keystrokes in a macro–that hasn’t been my experience. I would usually start recording, do the keystrokes, stop recording, and then you can go into the VBA code and edit if you like.

      1. Khushnood Viccaji*

        @Windchime, that someone was me :)
        What I wrote was that you can’t record the actual keystrokes for a command using the Excel macro recorder.
        E.g. the keystrokes for PasteSpecial values would be
        Alt, E, S, V, ENTER

        So when you record the macro for this, Excel will record it as :
        Selection.PasteSpecial Paste:=xlPasteValues, Operation:=xlNone, _
        SkipBlanks:=False, Transpose:=False

        and not as:
        {ALT} E S V {ENTER}

        This used to be the case the days of Lotus123 ;-)

        1. BigLaw Refugee*

          Right – and it doesn’t always record everything you do or implement the commands the way you’d like. But I’ve taught myself a lot of VBA by a) recording my keystrokes, b) reviewing what Excel did, and c) doing a Google search for the commands that don’t seem to be recording correctly.

  29. Samantha*

    One tiny/obvious thing that I think is super useful is that if you hover over a selection of number cells, it will tell you the count, sum and average of those numbers in the bottom right hand corner. Handy if you just need a quick sum and don’t need it in the table.

    Also, if people don’t know the trick you can look like a wizard who adds numbers in their heads instantaneously!

    1. Chrissi*

      I don’t understand this, but want to. Do you mean that you highlight cells with numbers in them then hover the mouse over that? I have Excel 2007 so it may just not work in mine.

      2. Khushnood Viccaji*

        @Chrissi, you just have to select (highlight) the range of cells for which you want to see the SUM / AVERAGE / COUNT /MAX / MIN.
        Based on the selected cells, the calculated values will show in the bottom right-hand corner of the Excel window (in the Status Bar).

        The mouse can be moved anywhere in your Excel window, but these results will be displayed until you move the cell selector (to select some other cells).

      3. bagworm*

        It should be in 2007, too, but I don’t remember if it shows all three though. And this can be turned off or you can change which values are displayed. In 2010, you can display any or all (or none) of the following: Average, Count, Numerical Count, Minimum, Maximum, and Sum. Just right click on the status bar at the bottom of the page and check or uncheck what you want.

      4. Samantha*

        Definitely works in 2007 since that’s what I have. The other comments explain very well, but let me know if you still have questions!

      1. Tax Nerd*

        I loved hovering to get a sum. Right click on the Status Bar down at the bottom, and make sure average, count, and sum are selected.

        I am a big fan of VLOOKUP, SUMIF, and filtering, but those have been covered. For what I do, Pivot Tables rarely come up.

        My other favorite discovery was F4 repeating whatever I did last. Like if I insert a row, then decide I want another row inserted, I can hit F4. It also works for formatting. I just made A2 bold, and now I’m in B2, I can just hit F4, and it will repeat the last action of making something bold.

  30. LCL*

    Custom toolbars! Floated in the worksheet! Especially useful if you use a few commands repeatedly.
    Tools/customize/new/toolbar name (whatever you want)/OK/commands
    Drag and drop the commands into your new toolbar.
    Float the toolbar wherever it fits into your worksheet.
    Also useful for driving others crazy if they look over your shoulder-I have heard that floating toolbars “just ain’t right”.

      1. LCL*

        And if you want to see IT support become all disoriented, set up your (windows) machine with the task bar at the top. It seems much more organized that way, to me. But when IT had to remote into my machine, they were appalled.

        1. Noah*

          For some reason this drives me batty too. My boss prefers the task bar at the top and it always looks weird to me. To each their own, but I’ve never felt the need to move it and it just looks weird at the top of the screen to me. She does use a Mac at home, I never really made the connection until now.

        2. Ethyl*

          I auto-hide the task bar and it has created problems when IT has to remote in to my computer or when people are looking over my shoulder trying to tell me how to do things. But I just hate the extra space that’s taken up!

  31. Ex Mrs Addams*

    It’s hardly cool but it’s invaluable if you hate switching between mouse and keyboard – pressing F2 will allow you to edit the contents of a cell without having to double click or type over what’s already in there.

    It also works for renaming files in Windows – select the file, press F2 and you can rename without right-clicking.

    1. Anonymous*

      oh my god. I have been using a million keyboard shortcuts since the days of DOS and somehow I never ever knew this one. You have just made my life quite a lot easier.

      1. Tax Nerd*

        This is super awesome. Especially if it changes file names. (I seem to live to change file names.)

    7. tcookson*

      I just opened a test copy of my excel file so I could try this, but my F2 key is also a hot key for turning the Wi-Fi of and on, so it won’t let me use it for anything else. I wonder if there’s some way to turn that off and have it just be an F2 key again?

  32. TL*

    The best tip for Excel is to flip through one of those Excel instructional books (at a bookstore or library, don’t need to buy) one day and just note what Excel can do.

    Then when you have a problem, all you have to do is Google. So many people underuse Excel because they have no idea how powerful it really is. They think it’s just a glorified calculator.

    Once you know what Excel can do, you’ll see a problem and think, “Oh! I can do that in Excel!” and Google’ll do the rest.

    1. Jen in RO*

      Flipping is not even necessary. I just assume that Excel is smarter than me and can do anything I could think of… and then I google and find out that indeed, it can. (And make my coworkers think I’m smarter than I really am.)

      1. TL*

        I just assume that Excel is smarter than me and can do anything I could think of

        It’s getting people to realize that that’s the pickle! I’ll never forget the time one of my group mates in college started complaining about spending 2 hrs doing something in Excel – the look in her face when I broke in and said, “Why didn’t you just do X? Even with learning how to do it, it shouldn’t have taken more than 30 minutes?”
        She has Excel as one of her skills on her resume. :(

  33. Laurie B.*

    I do a lot of mail merges using lists built in excel and sometimes I’m pulling info from multiple workbooks, some where the names are separated out into 2 columns but I need them in one for my mailing list, so I love the CONCATENATE formula it lets you merge the contents of multiple text cells into a single cell.

    And I also agree that VLookup is great!

    1. Ex Mrs Addams*

      There’s a reverse to that too – text-to-columns allows you to take the text in one cell (e.g “Ex Mrs Addams”) and if you select space as a delimiter, it will separate the words into separate columns (e.g “Ex” “Mrs” and “Addams”). Very handy, particularly for separating first/last names into separate columns.

      3. Leslie Yep*

        Text to columns was a game changer for me. You can also separate out the data using any other marker, so if you have everyone listed as “Mrs. Sally Smith” and “Mr. John Doe”, you can make the separator a period, and you’ll get a column with title and a column with full name.

    2. The Other Dawn*

      I seriously love CONCATENATE! I worked in a bank and there was a program I used that could affect changes to multiple accounts at once. For example, if we wanted to start charging for online bill pay I would have to change a flag on each customer’s account we wanted to charge. That flag would tell the system the account should be charged if they sign up for online bill pay. For something like that we would usually want to make that happen on ALL checking accounts. Well, if we had 2,000 accounts, I’m not going to input all of that by hand. So I would download a trial balance of all our checking accounts and dump it into Excel. I would then have to ensure it’s formatted correctly in order to “feed” it into the program I used to make the account changes. The trial balance would format the customers’ account numbers as 99-99999999, but the program needed the account number to be 9999999999. So I would do the LEFT and RIGHT functions, then concatenate the account number in order to get the right format.

      Can you tell I love concatenating??

      1. AnotherAlison*

        Do you know about concatenating without the function?

        Instead of =Concatenate(A1, ” “, B1)

        you can do

        =a1&” “&b1

        It’s a matter of taste, but concatenate is a long word to type.

    3. Kelly O*

      I was looking for someone to mention CONCATENATE.

      When I worked at the travel agency, I learned that one and it absolutely changed my life. The best part is being able to put spaces or characters between it, or just a “” if you only need the character string.

      I still feel smarter than the average bear when I use that one.

      The other simple one that I like is F5 – Special – Blanks for deleting blank columns or rows.

    7. Katie*

      I use excel to maintain databases which are mostly just plain text but occasionally need to have html formatting typed in and saved as a csv file (Don’t get me started on wrong tool for the job but whatever). CONCATENATE was so useful – I had a column of the organisation’s name, and another with URLs and then I concatenated to get NAME website so there were no errors in the html and I didn’t have to type every single one by hand. I’d been doing some of it with find and replace (replace http with <a href="http ) but it was still taking forever.

  35. Ursula*

    I create a lot of charts – we have “visual systems” for everything – and one thing that is wonderful is using visual basic to automatically format the colors of the data points on the charts when compared to a certain (or multiple) target.

    So macros, I think, are among the most useful tricks for excel.

    Oh – named ranges as well. They are really helpful with both vlookups and macros.

  36. Zahra*

    Oh man, Excel tips!

    Ok, learn If and all it’s variants (sumif, countif, iif, etc.).

    Conditional formatting

    Pivot tables (and pivot graphs, which are graphs made from pivot tables)

    Vlookup and Hlookup (basically, tell it to check a value against a table to return another value. For example, have a table of store numbers paired with store names, cities, etc.. On another sheet, you have store number, but you want to add store names, cities for easier filtering or pivot table readability. You use Vlookup or Hlookup depending on how your reference table is organized.)

    Named ranges (for example, for that Lookup table, give a name to the cell range, so you can build your formula without referring to the cell references (A1:D50 could become “StoreRef”)

    Sum and count by filtered rows (or by all data, not just filtered data). (Subtotal function)

    Also, learn to Google “Excel 2010 (or whatever version you have) [thing you want to do]”. Odds are, someone had the same issue.

    1. Ex Mrs Addams*

      “Also, learn to Google “Excel 2010 (or whatever version you have) [thing you want to do]“. Odds are, someone had the same issue.”

      +1000. This is how I learned (and still learn) how to do things in excel. Finding the right key words to describe your problem/what you want to do can be a little tricky, but try a couple of variations and you should hit on something.

      My coworkers think there’s nothing about excel I don’t know – truth is, there’s nothing about excel that Google can’t find out, but my coworkers don’t need to know that…

      1. Gail L*

        Same for me. I find this is true of computers in general as well. People think I am “good” with computers. No, Google is good with finding other people who are good with computers. I just read stuff and try it out.

        1. Leslie Yep*

          So true. I kind of don’t want my colleagues to find out how often I solve their problems with google or just plain old clicking around until I see something that might work. They think I’m magic.

        2. Another Ellie*

          The number of times my coworkers have suggested that I call IT for a problem that I fix five minutes later by googling, without involving IT….

      2. Jen in RO*

        I *told* my ex-coworkers that it’s all google… and they still thought I’m a wiz! I was actually surprised how lazy people can be.

  37. A Jane*

    Ok, here’s a non-useful Excel trick, but fun.

    For excel 2010/2007 users, you can change your Excel (and rest of MS Office Products) color scheme. Under the File Tab, select Options. Under User Interface options, you should be able to change the color scheme to something other than standard.

    1. A Jane*

      I recently upgraded to 2013, and one of the first things the setup wizard had me do was choose my scheme. I now have an underwater menu bar scheme.

  38. Bryan*

    I’m a big fan of freeze frames. If you click view there is a freeze frames button or you can just hit alt+w, f, then f again.

    Does anybody know how to get it so if you open multiple files in excel you can put them side by side? In word it treats multiple files as multiple programs but in excel they’re all windows within the program and I can’t just drag to the side to have it take up half the screen.

    2. Zahra*

      View, “Show (or something to that effect) side by side”. It’s to the right of the freeze pane button, like the 2nd column of buttons over.

    3. Gail L*

      I just end up resizing both windows so they take up the half I want. There’s an official way to do it in the “View” menu called “View Side by Side” but I kept getting annoyed at it.

      1. Geof*

        Not an Excel shortcut, it’s a Windows 7 shortcut, + will autosize/snapto a window to the (left or right) side of your screen. Makes the the careful resizing of windows so much faster.

        2. Jen in RO*

          Thanks for the Windows 7 tip! I kept pressing that shortcut by mistake and I didn’t even knew what keys they were!

      2. Marcy*

        I use the “View” “Arrange All”, “Vertical”. It puts all of your open spreadsheets next to each other.

    5. Apollo Warbucks*

      If you are using windows 7 hold down the control key when open the program with the mouse this should open a second version of the program and have them both shown separately in the task bar at the bottom of the screen

    6. anonn*

      Click onto the same Menu as Freeze Panes. Then look below and there will be “arrange” options. Once you have it with the windows open you can set up whatever arrangement you like. However note that if you want to scroll up and down then you will need to turn the “link scroll” option off on the little options box that will be floating on the screen.

  39. Kimberlee, Esq.*

    My favorite trick is that Excel is really good at pattern recognition.

    So if you have a column where you write Mon on the top cell, Tue under it, then highlight both cells, and mouse over the bottom right corner so you get the black crosshairs, and click and drag, it will fill in all cells you’re highlighting with Wed, Thurs, Fri, etc.

    I’ve done this with random numbers as well, just to see what kind of pattern it will try to replicate. :)

    This also works with formulas, of course, clicking and dragging will copy the formula onto the new cells, but using the corresponding cells for the math, rather than the original cells (using only words to explain Excel is really hard!)

    1. Ellie H.*

      This drives me crazy more than I find it useful. I most often want to copy and paste the same date into a few adjacent cells and I wish I could drag it the same way you can drag numbers or a text value.

      1. TL*

        Store the values as Text (in the columns) and it should let you drag a copy instead of a pattern.

        It’ll automatically store as a date, time, or number, so you have to go back and change it manually.

        1. Khushnood Viccaji*

          @Ellie H., @TL, if while dragging with the mouse, you get a pattern or series, keep the Ctrl key pressed, and it will copy the values instead of creating the pattern.

          2. amaranth16*

            Oooh, this was new to me and I was so excited, but I just tried it and it didn’t work for me! Is it limited to Excel 2013?

            1. Khushnood Viccaji*

              @amaranth16- this feature has been there since a long time.

              You can find more information about it on these web-pages –
              Auto Fill Lists of Data with the Fill Handle
              http://spreadsheets.about.com/od/exceltips/qt/2010-09-02-excel-2007-fill-handle-auto-list.htm

              Copy Formulas with the Fill Handle
              http://spreadsheets.about.com/od/excel2007tips/qt/07_fill_handle.htm

              Adding a Series of Numbers with the Fill Handle
              http://spreadsheets.about.com/od/exceltips/qt/071102_fill_ser.htm

              1. amaranth16*

                You know what, I think I just misunderstood what you meant the first time – I see what you’re saying.

                1. Khushnood Viccaji*

                  Good for you :)
                  But if you go to the links, you’ll probably learn something new.
                  I often learn many new things this way !

            2. TL*

              It works in the really old version of Excel I’m in now (lab computers!) but I had to use the alt/option button (Mac) instead of the Apple or Control one. So maybe try Alt on Windows?

              1. Khushnood Viccaji*

                @TL, it’s the Ctrl key alright in Excel for Windows.
                Holding down the Alt key while dragging the Fill Handle has no effect.

                1. TL*

                  Huh. Normally the control key in Windows translates to the command key in Macs. ( I figured maybe they had changed the keyboard shortcut from the older versions – the computer I was on was probably 10 yrs old)

                  But, nope, it’s the Alt key on my new Mac, too. Weird!

        2. amaranth16*

          Ooohhh, I hate numbers stored as text because unless you know the trick about multiplying the whole column by 1, you can only change it back to text one cell at a time – and any formulas you write looking at those cells will fail until they are no longer stored as text.

          The fill handle will work to fill down the same value as long as the value is selected twice in a row. So type 100 into A1 and A2, and then you can use the fill handle to fill 100 down to your heart’s content. And then you don’t have to do anything to fix it afterwards!

          2. TL*

            Changing one cell at a time? I just select all the cells that I want to change – usually a column – and change them all at once.

            The only problem I have with cells stored as text is when someone locks an entire column and gives me the file. I, for some reason, have to delete the column and redo it change from text to whatever. But I’ve never had problems otherwise.

            1. amaranth16*

              Huh – for some reason the version of Excel I’m using will only actually change a number stored as text to a number if I select that specific cell, not ranges of cells. From MS’s website it seems like that’s a bug and I guess it’s not a universal one. But I’ve had numbers-as-text give me headaches in enough documents that I just don’t mess with them. :-)

              1. TL*

                I’m always changing the format anyways, thanks to the spreadsheets I deal with, so it’s not a big deal to me to do that back and forth – I’m there anyways. And thankfully, no bugs in my Excel.

                But yeah, that would be incredibly. annoying. to deal with!

              2. Khushnood Viccaji*

                @amaranth16,
                To convert numbers stored as text, back to numbers, you can try one of these tricks –

                1. Assuming that the offending original value is stored in cell A1 and below, in the column next it, type the formula =A1*1, and copy it down as far as you want.
                Then copy-paste it as values, over the original data (or even leave it as it is).

                2. In any cell away from the numbers, say D1, type the number 1.
                Press Ctrl+C
                Now select the offending range of numbers (stored as text).
                Press Alt, E, S, M, V, ENTER
                (You are using paste special to multiply the original values by 1, and over-writing them as numerical values).

                3. Select the range of numbers (should be in a single column only).
                Press Alt, D, E to show the Text-to-Columns wizard
                Select ‘Fixed Width’, and click Next
                Click on Next again
                Select ‘General’
                Click on Finish. Ta-da !

                As always, try all these on a TEST file first :)

              3. Anonymous*

                Best way to change between data formats is to use Data -> Text to Columns and in Step 3 choose the Column data format. If your number stored as text is the result of a formula this will not help you. Either convert to value first, or use the Value function if you would like to keep the formula.

      2. bagworm*

        If I understand your frustration correctly, if you want to copy a value into multiple cells by dragging, you can do this. (In Excel 2010), after you do the drag, you should see a little box at the lower right-hand corner of the cells you filled. Click on it and you can select how the cells are filled. One of the options is Copy Cells. Click this and you should get the data copied versus filled by some Pattern.

  40. Gail L*

    Oh! One more. $ signs in formulas to keep on referencing a particular cell.

    If you have a formula and you want to add something, you would say =B11+B12

    But say you want to copy that formula and keep B11 the same, while adding up other values?

    Type like this: =$B$11+B13

    You can copy/paste (or drag) that formula all over the spreadsheet. B11 will always remain, but B12 or B13 will move around. This is really helpful for me making a table that has a currency exchange rate. I need to multiply values all over the table by the same exchange rate that is in one cell. So that cell reference gets the $.

      1. Gail L*

        Depends what you want to do. The $B says to Excel “don’t move that B!” while the $11 says “don’t move that 11!” (and both will tell it not to move either of them)

      3. Show me the $*

        Not quite…the $ in front of the B locks the reference to the column, the $ in front of the 11 locks the reference to the row. (So you still need both)

        Supplementary tip: if you have typed in a cell name & forgot to put in the $’s while you have your cursor in the text of that cell name, repeatedly push f4 to cycle through the different kinds of reference designators (ie cycle between b11, $b$11, b$11 and $b11)

    3. Anon*

      This is also useful for vlookups – you need to lock the cell references to the columns it’s searching. E.g. =Vlookup (a2, $g$2:$h$100,2,false).

      Otherwise, it will search rows 3-101 for a3, and 4-102 for a4, and so on. There will be increasingly few matches as the range moves away from what it should be. The dollar signs make sure it is checking the data in rows 2-100, and nothing else.

  41. Laura*

    I combine the index and match functions to create one very long formula that essentially allows me to complete my work. I am TERRIBLE at explaining excel so I encourage you to look up both the index and match function.

    I use them both together and they essentially allow you to do vlookups in both directions, at the same time, and don’t limit you to looking up something in the same column. I export thousands of rows and thousands of columns of survey data, and a quick index/match function allows me to FIND a particular column (without me telling excel which one) and then perform a function to that entire column (such as summing or counting).

  42. Laura*

    I also exclusively use countIFS or sumIFS, instead of the simpler version. it allows me to cut data in a variety of ways by only counting something IF it meets a certain criteria. Such as…

    Only count the number of orders we got in January from females.

  43. Joey*

    Pivot tables for easily viewing large amounts of data, formulas that link data between spreadsheets, and protecting data are probably my favorites.

  44. Megan*

    I love the text to columns feature in 2013, it’s nice when dumb people send me annoying spreadsheets with first and last names in one column.

    My theory with Excel is always if I’m spending more than 3-5 minutes on a task, to google what I’m doing & figure out how to automate it.

      1. Megan*

        I think there used to be something similar, but it required a formula. Now it’s just a button to click.

        1. Khushnood Viccaji*

          @Megan- the text-to-columns feature has been around since Excel 95 ;)

          @the gold digger- there was no ‘parse’ function as such, but yes, this feature is used to parse data into multiple adjacent columns, which would otherwise end up showing in a single cell only.

  45. Susie*

    Chandoo.org is the Excel version of this site basically. So hang out there for nifty Excel awesomeness.

    Most of the more complicated stuff they talk about is over my head but that’s because I need actual data to understand it. There’s always dummy spreadsheets included that you can play with so it’s amazing.

  46. OmarF*

    Here’s a simple one. I discovered that the last day of any month is day zero of the next month. So, =DATE(YEAR(TODAY()),MONTH(TODAY())+1,0) will get me the last day of the current month. You also put negative numbers, or even numbers greater than the number of days in a month. So if you terms of 45 days from the next month’s first day (don’t ask), Excel can do it for you. Of course, there are lots of ways to get the last day of a month.

    I think the greatest feature introduced in Excel 2007 is the structured tables. I no longer worry about the beginning and ending rows when setting ranges. I just do SUM(Table1[Column2]). There can be zero rows, or 1 million rows. I don’t need to know when I set up the formula. Excel doesn’t care what worksheet the table is on. It will find it. Of course, the table names can be edited, and the column names are just the text in the heading cell.

    If you use INDEX/MATCH instead of VLOOKUP, you don’t even need to worry about which column number to look for. Eg. INDEX(Table1[Column2],MATCH(A1,Table1[Column4],FALSE)). This will find the row number of an exact match to whatever is in A1 in the Column4 of Table1. Then it will go to the same row of that table, and return whatever is in Column2.

    Second greatest is the variation on SUMIF and COUNTIF. They are now SUMIFS and COUNTIFS. These allow multiple conditions. So you can add up the sales of Chocolate Teapots by the Western branch only on Sundays.

    I could go on. I get soooo excited by Excel!

    1. DATAnerd*

      A simplified version of getting the last day of the current month:
      =eomonth(today(),0)
      1st day of current month
      =1+eomonth(today(),0)
      Change the ,0) to change months
      ,1) will give you last day of next month
      ,-1) will give you last day of last month

  47. jeREMy*

    Finally! My bread and butter is getting discussed on AAM. I work as an IT analyst for a non profit. I can write a vlookup formula in my sleep. Here is a brief list of “advanced” excel techniques I use:

    * Learn the keyboard shortcuts. I can select and manipulate data much faster because I never have to take my hands off the keyboard.

    *learn to sort and autofilter data correctly. It is very easy to sort excel data incorrectly. The autofilter is helpful when trying to find a common value in a set of data. For example, I want everyone in my address list who lives in NJ to get a postcard. I will autofilter the data for state = NJ. From there I will use keyboard shortcuts like CTRL+SHIFT+DOWN and CTRL+SHIFT+LEFT to select the data set and copy it into another workbook. From there I can do a mail merge for labels.

    * VLOOKUP and other formulas. VLOOKUP is used when you have two sets of data where each has a unique identifier that you want to match. Google this formula for more.

    * Concatenate cells using the & symbol. I know that there is a formula called CONCATENATE but I hate spelling that out. Here is an example of how I use & in a formula. Say I have City, State, and Zip in columns A B and C. I want to get these into a new column called ADDRESS LASTLINE. The formula would be =A1&”, “&B1&” “&C1. Note that this will combine all three fields and add in the proper comma and spacing as denoted between the double quotes.

    * TRIM() formula gets rid of extra spacing in a cell.

    * LEN() will tell you the length of characters in a cell. This is useful to see if a zip code is the proper 5 or 10 characters long.

    * PROPER() this will capitalize the first letter of every word in a cell. Useful for names.

    *LEFT(), RIGHT(), MID() These three formulas will produce a subset of the cell that they point to. For example: In cell A1 I have a zip code 08080-1234 but I only want the 5 digit zip. You write a formula =left(A1,5). This tells excel to take the first 5 digits of cell A1. It would output 08080. If you did –right(A1,5 you would get -1234. MID() works like this. Using the same A1 example =mid(A1,3,4) will output “080-“ to translate the formula into words: Take cell A1 and give me 4 characters starting at the 3 character from the left

    There is much more, but my lunch break is only so long. I also want to point out that with excel there are usually more than 1 way to do something so don’t bite my head off if you do it differently.

    Explaining pivot tables would take another 5 pages of notes. They are powerful and very useful for analysis. Perhaps another time I could discuss them. For now, Google is your friend.

    1. Gail L*

      Yeah, it takes about 10 minutes to show someone PTs, but I don’t know how to explain them, other than they summarize, slice, and re-organize data sets.

      1. the gold digger*

        If you have 60,000 lines of raw data – a line for each sales transaction that includes product name, product type, customer country, revenue, and cost, you can use a pivot table to summarize that data into a report that shows total sales by country, by product type, and/or by customer and you can show the revenues and costs and calculate the margin.

        Then you can move elements around to show product sales by country or country sales by customer or any combination you want.

    2. Chrissi*

      You are my hero! After years of being told that I should learn Pivot tables, I only finally did it when a client sent me a workbook with 13000 rows of data. Then I finally fiddled around with it and it’s really not hard to use at all – it just doesn’t make any sense unless you’re working with pretty large datasets.

    3. Ellie H.*

      Concatenate is great. I’ve done it both ways (your typing way and “CONCATENATE”). Everyone at my organization has standard email addresses (first.last@) and I use it to create email addresses from names very quickly. It’s so useful.

    4. junipergreen*

      Also CTRL and a direction arrow will get you to the next cells with data entered – so if you wanted to get to the bottom of a column with lots of empty cells, CTRL + down arrow will help you easily navigate. (adding shift helps you select, as mentioned above)

  48. GlorifiedPlumber*

    Some hacks I use routinely:
    – Ctrl+5 for strikethrough (will work on specific portions of text highlighted versus the whole cell)
    – F4 for repeat last command (works great for routine highlighting and line inserts, or formatting)
    – F4 when on a cell reference to cycle between relative/absolute
    – Learning to use the MATCH function effectively with VLOOKUP and HLOOKUP (only way to learn is to dive in and try as someone else has said)
    – COUNTIFS vs. COUNTIF and making a unique field by “&” and two cells
    – the & operator versus CONCATENATE
    – alt, e, s, v, enter for “Paste Special: Values” (alt, THEN e, then s, then v, then enter)
    – Knowing how the INDIRECT function works and when to use it
    – OFFSET can come in SUPER handy, need to know how it works

    1. CollegeAdmin*

      Ctrl+5 for strikethrough (will work on specific portions of text highlighted versus the whole cell)

      OMG this is excellent – I knew there had to be a faster way than going into Format!

    2. TychaBrahe*

      Offset(x,0) works on cells x below your current cell. Offset(0,x) works on the cell x to the right of your current cell. Saves you from selecting different cells in macros.

      ActiveCell.Offset(0, 1).Value = cust
      ActiveCell.Offset(0, 2).Value = name
      ActiveCell.Offset(0, 4).Value = store
      ActiveCell.Offset(0, 5).Value = address
      ActiveCell.Offset(0, 7).Value = zip
      ActiveCell.Offset(0, 8).Value = email
      ActiveCell.Offset(0, 9).Value = phone
      ActiveCell.Offset(1, 0).Select

      Copies a bunch of data into difference columns of a spreadsheet, then advances down one row to wait for the next row of data.

  49. Keryn*

    I work at a university, and I use Excel for various text-based databases that are just easier to read in spreadsheet format (e.g. addresses, classes taken, grades, University ID’s, etc.). I especially love this because I can mail merge data in spreadsheets into Microsoft Word to be used in forms, e-mails, and (of course) letters/envelopes/labels.
    This means that if I have to fill out a Registration form for 125 students, I would open the form in Word, connect the documents, and then put the Last Name field in the Last Name spot on the form, and it will pull the information from the Last Name column to fill in for each student’s form. (See here for mail-merge how-to’s: http://office.microsoft.com/en-us/word-help/word-mail-merge-a-walk-through-the-process-HA001034920.aspx)

    I also really CONCATENATE function for connecting text strings and LEFT, RIGHT and MID functions for pulling out PART of a cell (helpful if you want to insert dashes in a phone number, for example, like here: http://www.techrepublic.com/article/save-time-by-using-excels-left-right-and-mid-string-functions/) and as someone said before, Paste Values and Transpose.

    1. BigLaw Refugee*

      Keryn, I have developed some macros and tools that might be of interest to you.

      1) Do a Word mail merge that creates a separate document for each record, and saves each mail merged doc for you automatically with a standardized file name.

      2) Create email templates for standard emails, e.g. rejection emails; maintain the list of emails in Excel; and use VBA to automatically send emails to all the people in the email list. (You can even customize the email text, as in a Word mail merge, so that it says “Dear Jane” etc.)

      If you (or any other reader) would like to see samples, please feel free to email me at kemnyc at gmail. (I’m super busy right now so may not respond immediately, but I love sharing these tips with people. Please put “Excel/Outlook VBA” in the subject line.)

  50. CollegeAdmin*

    If you have to print out Excel documents and don’t want to lose track of which file it is, create a footer and add the file path by clicking Insert > Footer > File Path. It adds not only the name of the file but also the details of where it’s stored on your computer. (For example, “C:\User\CollegeAdmin\Dropbox\CollegeName\Department\anonymousworksheet.xlsx”) My boss is the queen of disorganized subfolders, so this way I know exactly where the file is.

    Note: This works in Word, too, but it takes more steps. When you add the footer, find the button at the top of the screen that says “Quick Parts” and select “Field…” In the new box, select “File Name” and check the box that appears on the far right that says “Add path to file name.”

    WARNING: The document will ask you every time you go to close it if you want to save, even if you haven’t changed anything – I think it’s because it wants to confirm the file path.

    1. Khushnood Viccaji*

      @CollegeAdmin, in the Word document if you have the full-file-name inserted in the footer, it’s actually inserted as a ‘calculated field’.
      Every time you open the file, the field gets ‘recalculated’, and the file becomes ‘un-saved’. That’s why it asks you to save the file even if you haven’t made any other changes.

      Incidentally, if you move the file to another folder or computer, and open it in Word on that PC, it should show the full-file-name of the new folder, in the footer.

  51. theotherjennifer*

    =PROPER – converts a text string to correct upper and lower case
    TEXT TO COLUMNS – completely invaluable
    CONCATENATE – if you have a program that dumps your data into excel but puts your text (e.g. notes field) into a bunch of different columns you can merge all that text (no, merge doesn’t work) into a new cell with this. Handy for notes, etc. when the text you need is broken into multiple columns.

    1. The Other Dawn*

      Thanks for the tip on PROPER. I haven’t use that one. Actually, there are many functions I haven’t used. I love TEXT TO COLUMNS and CONCATENATE also. So useful!

    3. Joline*

      As many others have mentioned text-to-columns and concatenate are invaluable in certain jobs. They can save me up to hours in a week (an accountant receiving data in a variety of sources from clients).

      One of my favourite non-formula (and analysis) thing, however – format paintbrush. I save so much time by “paintbrushing” my formats from other areas as opposed to pasting them over or doing it manually.

  52. Laura*

    I also love “Data validation”. One use for it is to create a drop down list. For example, If you had a list of employees and were assigning them which shift to work, you can make a column called “shift” and in each cell is a little drop down menu with options such as “morning” or “afternoon” or “evening”. This is great when you are passing around a spreadsheet to a group and want them to fill in a cell with a pre-determined set of options.

    Go to Data on the ribbon and select “data validation” and then “from list”. you can google for a tutorial.

    1. LucyVP*

      I also love Data Validation. Especially for worksheets that many employees work with.

      Creating validations such as drop down menus or even ‘column C must be a date’ or ‘column x must be a numeral between 1 and 10’ helps ensure that everyone is entering in data in the same format.

      1. LucyVP*

        and for people who dont like to switch between keyboard and mouse, when you are entering data into a cell with a drop down list you can hit the Alt key and it opens the list, use the arrow keys to make your choice then hit enter to select it.

  53. Jenny D*

    Fixing capitalization with:

    =proper(cell) – takes JIM TEAPOT to Jim Teapot

    It isn’t always perfect (MacTeapot or de Teapot or van der Teapot would need manual fixing from Macteapot, De Teapot, and Van Der Teapot) but it saves me time for 95% of my entries!

    1. anonn*

      I use these so often I don’t think of them as tricks… although I have had an auditor not know how to use them and call me all confused. He actually thanked me when I showed him how to use it!

  55. LadyHope*

    After a customer service dude made me feel like a moron for not knowing this, I discovered Concatenate. Basically, if you use Excel for mailing lists this is handy… say you have Column A = First Name, Column B = Spouse First Name, Column C = Last Name, then in Column D you can use Concatenate to get A & B C (John & Jane Smith).

  56. Frieda*

    This isn’t Excel specific but I read it while learning SPSS (statistical software that also organizes data into rows and columns): http://vita.had.co.nz/papers/tidy-data.pdf

    It basically explains best practices for how to format datasets that are organized into rows and columns, some common situations in which data that looks “OK” will actually cause you problems later, and how to fix it. It’s sort of revolutionized how I make spreadsheets.

  57. Frieda*

    Also, damn you Alison for posting about my favorite thing just as I’m getting into the “have to get everything done before the holidays” period at work!

    Seriously, the slow realization I had a few years ago that I LOVE using Excel is what motivated me to start a Master’s degree in data science.

    1. AnotherAlison*

      +1
      well, no data sci degree. . .but I do like Excel & I am busy today. . .with Excel. I’m gonna go export a database & vlookup a a Word list someone gave me to add some info to. . .of course names won’t match perfectly. So fun!

  58. Goofy posture*

    Question:

    I have data that I need to summarize under different conditions. I love “averageifs()” for “AND” clauses, but is there a way to do “OR”?

    For example, the difference in our sales when X promotion OR Y promotion is in effect (with X & Y info in separate columns.)

      1. Goofy posture*

        Sorry, to clarify – I don’t actually type “AND” in the averageifs formula; it’s built in that all of the criteria must be met. Is there a way to adjust it to “or”?

        Right now I have this to show me sales for items that are new AND blue:
        =AVERAGEIFS({Weekend sales},{age of item},”new”,
        {color},”blue”)

        But what if I want the average sales for things that are new OR blue?

        1. DATAnerd*

          Break it out:

          =sumif({age of item},”new”,{Weekend sales})+sumif({color},”blue”,{Weekend sales})/(countif({age of item},”new”)+countif({color},”blue”)

          1. DATAnerd*

            Forgot the paranths.

            =(sumif({age of item},”new”,{Weekend sales})+sumif({color},”blue”,{Weekend sales}))/(countif({age of item},”new”)+countif({color},”blue”))

  59. Anonymous*

    I have 2 totally cool but nearly useless ones:

    ROMAN
    because who doesn’t occasionally need to turn in a spreadsheet in roman numerals.

    UPPER/LOWER/PROPER
    since sometimes you just need all caps for something, or you feel like e e cummings, and sometimes others do and you need it to not be.

    1. Lillie Lane*

      Oh, dude. I need to get up the courage to turn in a Roman numeral-filled file to my boss. He would have a cow, and I would die laughing.

    3. JanetM*

      I love the idea of turning in a spreadsheet all in Roman numerals. However, I just tested it and discovered that it only works up to 3,999 – at 4,000 it produces a #VALUE error.

  60. Darcy*

    My favorite formula is averageifs(… which allows me average only cells that meet certain criteria. My favorite function is the very hidden worksheet visibility which you get to in VBA (by clicking Alt + F11).

  61. The Other Dawn*

    My favorites are TEXT TO COLUMNS, LEFT, RIGHT, and CONCATENATE. Probably TEXT TO COLUMNS was most useful in my last job. I worked at a bank and we would take some of the reports provided daily by the core processor and download them into Excel. The data would come in as a text file and then we would then slice and dice for our individual uses.

  62. All I do is Excel*

    SUMPRODUCT is one of the most powerful formulas you can know. It is an array formula so it will multiply across rows of data allowing it to be a multi-criteria SUMIF, a COUNTIF, RANKIF, Weighted average, etc.

    As an example:
    =SUMPRODUCT(–(A1:A10=”YES”)*–(B1:B10>2))
    This will count how many rows there are where column A has ‘Yes’ and column B is greater than 2. The double negatives force the true/false return of the argument into a 1 or 0, so if either argument is false you are multiplying by 0.

    =SUMPRODUCT(–(A1:A10=”YES”)*–(B1:B10>2)*(C1:C10))
    This will sum the values in column C when column A is ‘Yes’ and column B is greater than 2.

    =SUMPRODUCT(–(A1:A10=”YES”)*–(B1:B10>2)*(C1:C10)*(D1:D10))
    This will sum the multiplied values of column C and D for each row where column A is ‘Yes’ and column B is greater than 2

    This example is basically a ‘RANKIF’, its written as if it was pasted into cell D1.
    =SUMPRODUCT(–(A1:A10=A1)*–(B1:B10=B1)*(C1:C10>C1))
    It counts how many cells in column C are greater than that rows (row 1, where this formula is pasted) but only counts that if the corresponding value in column A is equal to the reference row’s column A and when the corresponding value in column B is equal to the reference row’s column B.

    For all of these you’d want to lock the range references, except in the last RANKIF example where you would no want to lock the reference cell (A1,B1,C1). All of the ranges have to be the same height/width.

    Also, learn VBA, its really easy and you will look like a god among mortals.

    1. OmarF*

      SUMIFS and COUNTIFS replace the vast majority of the places I used to use SUMPRODUCT. And they are almost readable by humans!

      1. DATAnerd*

        YES! Arrays used to be the bomb… then they came out with the plural ifs and made it all so much simpler :)

    2. excelnerd*

      Sumproduct is actually even more helpful.. It allows you to do a countif or sumif using row and column information, kinda like index, but with non unique values, and the ability to use ranges. Say you have a table of account balances (accountant here) that has multiples lines with accounts that are designated as wages in another column. So your data has lets just say 14 columns, 1 account column, 1 group column (where wages in designated) and 12 date columns (for 12 rolling months). Sumproduct can be written so that you can add all intances of wages for a varying date range (which could be set in another cell(s)). It might look something like:

      =SUMPRODUCT((C2:N200)*(B2:B200=”wages”)*(C1:N1>date(2013,5,31))
      This will sum the data in C2:N200 that is in a row with “wages” in column B and is for dates that are after 5/31/2013. You can use the formula calculator to see how it is actually doing this.

      Note though that with this formula there can be no blank cells in your data range, they must have a number value (they also can not be text).

      Also, FYI * is AND in excel and + is OR (I haven’t tried using this in other functions yet though)

  63. ChristineSW*

    OMG…I had no idea how much there is to Excel!! O_O I only know the basic stuff, so all of these tricks seem really cool. I love the trick that lets you highlight a large range of cells (ctrl+shift+down or right arrow). Makes me a little sad, though, because I’m not sure I’ll ever need to learn much more than I already know, given the field I’m in.

    And I even applied for a job recently that required Excel knowledge. Silly me!

  64. AnotherAlison*

    Also a big fan of Clear Formats. Just because I hate looking at all those crappy borders and colors in working data. It’s fine for the output-type worksheets, but I like plain old text when I’m working.

  65. AnonHR*

    Look up keyboard shortcuts- I use the F4 key a lot, if you have a formula filled in the cell, and hit F4 it adds the “$” symbols in the name of cell you reference in the formula which freezes that cell within the formula if you drag the formula to copy it. It’s great if you want to use a multiplier that you may want to change later that’s in one cell in the spreadsheet.

    I also use vlookup. I work data which is sorted by name in one spreadsheet, but in one I format completely differently for the management team, it’s sorted by department. To avoid having to manually enter the numbers, or reformatting the spreadsheet every month, I take the pay info by name, and do a vlookup in the department spreadsheet to pull the number based on the employee name it’s next to

    I use concatenate to combine cells when my reporting software doesn’t give me the name or address format I want. Want first and last names in the same cell? Make another column and combine the two with the concatenate formula. The opposite of that is the “text to columns” option under “Data”. It separates text based on spaces, commas, a certain number of digits, etc.

    I use the sumif formula to add up items per person. I can’t get my expense software to get me a decent YTD report about mileage, so I run something that gives me each mileage expense each person has submitted, and then do a sumif using their name. It adds all the numbers next to their names. I’ll copy/paste the column with the formulas, and then choose the option to paste only the numbers (not the formulas) so I can delete the column with all the smaller numbers. I then use the remove duplicates function under data to end up with only one name per person.

    I learned all of these (except the F4 function, a prof showed me that) by Googling “how do I…”. Basically if it seems like there should be an easier way, there probably is. The one exception is a keyboard shortcut for the “copy formatting” button and I can’t find one. If someone knows how to do that I would be eternally grateful.

  66. AnonAdmin*

    OK, basic but timesaving one: at the bottom of a column of info, hit ALT-= and it’ll automatically sum everything above. No need to type =SUM(…

    1. reneeflower*

      I just recently found out about this. It will also automatically format it as a SUBTOTAL function if the cells you’re referencing are filtered.

  67. Meredith*

    I have a question about the VLOOKUP feature that I’ve seen mentioned. I want to know if I can use it for something like this:

    I track enrollments for online courses that my organization offers. The enrollment system doesn’t assign people user names for logins automatically, and (unfortunately) enrollees can’t make up their own user names. I have to create the user names manually. Each person gets one unique user name, and we try very hard not to give them multiple logins if they are taking more than one online class.

    To track the user names I’ve already created, I have a “master” spreadsheet that contains each person’s first name, last name, email address, and the user name I created. When it comes time to enroll a new set of students, I manually compare the enrollees for the new class against the master list, to see if anyone has a user name that already exists. This is tedious, and I can’t help but feel that it could be automated somehow. Will VLOOKUP do this for me? Is there another solution that would be better?

    I use Excel, but not in any complex ways. However, I’m fairly tech savvy in other areas and wouldn’t mind learning a trick or two!

    1. Zahra*

      Are email addresses unique and do you receive them from the enrollment system (or any other unique identifier)? The unique identifier you use as a reference must be in the first column, but you can have duplicate values in the “answer” column.

    2. Khushnood Viccaji*

      @Meredith,
      Let’s say you have FName, LName, Email and UserName in columns A, B, C and D respectively.

      Now insert a helper column to the left of the UserName column. The UserName moves to column E.
      In the (new) column D, put the FirstName, LastName, Email as a concatenated value. E.g. John Smith jsmith@abc.com

      Now you have a range in which unique combinations of the FullName & Email IDs are available, with the UserName as a lookup value next to it– i.e. Range D1:E100

      Next time you have a batch of 10 new enrollees, copy their FName, LName & Email IDs in the range G1:I10

      In cells J1:J10, enter the concatenated values of FName, LName & Email (exactly like you did for A, B & C above)

      Now, in cell K1, enter the formula
      =VLOOKUP(J1,$D$1:$D$100,2,FALSE)
      Copy the formula down upto cell K10.

      If any of the values in J1:J10 are already there in the original range, they will show the UserName from there.
      If not, it will display the #N/A error.

      Try this on a small sample first to see how it works, then apply it to the full dataset.

      1. Khushnood Viccaji*

        Sorry, the formula in K1 should read as:

        =VLOOKUP(J1,$D$1:$E$100,2,FALSE)

        not $D$1:$D$100

        1. Zahra*

          Name that reference range! Then, when your range gets bigger, you just need to name the expanded range the same way, without needing to change your vlookup formula.

    3. anonn*

      Use concatenate on the master spreadsheet in one column. Add in enough of the variables that you can tell John Smith from Oxford apart from John Smith from Cambridge.

      Then use Vlookup on the new registrations. If you put FALSE in any that have N/A# in the column need a login created.

      1. Meredith*

        Thanks, everyone! I ran the idea past my more-techie-than-me boyfriend (who distains Excel in favor of Access), and he is pretty adamant that an Access Database will be a good step toward automation of this process. That’s something I’ve thought of too, but I’d have to dust off my Access skills first! I’ll experiment with your suggestions, as well as with Access, and see if I can’t cut down on the silliness of the manual compare.

        Zahra, Khushnood Viccaji, anonn: Thanks so much for taking the time to reply! :)

        1. Khushnood Viccaji*

          You’re welcome Meredith :)

          If using Access is a viable option, then it changes things because that could mean developing a mini-application, which would take care of ensuring no-duplicate logins (among other things), rather than using formulas and stuff in Excel.

        2. BigLaw Refugee*

          I recently started trying to use Access. Yes, it’s more powerful, but I find it really unintuitive and difficult to use. I think the tips others have given you will allow you to do what you want very easily in Excel – the VLOOKUP feature will be perfect once you concatenate the name columns to make a unique ID for each user. So unless you have a resource who can develop an Access app for you, or you have tens of thousands of users, it may not be worth investing in learning Access.

          One more tip: you could use a formula to generate the userIDs instead of creating them yourself. Say the full last name and first two digits of the first name: “=A2&LEFT(B2,3)” (where A2 is last name and B2 is first name). Next to that you could have a VLOOKUP that to make sure you hadn’t already assigned the username.

  68. Andy*

    Get familiar with incorporating “IF” into your formulas…

    Use =IFERROR to hide the dreaded N/A! or VALUE!
    Use =SUMIF (or SUMIFS!) to add values based on given conditions

    There are many more…

  69. Sarah*

    I’m so thrilled to see so many other vlookup fans! I’ll also stress the importance of the FALSE argument when it comes to exact matches.

    I’ll also give an example of something I did the other day for someone – she had a list in a browser that showed LASTNAME, FIRSTNAME, and address information.

    I selected that, copied it, and pasted it in Excel. It came over with some graphics, so selected it again in Excel, went to a new sheet, and pasted as a value. Presto – here’s just the data.

    Next up, she needed to create a mailing list, and group couples together. The list is a pretty bad list, so it would take a person who knows the people to go through. I inserted a column, and created a formula for the name = proper(A2&” “&B2). That changed SMITH BILL to Bill Smith. I then selected that entire column, and pasted it as a value. Some entries were “Bill And Jane Smith”, so I did a Find and Replace for ” And ” and replaced it with ” and “. (alt E, E brings it up quickly).

    After that, sorted by address, and then added a column at the end to compare addresses, to give her a quick indication of whether or not someone was possibly a couple – =If(D4=D3,”Same address”,””).

    After that, I showed her the magic of Mail Merge.

    I love Excel.

  70. T.*

    Question:

    I’ve tried Googling this but didn’t have success so maybe someone here can help..

    I have to go through a very large spreadsheet row by row and check information against a hard copy. It helps me to have the row I’m looking at selected (highlighted) when I’m looking back and forth between my screen and papers on my desk. So what I’m wondering, is there some way to scroll between rows, but to only have one row selected (highlighted) at a time?

    Eg. I’ve selected Row 100, and when I’m ready to move on to Row 101, I want to be able to use a shortcut to scroll down one row and only have Row 101 selected.

    2. Khushnood Viccaji*

      @T, you will need a small piece of VBA code to do this.
      On the worksheet where you need to have this ‘full-row-selection’, right-click it’s name tab, and click on the ‘View Code’ command.
      This will open the Visual Basic Editor (VBE) window with a blank module for this sheet.

      In the blank area, copy-paste this bit of code.

      Private Sub Worksheet_SelectionChange(ByVal Target As Range)
      With Range(ActiveCell.Address)
      .EntireRow.Select
      .Activate
      End With
      End Sub

      This will now select the entire row of the active cell whenever you move around in this sheet using the direction keys.
      To disable this code, you can simply comment it out by inserting a single quote before each line, so that the text turns green.

      Note: to switch back-n-forth between the Excel spreadsheet window and the VBE window, use Alt+F11.

      Caution:
      Be very careful not to press the DEL key or Ctrl+D or Ctrl+R.
      The entire row will be affected by these commands :)

      1. T.*

        Thank you!!! That does EXACTLY what I’ve been trying to figure out for months! :) Will definitely make work easier on my eyes and I should be able to move through the spreadsheets much quicker!

  71. AMH*

    I second everyone, especially vlookups, offsets, and pivot tables. I also love sparklines which were introduced in Exel 2010. They are similar to mini charts and I use them for performance dashboards.

  72. Mints*

    Something I find really useful is I wrote a macro to change formatting to normal. I do a lot of copy paste text (names from websites) and I wrote a key board shortcut to make the font, size, color etc all normal. Before I was leaving them irregular till I got to the bottom of a page and it drove me nuts

    1. Goofy posture*

      Another option is to paste the text into notepad to strip the formatting, and then recopy the plain text. I do that all the time for web and email stuff, not just excel.

    2. TychaBrahe*

      Alt+E, S, V

      Edit, Paste Special, Values.

      Copies the value of formulas, not the formulas. Also copies the text without the formatting.

  73. Leslie Yep*

    Can we do one of these for personal organization/task management/project management? Or maybe not because reading it would undermine any org/task/project management systems I do have?

      2. Goofy posture*

        Excellent! I have so many projects popping up all the time, it’s so hard to keep on task with everything!

  74. bagworm*

    One thing that I love to supplement Excel is ASAP Utilities. It’s an add-in for Excel that offers a bunch of additional functionality. Everything it does you could do with some VBA but it’s so much quicker and easier to use this package. It’s free for home, student and charitable organizations but it’s also pretty inexpensive and I like it and use it so much I paid for it when I didn’t have to just to support the product.

  75. Nikki B*

    Actually naming data, so that formulas make sense when you go back six months later.

    So instead of a forumla reading =A1/52, it could read =salary/52
    Click on your cell with the data in it. go to the formula tab and select define name. Type in your name and you are done. (no spaces allowed in names though)

    Even cooler in current excel is the ability to auto name, using create from selection.

    So if you put the names you wanted in the columns beside the data, it will then use the column text to name the data cells.

    1. BigLaw Refugee*

      Yes! Folks, google “named ranges.” You can even create a dynamically named range, so that as your data expands, so does the range. Named ranges, in addition to being intuitive, provide an even faster way to select your data for the purpose of filtering, creating charts, etc. There’s a drop-down box in the upper left hand corner where you can select any named range with one click.

      Another useful trick is Custom Views. If you tend to do a lot of hiding and showing of columns, these can do that faster. I think in theory it can also save your filters, although I’m not sure that’s worked for me.

  76. anonn*

    Another: Split Screen

    Nice for when you may have multiple areas of the same sheet to look at at once. For example I can look at the nice box at the top of a report that the boss wants done in Excel AND the data on the same sheet below it.

    Format painter – little paintbrush on the copy/paste section which you select the box that is correctly formatted and then click and drag over the ones to format.

    Decimal points add and remove, also currency – easy way to reformat data for whatever you need.

    Turning Cell calculations on or off – nothing worse than having a big document with many pages that references a data set and waiting for it to recalculate every time you change 1 thing…. Turn it off and then press … (ok, I’ve forgotten – been off sick for three months!) an F key to force calculation once the entry phase is completed.

    Add a note: not sure the latest version has this – can’t see it right now by looking – you can add a note to a cell which appears as a little red triangle in the top right. This can then be a “this has to be calculated by doing XYZ” or a “this entry is special, related to X, any questions ask Y”. But it doesn’t impact the overall look of the data when printed.

    As another note for those who do long spreadsheets which the length of changes – I actually add the formulas at the top. Less time trying not to lose them and you just have to make sure that they cover the last cell of data.

    Mentioned above but worth it again. End then an arrow can be used to navigate to the last filled cell. when you are copying it will drag the formula to the last filled cell.

    1. Anonymous*

      F9 key triggers a manual calculation.

      The little red triangle you are referring to is called a Comment.

  77. Anonymous*

    Using graphics as line markers in charts. It’s so basic, but everyone thinks it is utterly cool in the report.

  78. JF*

    If you are using the autofill (that “draggy click” thing that autopolulates cells based on what you dragged from) tools, and have a formula in them, use $ to prevent autofill from changing a specific value.

    Let’s say you had a formula that was =sum(A1,B2) and you wanted to drag it across multiple columns so that it would always add A1 to B2, C2 or whatever.

    If you use the autofill, and drag to the right, you’ll get =sum(B1, C2).

    If your original formula has a $ in front of the A in A1 it will read =sum($A1,B2). If you autofull drag to the right, youll get =sum($A1, C2); =sum($A1, D2) etc…

    If you are dragging down, you can put the $ in front of the cell row number instead, like A$1

    I hope that made sense. It saved me a lot of time.

  79. alison*

    Omg.. these are my people. Most everything i love it’s already written.

    VBA to make macros (never write your own, start by letting Google results help).

    Ctrl G. Picture that you have done some subtotal-ing and collapsed to show the results. Highlighting and copy actually copies everything. Now highlight and press ctrl G. Choose “visible cells only” then copy paste. Yay. And, yes, i know i could do this with pivot tables, but I’m generally trying to get my data results and format nicely for management and this gives me a better starting point.

    Also as a follow up, I’m so curious what all you people do for a living. It seems like fun. Because I’m nerdy.

  80. Kelly*

    An Excel question has been nagging away at me and I can’t seem to find the answer by Googling…hoping someone here can help!
    An ex-coworker of mine had a shortcut she used to jump to the cell being referenced by a certain formula. For example, if she was working in Sheet 3, which pulled data from both Sheet 1 and Sheet 2, she could go to the cell in question, and hit a key, and Excel would take her to the cell being referenced. I know you can also read the cell being referenced by reading the formula itself, but this was super helpful where there were many sheets it the workbook and reading each formula was arduous. Any thoughts?

    1. Nikki B*

      Try using the trace precedents button in the formula tab.

      The trace dependents will tell you where the selected cell’s data goes.

    2. DATAnerd*

      If the cell is named, then you can hit F5, which will bring up all named references. Select –> OK and it will take you to that cell.

    3. Khushnood Viccaji*

      @Kelly, you can try using Ctrl+[ (that’s the left square bracket) to jump to the precedent cells of a formula.
      And Ctrl+] will jump to the dependent cells of a formula :)

      1. Kelly*

        Thank you guys SO much!! I think my ex-coworker was using Ctrl+[ — I just tried it out and it worked exactly as I remembered! Thanks again!

  81. Anonymous*

    Thanks for this topic. I’d been told my Excel skills were out of date. I’m a VisiCalc -> QuattroPro guru from the old days. But if these are the coolest tricks out there, go me ’cause I can still hang with the best! Really, this topic has made my day!

  82. Andrew Bryk*

    AVERAGIFS and SUMIFS are two functions you want to know. They search a range of numbers and then do the titular operation based on criteria you can specify in matching columns. For instance:
    A B
    Apple 1
    Orange 5
    Apple 2
    =sumifs(b1:b3,a1:a3,”apple”) will return 3. I work in marketing analytics and find this very useful for pulling out certain products, franchises, or categories from a big data dump. If you combine this with a drop-down box then you’ve got a very user-friendly tool for searching data.

  83. Elizabeth West*

    I don’t math. But I’ll look at this when I’m back in the office and see if there is anything that might be useful to the sheet I have to update. It’s all complicated but I didn’t make it, so I mess up sometimes. :(

  84. Paul*

    Delete Duplicates does just what it says.

    Insert Cut Cells is another great time saver for moving data you’ve entered.

  85. alison*

    O. Another one is formatting numbers. I deal with big numbers, but i don’t really like to divide by 1,000,000 all the time because when doing calculations, i don’t want to have to think about units.
    Go to the format numbers and choose a format where your number has a comma. Then add 2 commas to the end. 5,089,, “M” displays your number correctly but keeps the actual data the same. Also 5,089,127, “K” for thousands.

    Bonus points, the number format also displays on the status bar for sums and averages. Cool.

  86. Teacher Recruiter*

    For anyone who creates worksheets with crazy calculations and you’re trying to speed it up, I’ve found the FullCalcTimer Macro to be incredible.

    Here’s a link to the code you can copy, but if you run it, Excel will tell you how long it’s taking to calculate an entire workbook, individual sheet, or even just a range of cells. Very meta! Plus, it’s practical and can help you figure out where you might have a bottleneck causing problems with your calculation speeds.

    http://msdn.microsoft.com/en-us/library/ff700515(v=office.14).aspx

    Two years ago I knew nothing but the SUM function. Several others have said it, but Google is the best way to learn.

  87. Chinook*

    I have created a spreadsheet to track invoices that come to us and before they are entered by A/P for 3 separate departments that I reconcile monthly. I have it set up with 3 tabs of pivot tables, labeled for different users, and have figured out how to have dependent drop down menus so that only certain account codes are usable for certain depts and I can only use a certain list of vendors so that it is easily filterable and pivot table friendly. I can’t wait for 2014 to start so I can use it instead of 4 separate workbooks with different layouts and purposes.

    Now I have to spend an hour or two reading what everyone else said.

  88. Twyla*

    Most of my favorites have been mentioned already (vlookup, concatenation, countif, sumif, conditional formatting, use of F4 (to get the $ to lock rows, columns or both in formulas), pivot tables and filtering options but I have two I don’t think I saw on my quick skim of comments:

    Changing text to numbers: highlight column, do “text to columns” to convert the column to number values.

    Remove duplicates: I often have a report with multiple lines per employee (work in hr). I really don’t need the data that detailed, but I need to know that record needs reviewed. Works wonders.

    <3Ecxel
    .

    1. OmarF*

      I connect to our business system data all the time doing that. Access tables work great as well. And, I’ve figured out how to connect to other spreadsheets. We have reports we get from a supplier that I drop into master tables and maybe do some initial work with. Then my report spreadsheets connect to the master and add that information as needed.

  90. Nice Pants McGee*

    Hi! I would strongly suggest making friends at your local community college. Not only do they have a course or two you can take, if you wish, but they also buy back books every year. Get your hands on a used Excel Lesson book (or related therein) and you’ve got gold! They take the reader through graduated lessons and have an index that aids questions later on. I wish I’d kept mine :/ Google is great, but something about a book, pencils and highlighters can’t be beat. Each new chapter will spur ideas you can jot down in the margins too. If you get a handle on Excel, you’ll be ready to enter Access territory and that’s a super-power. Best of luck!

  91. Helen S.*

    One thing I don’t see mentioned is the EXACT function.

    Sometimes I need to confirm that strings of text from 2 sources match, so I paste them into excel and compare them using EXACT. If they are identical, it will return TRUE, otherwise it will return FALSE. This lets me catch if there are minor discrepancies in the text I’m working with without trying to sit and inspect it word by word, which makes my brain tired.

  93. Anonymous*

    Combining VLOOKUP and HLOOKUP in one formula to pull in a specific value on a table by matching first on a row value and then on a column value.

  94. Noah*

    I learned so much from my freshman year MIS class at college about Excel. That is the only professor I’m still in contact with years later, and it is one of the few classes that I’ve directly applied to the workplace.

    I love these threads, I have a few spreadsheets that use SUMPRODUCT arrays, I’m going to try the SUMIFS and COUNTIFS functions to see if I can replace them. These spreadsheets have been around for years, and I always used SUMPRODUCT to do this task, didn’t know MS finally made another option.

    I agree with VLOOKUPS and pivot tables, use them all the time. One thing I didn’t see mentioned was something I just figured out. We needed a report to look at demand by day for staffing purposes. The workbook I was given had the entire last year with weeks 1-52 each having their own worksheet. It was easy to do =AVERAGE(‘Week1:Week52’!B5) and average up cell B5 among all the worksheets. You can also use the SUM function the same way. I ended up creating a much more complex report in Crystal that pulls the data straight out of SQL instead, but it was a good proof of concept.

    Also, another feature of Excel I use all the time is Goal Seek. It took me awhile to get the hang of things, but its a great way to figure out if you can meet whatever goal in a certain amount of time. I use it for safety goals a lot, but I can see it being useful to sales teams as well.

    I guess I should also mention that I’ve learned to never, ever store data in Excel. It’s great for crunching numbers, but it is really easy to screw everything up if it is the only place data is stored. One messed up sort and then save and you’re screwed and running to IT to get a backup copy of the file. Thankfully, almost everything where I work now is actually stored in a SQL database and you can use either Excel or Crystal Reports to get it out into a useable format. I used to always dump everything into Excel and then sort and report. However, I’ve grown to love Crystal now, it’s a great way to build reports when you are reporting on the same information week after week.

    1. Wakeen's Teapots Ltd.*

      I’m fortunate enough to have someone on staff who has set up amazing Crystal Reports system. I specify the reports I need and she builds them.

      If I need to analyze the data further, I export the raw data into Excel and drive for the needs of the moment there (mostly using Pivot tables).

      It’s the best of both worlds. Her reporting system allows me to get commonly used reports (like a daily/weekly/monthly sales report) with a couple of button pushes or data dump into Excel and drive however I like on my own.

  95. Laurie*

    The coolest tricks I know (and learned very recently too) are:

    1) Ctrl+Spacebar selects the entire COLUMN of the cell you are currently in

    2) Shift+Spacebar selects the entire ROW of the cell you are currently in

    3) And, the best one:

    For those of you who know and use CONCATENATE, you know that you have to click into each cell you want concatenated (same with just using the A5&B5 trick), and it’s even more of a pain when you have to put in a separator of some kind. Well. Let me change your life here.

    I got tired of doing this one day, and went looking for a trick that would let me just select an entire range, then specify the separator I want, and when I hit enter, it should concatenate everything.

    Here’s the link, and it includes a downloadable Excel file too – http://excelexperts.com/VBA-Tips-Concatenate-Range.

    The gist of it is, you’re creating a custom function using Excel VBA, then saving it to your Personal.xlsb file so that it opens up every time you start Excel. Then you can use this custom function (and you can call it whatever you want, so bonus points for calling it “=ConcatenateThatThang(A2:A20,” – “)”) in any workbook.

  97. FD*

    COUNTIF, SUMIF, and AVERAGEIF are all extremely helpful. They allow you to count, add up, and average (respectively) cells that meet certain criteria.

    For example, let’s say that I entered in a bunch of items from an invoice. I want to average only those items that I labeled “office supplies”. Let’s say I put my labels in Column A and my values in Column B. I can enter this function:

    =AVERAGEIF(A:A,”Office Supplies”,B:B)

    And my work is done. Remember that the criteria always need to be in quotes–it’s fairly intuitive when you’re entering text criteria, but I tend to forget when I’m entering a mathematical function (such as “>0″).

    The IF and AND functions can be useful too–though the syntax is kind of a pain because you need to close your parentheses.

    I often use IF because I hate having #DIV/0! in cells. Let’s say that I’m working on a spreadsheet where I enter data every month and track the percentage increase or decrease. I want the percentages to be blank if I haven’t entered any data for that month, instead of showing #DIV/0. So, I’ll use something like this:

    =IF(B4=0,””,B6/B4)

    That way if B4 is blank or has a value of zero, the result cell will be left blank.

    You can pair that with AND too. For example:

    =IF(AND(A1=0,A2=0),””,A4/SUM(A1:A2))

    Will leave the cell blank only if A1 and A2 are blank (or have a value of zero).

    Oh, last trick. So, if you know Excel moderately well, you know you can ‘drag’ formulas. For example, if you fill in the formula for the first cell, you can click on the lower right hand corner and drag it down to fill in the rest of them. (I know there’s another way, I just don’t remember it.) Excel automatically changes the cell references for you. For example, let’s say your starting formula is:

    =A1+B1

    If you drag that down to the row below it, Excel will change the formula in that cell to:

    =A2+B2

    But, what if you don’t want that? What if you always want the value of your formula to be A1+B1, no matter where you move the formula to?

    You can easily change it by typing:

    =$A$1+$B$1

    Whatever you put the dollar signs by will keep the cell references from changing. For example, you can do any of these:

    =$A1+$B1 (cell references will change rows when you drag them down, but won’t change columns when dragged across)
    =$A$1+B1 (A1 will always be the first part of the sum, but the second half will change columns or rows when dragged)

    Etc. I’m not sure that made any sense, but play around with it! Excel is a great tool.

  98. snuck*

    Mkay, not going to read all 391 comments :P

    My key ones are already here – vlookup, cocanonate and test to data.

    Another is to copy, paste values only – so you don’t copy formula across.

    Another is simply to highlight a column of numbers – it auto sums / totals them in the bottom footer of your screen so you don’t need to do a total/sum for a quick add up.

  99. Tony*

    Some useful keys to cut down on mouse clicks:

    ctrl+d = copy cell above down to current cell
    ctrl+r = copy cell from left to current cell
    (alternatively both of these fill a range from the first cell)

    shift+space, ctrl+space = select current row, colum

    ctrl+-, ctrl++ = insert/delete cells or cut/paste current selection

    ctrl+; = insert today’s date (as a value, not function)
    ctrl+: = insert today’s time (as a value, not function)

    ctrl+pageup, ctrl+pagedown = move between sheets

    Definitely best to pick a project and google until it’s done. Budget’s a good one but relatively simple. One of my more complex non-macro based sheets is my tax calculation.

    Best of luck,
    Tony

  100. Trillian*

    Another request: could we please do a Word thread at some point, for the writers and editors among us?

  101. anonengineer*

    I only ogt about halfway through this thread, and I’ll have to second all of the data functions —- index, match, vlookup, and pivot tables (oh and filter, esp filter by color + conditional formatting = awesome); and the text manipulation functions that makes working with them possible (clean, mid, left, etc.) along with the click the dark box on the lower right of the selected cell to fill and paste-special (anything)

    but seriously – CUSTOMIZE YOUR RIBBON – in excel 2010+ you can make a custom ribbon pulling in whole modules (like for conditional formatting) or making your own (I have one with short-cut buttons for paste formats, paste values, and paste formulas) —- I couldn’t live without my “Shortcut” ribbon.

  102. Matt*

    In Excel 95 or 97 or so, there used to be an easter egg “flight simulator” that could be triggered by entering some secret formula or whatever … does anyone know if this is still there in more recent versions?

  103. the gold digger*

    I have to prepare powerpoint presentations for my boss with the budget, which is a moving target. Rather than update the powerpoint every time, I keep a master spreadsheet with all the financial information. I make all my charts in excel and then copy them to powerpoint with the format (can’t remember – it’s one of the paste options) that links PP to excel. If I update any numbers in the master sheet, the excel charts automatically update and so does the powerpoint. (I think I might actually have to open each PP sheet.)

    Of course, this only works well if your boss doesn’t save everything from the network drive to his hard drive, where he renames the document from “2014 Brazil Budget.pptx” to “2013-12-4BrazBud.pptx.”

  104. Lissajous*

    Aha! Excel! I think this is where all the engineers emerge =)

    IF can be incredibly powerful, especially when you start nesting your IF statements. (SUMIF and COUNTIF are very specific types of the more general IF).

    Basic version:
    IF(logic_test, value_true, value_false)
    For test results:
    IF(A11>=50,”Pass”,”Fail”)
    If you don’t put in specific values to return if your test does or doesn’t pass, it just returns TRUE or FALSE respectively. When you want it to return text, don’t forget to put quotation marks around the text so it recognises it as string.
    Where you can start getting fancy is that you don’t just have to put text or a number in the value_true or value_false spots. You can put in another function.
    Suppose we want grades, not just pass/fail.
    IF(A11>80, “A”, IF(A11>70, “B”, IF(A11>60, “C”, IF(A11>=50, “D”, “F”))))
    Above 80 gets an A, 70+ to 80 a B, etc
    Nested IF statements can be very powerful, just remember to close all the brackets. And you can put almost any function in there.

    Now, on those logical tests… Say you’ve got a spreadsheet of transactions in and out of your account, and you want to find all the times Jane sent you money. The text the bank puts against the transactions always has the same account name against it, but the receipt no. changes each time, so you can’t just look up the whole text of any one time Jane sent you money. You also can’t just do a nice, simple sort on the top row.

    IF(COUNTIF(B25,”*JANE*”)”, “Jane”, “Not interested”)
    Translation: If cell B25 has the string of letters JANE in it anywhere, then tell me “Jane”. Otherwise I’m not interested.

    The asterisks tell the formula to check if B25 has the string “JANE” in it anywhere. Without the asterisks, the formula would look for the exact and only the string JANE, and JANE EUTERPE would return FALSE.
    (Excel doesn’t seem to treat this as a boolean test (one that returns either true or false, nothing else), which is why there’s the COUNTIF in that example above.)

    And a note on layout (from someone who does engineering calcs and capexes, rather than data manipulation):

    Label your cells. Go to the little white rectangle to the left of the formula bar that shows the cell name, click on it, and type in something that makes sense. It’s much easier to track formulae when C31 which has the gang labour rate has been renamed lab1.

    For design calcs, adding a column (or row, whichever way you’re working) that shows the equation you’re using in as close-to-traditional-maths-and-physics-as-possible notation makes life a lot easier for the people trying to understand what you’ve done. I usually call it Symbol and grey the text so it’s easy to ignore, but it is really useful.
    My typical setup is:

    Description Unit Symbol
    Flowrate l/s Q=v A
    Power kW P=Q H p/(eff x 1.02)

    etc

    Other than that, anything that means you don’t need to grab the mouse as often is wonderful, and Google is your friend. Chances are someone’s asked the same question in a forum somewhere, and even better, someone’s probably answered it.

  105. Frieda*

    This thread made my day. I also remembered my favorite Excel shortcuts (2007 on a PC; not sure what it would be on a Mac):

    Select a row (or many rows). Hold down ALT then (sequentially, not at the same time) hit O, R, A. It will automatically adjust the row height to fit the contents of each row.

    ALT + O, C, A does the same for columns.

  106. Nicole*

    I learned how to consolidate a list by capturing every other line (or every third, forth, etc.).
    =MOD(ROW(A2),3) the ‘3’ can be any number of rows you want to skip. Drag the formula down the column- paste as value- sort descending. The data next to the ‘3’ is from every 3rd row.

  107. excel monkey*

    Vlookup, Hlookup, pivot tables, conditional formatting are all wonderful and will save you lots of time, but the single BEST thing you can do to speed up is learn how to use excel so that you never reach for the mouse.

    Here are some very basics:
    ctrl+[arrow]: to move across all adjacent cells in a direction
    ctrl+shift+[arrow]: to highlight all adjacent cells in a direction
    ctrl+r: to copy a formula to the right (you have to highlight the cells first)
    ctrl+d: to copy a formula down (again, highlight cells first)
    alt+i+r: add a row above selected cell
    alt+i+c: add a column to the left of selected cell
    F4: when you are writing a formula to freeze the reference cell (adds the $ symbol, keep pressing F4 to move through both row &column frozen, just row frozen, just column frozen)
    alt+e+s: paste special (ie to paste just formatting or just values or just formulas or some combination)
    alt+e+i+s+tab+tab+f : to do autofill without dragging

    Learn how to use the alt button to navigate every single menu option in excel. Google how to use excel without a mouse. It is INSANE how much time this will save you.

  108. Greg*

    Really minor, but incredibly useful: Let’s say you want to have a number in a cell appear as-is. For example, you have a zip code of 01234, and you don’t want Excel to automatically shorten it to 1234. You could change the number formatting to “Text”, but if it’s just a for a limited number of cells, you can save yourself time by entering a single apostrophe at the beginning of the number (eg, ‘01234). That tells Excel to leave it alone.

        1. Anonymous*

          But underneath, Excel still reads that as a number. So if you save as a CSV file or try to import to Access and you need that leading zero to be there (say you have a product ID code that starts with zero or a zip code or something). Formatting the field as text or starting with an apostrophe will retain the leading zero.

          1. Khushnood Viccaji*

            Very good point Anonymous !
            To check how Excel treats a cell entry, look in the formula bar — if the number is displayed on-screen with the leading zero, but doesn’t show the leading zero in the formula bar, then it will be treated as a number (and not text).

            A rule of thumb that I follow, is to check whether I will need to perform math operations on a particular field / column.
            If not, then it is safe to format it as text or enter it with a leading apostrophe.

  109. Gary*

    CTRL ‘ copies what’s in the cell above to the selected cell
    CTRL ; inserts the current date into the selected cell
    CTRL : inserts the current time into the selected cell

  110. reneeflower*

    If you’re working with ranges that are filtered or have hidden lines, highlighting the range and holding down ALT and ; at the same time selects only the visible cells. This is useful when you have filtered information and only want to format the visible cells with a certain color or if you want to paste a formula or text to only the visible cells in a column.

  111. EA*

    Most of the excel work that I do is macro-based. I am pretty sure I’ve never needed to create a pivot table, and although I have used vlookup, it’s not often. So my advice will focus more on the VBA side of things.

    -When you’re writing VBA code, always comment your code (this actually applies to any code, not just VBA). Even if you think you’ll be the only person to ever edit it, the comments will help when you go back 2 years later and wonder what you were trying to do when you first wrote it.

    – Always backup your working files. If I have something that I need to make changes to, first I copy/rename the file, then I work from that version, rather than my original version. That way, if I break something, I can always go back.

    – Don’t reinvent the wheel. Keep all your old macros/sheets because you never know when you’ll need to use a function that you wrote for one project in another. I wrote something for one project (create a single HTML email from data stored on multiple pages). 4 years later, I had changed roles, but I needed to do something similar. Looking back over my old (nicely commented) code saved me a lot of time, compared with having to re-write it from scratch.

    – If you use macros you find online (message boards, etc), take a look through it, and try to understand what it’s actually doing. That way, when you need to modify it, you’ll have the knowledge to do it yourself, rather than needing to go back to the message board.

    – If you write a function that will clear data, always put an “are you sure?” check in there. Otherwise, one accidental mouse click could be disastrous.

  112. Checking in*

    My two favorites are:

    – Named Ranges = in the top left corner of excel you will see the “name” of the cell you have highlighted. For example, “A1”. You can click inside that box to change the name of that cell. Later when you write a formula, instead of =A1 + C534 you can put = Price + Margin. This is INFINITELY helpful if you have workbook with tons of formulas, OR if you have a workbook that will be maintained by people other than just you. It save the time of dancing around the spreadsheet trying to figure out where numbers are coming from. You can also name ranges of cells. You can use the “names” in macros too, which is very helpful if you add/remove columns and rows frequently. There is also a toolbar for this under Formulas –> Name Manager, in case you spell something wrong or want to see all your named cells in one place.

    – Most people know the dragging tip (to copy the above cell) but a twist on that is that right-clicking and dragging allows you some additional options – like just copying the formula and not the format, or just the format and not the formula. I love the second option because a lot of time I have unique data (example: text data of a person’s name) and I just want to copy the format. Sure you can do the paintbrush, but this way can be quicker at times.

  113. RF*

    My favorite excel trick is using named ranges and offset to make a graph automatically update when I add new data to the end of a column – saves lots of time when you have a lot of graphs you just need to add 1 more data point to.
    It seemed complicated at first, but once you get the names and formula down it is a HUGE timesaver.
    Some info here:
    http://office.microsoft.com/en-us/excel-help/using-named-ranges-to-create-dynamic-charts-in-excel-HA001109801.aspx

    you can also find the steps if you google something like ‘make and excel chart update automatically’

  115. Alan Miller*

    This is more of a design tip and isn’t as relevant now as it was some years back, but if you’re still using paper forms you can use Excel as a reasonable tool for building them. I’ll include how you can make them usable in Excel as well, or you can use them to generate a PDF and use Adobe to add text areas so people can fill them out.

    Simply select the entire sheet (Ctrl-A), then right-click on a column header, select Column Width, and set the width to 1. Set your page margins (generally on the Page Layout tab in Excel 2007 and newer), then do a print preview or look at the page layout view (View tab) to show the page break lines. By the time you’re done with this, you have what’s basically a piece of graph paper for laying out your form. Fill cells as appropriate with labels, apply right-justification as appropriate (e.g. for a label just left of a fillable area), turn on bottom borders for underlines. You can also select the first column outside the first page printable area (click column header), then Ctrl-Shift-Right to select all remaining columns, then right-click the header and Hide. Repeat for rows below the page(s) you’ll be using.

    From this point you can do your design and generate a PDF, then use Adobe or another tool to make relevant parts of that PDF fillable, or you can continue on to make an Excel-only fillable form template.

    To prevent changes to the form, select each fillable area and use the Merge Cells feature (Home tab, Merge and Center, then change justification back to Left in that same area of the toolbar). Next, right-click the first fillable area, select Format Cells, pick the Protection tab in the dialog, and uncheck Locked. You should be able to repeat this on other fillable areas by immediately selecting them and doing Ctrl-Y (redo). Finally, go to the Review tab and click Protect Sheet (password is optional). At this point, Tab will cycle you through only the unlocked areas. Select the first unlocked area (or where you want people to start), then do Save As and change the file type to one of the Template options. When people double-click on the template file to open it, it’ll create a new blank fillable form in Excel that they’ll be prompted to save with a new name.

    You can go further from here and do actual development with macros, but if you have someone capable of doing that then Excel may not be your best choice of development tools.

  116. swtrader*

    I haven’t read ALL the tips but there are plenty of good ones I have read. VLookup is a powerful tool (used properly).
    For those who have large data sets that need to be organized, Excel is often the easiest way to do that. However, Access is masterful at data connection (invoices to customers, for example) without having to enter duplicate data like the customer name each time. I’ve learned over the years that (rough numbers here) 40% of projects should clearly use Excel; 40% should use Access and 20% are in the grey area where either application would work — depending on your tastes and experiences. Since I am a self-taught 60+ year old, I have been using Excel for a long time — and taught myself Access about 10 years ago. For me, Access (and relational databases in general) are absolutely PURE magic. I am still amazed at what can be done. (Will add that Access is deceptively simple. It’s easy to build an application; it takes a lot of thinking to get relationships and the overall structure correct.) My advice to anyone who has to use and manipulate data — and who will be in the workforce for more than, say, 5 years — is to learn Access and get a firm understanding of how database structures are changing. For me, even though I am a mid-level manager, having above average Excel and Access skills has been a feather in my cap at review time every year. Explore, learn, enjoy!!!

    1. Eric*

      Access is a good place to start. It’s where I started because it is easy to learn and highly accessible. If you really want to be excellent with databases you need a better solution though like MySQL, Sql Server etc…They handle way more data than access ever will. Plus they connect better with web content, which is where business and technology is headed.

  117. KG*

    Surprised I haven’t seen this in the comments – everyone should play around with the Solver plug-in! You tell it what you are trying to optimize, what variables can change, and any constraints and it solves the problem for you!

  118. Jessica*

    Macros — I once created a Sudoku solver in Excel using macros. A bit extreme…. yes.

    Keyboard shortcuts – If you work in Excel often, learn the keyboard shortcuts. I almost never touch the mouse. It will make you much faster in Excel and (bonus) it’s impressive to watch. People who look over my shoulder often comment on my Excel skills because of this.

  119. JC*

    Thanks for the great tips! All of these ideas inspired me to create a spreadsheet to assign accommodations for one of our events (about 300 people). Last year, I kept on losing people when I switched people around, and it was getting really time consuming. Now I’ll be able to keep track of everyone and be much quicker!

  120. Eric*

    Top 10 features in order of usefulness:
    Sumproduct
    Index/Match
    Pivot Tables / Power Pivot (Excel 2013)
    Data Tables/What if analysis
    Conditional Formatting
    Wildcards
    Vlookups
    Sumifs/Countifs
    VBA
    Trim

    The most useful Excel website on the interwebs is Chandoo.org. It’s simply amazing…

  122. Eric*

    A VERY useful thing to know if you’re using multiple monitors:
    File->Options->Advanced

    Towards the bottom under the General section, tick the ‘Ignore other applications that use Dynamic Data Exchange (DDE)’ checkbox.

    With this checkbox ticked, you can open 1 Excel file on 1 monitor, and another Excel file on your 2nd monitor. However, you’ll want to untick it before you exit Excel or next time you open a file you’ll get hit with a “There was a problem sending the command to the application” message and won’t be able to open Excel files until you untick this option.

    More advice…learn VBA. Without VBA, you’ll always be an Excel rookie :)

  123. Lis*

    Okay, for my first job out of college, I worked Lotus technical support, so I use spreadsheets for EVERYTHING.

    String concatenation and parsing (LEFT/MID/RIGHT, combined with FIND and SUBSTITUTE) are amazing tools. I have built entire websites in this manner from tables of data.

  124. Michael*

    My favorite is to highlight a table, and hit control + shift + F3 to create named ranges. Next enter two named ranges in a formula with a space between them, and you get the intersection. It’s the nerdiest, most awesome thing I know in Excel.

  125. CharlieG*

    My left Ctrl key is completly “worn out”…
    If you want to impress, start learning key shortcuts ! The simpliest things will look just amazing if you master them.

    Tips for navigation within Excel:
    Ctrl +Arrow left, right, up, down
    Ctrl +PageUp, PageDown
    Combine the 2 above with Shift (Ctrl+Shift+ …)
    Think of the possibilities with those combinations and Ctrl+c, x, v!!!
    Ctrl +F4, S, O

    Use F4 to insert “$” in your formulas.

    Merge 2 tables with VLOOKUP using relative references and the shortcut keys above!

  127. Glen Palmer*

    when I auto fill in excel 2010 the formula is correct in each cell but the value that shows up in the auto fill cells is the same value from the original cell used to auto fill

  128. Dan*

    Data validation for drop down lists.

    Naming secondary lists the same as items in the primary list and using the INDIRECT function works really well giving you a secondary list dependant on the selection in the first.

    Combine that with named ranges and you can make some very intelligent spreadsheets.

    Also, try to make them look good. I like to conditionally format input cells so that they are bright yellow only when left blank. That way everyone knows where they need to input data. Another appearance trick is to hide gridlines. Sheets can look so smart with just a small amount of time.

  129. Dee*

    I have a question about using Excel Spreadsheet but so that you can understand i will give a scenario of what we do.
    Everyday at work when we complete something we go into a logbook which has been created from excel and we click on the things that we have done for the day, week, biweekly,tri-weekly, monthly ect. Well to make a long story short my boss is now asking me if I know a better way that at the end of the month if everything in those excel sheets can be added in a report because right now what he has to do is open our month file and go to each individual day and see if we completed everything for the day,week,month ect. Not sure if anyone understands this but any help will not hurt.

  131. LDB*

    I would like to see if anyone could give me an idea on how to:
    Create a Excel spreadsheet that will help me with reminders that will change colors when a date is due or blink? I monitor a lot on one spreadsheet on a daily task.
    * Physical & due dates & CDL Drivers Due Dates
    With all the CDL changes now I am having to send out reminders to the all the drivers at least 2 weeks in advance.
    I have a spreadsheet that is made that has everything on it just want to add extra help; I also monitor the refused that are given to the guys for a reimburse for the physical however we only do those every 2 years. I have had some that have only been given 3 month Physical these a note so they don’t try to send in the forms again. I also track in one leaves the company on when the last Physical and if it was within the 12 month they are required to pay the Physical back.
    * Are you able to have an email from reminders sent
    I have tried to find programs that would help with all this but there is nothing that I can get to do everything. I always seem to return to Excel programming.

    1. ETL*

      Hi LDB,
      I was just surfing for new tricks that I didn’t know for Excel when I cam across this site. Naturally I cam to your post first because it is the last post. I can help you with all your questions if nobody else has ever responded. It is possible to do the things you want to do with such things a formulas and conditional formatting. You can even take things one step further and use VBA to do the same things and save computation power by reducing the number of formulas that are in your workbook. If you are still looking for answers to your questions or maybe you’ve figured it out already and are looking for different possibly more efficient ways to accomplish your tasks feel free to e-mail me!

  132. jcsgo*

    Oh… I haven’t even begun to read this post but I am so psyched. I just started a job where Excel skills will so pay off. AAM FTW +3048. And I thought I had already gained so much from this site…!

  133. Ken*

    How to get my team(s) to THINK … I used Excel to do that … very simple … one of those “why didn’t I “think” of that” things….

Comments are closed.