what are your best Excel and Word tricks?

by Alison Green on November 9, 2017

A few years back, I asked people to share the coolest Excel tricks they knew. By reader request, we’re doing it again — and this time we’re including Word too.

So: in the comments, tell us your favorite Word and Excel tricks. And if you have a question about how to do something in either, feel free to post that too.

You may also like:
{ 771 comments }

{ 771 comments… read them below }

  1. Future Homesteader*

    This is something I knew for years must exist, but didn’t have the name for it and therefore couldn’t figure it out. Most of you probably knew about this, but it’s such a time-saver that I feel like it’s worth mentioning in case anyone else has been missing it: Text-to-Columns. Someone hand you a spreadsheet with first and last names together? Simply highlight the column, go to data–>text-to-columns–>and tell it to split them using a space, comma, or any other character that will give you the separate columns you desire.

    1. JB*

      Using fixed width for this is also helpful, if there are more than one space/comma/etc and you only want it split once.

      1. Mabel*

        Actually, you can indicate the you want consecutive delimiters to be treated as one. I JUST used text-to-columns about 5 minutes ago for a long string of email addresses separated by a semi-colon and a space. When I forget about the space, and it shows up at the beginning of every cell, I use the TRIM function to lop off the leading space. Click in a blank cell and type =trim(cell_reference_of_cell_with_leading_space) Then copy the new data and paste it over the data with the leading space, and BE SURE to use Paste Special, Paste Values.

        1. Margaret*

          When there is a space and comma that I want to be treated as the delimiter, I do find and replace and replace for those two characters with a vertical bar |, which is unique enough that it won’t get confused with actual punctuation (like a comma).

          1. Mimmy*

            Ah, so there IS a use for the vertical bar key!! I’m a keyboarding instructor and can never figure out how to explain where one might encounter that character. I’m always like “umm…you may not encounter this much, but it is part of this lesson with this other key that you may occasionally come across…”

            1. J3*

              It’s also used heavily by linux and unix users working in the command line. It does serve a purpose, but it’s a fairly niche one.

            5. Windchime*

              Another use for the pipe character is to delimit files. It’s very common; just a couple of months ago, I created a process that will produce pipe-delimited text files weekly.

            6. Magenta Sky*

              When I do exports out of the big inventory database, I always use that character to delimit columns, because it’s the one character I *know* isn’t used anywhere in the data. Excel has some pretty sophisticated tools available on importing from text files.

            7. NotAnotherManager!*

              It’s a very common text delimiter in a lot of contexts. Delimiters are ideally characters that don’t occur naturally in text so that field separation/parsing errors are less likely to occur. My data team uses pipes and carets when they have to use keyboard characters to delimit.

    3. Finman*

      You can also tell Excel to ignore an entire column to “delete” a section of what you’re breaking up

    5. Ama*

      Thanks! I know how to use this feature in Word, but didn’t realize they had it in Excel too. I do a major annual project that involves pulling a list of abstracts off a conference website and creating a sortable spreadsheet and this means I can now cut one step out (i.e. paste straight into Excel and convert to columns, as opposed to paste into Word, convert to table and then paste into Excel).

    6. Elsajeni*

      Yes! I use this all the time, for stuff like course listings (splitting ENGL 101 into a “department” column and a “course number” column). You do have to have enough blank space available to the right to put the data in, so make sure you insert columns first if the one you want to split isn’t already the rightmost column.

    7. Kira*

      +1
      Two weeks ago I finally figured out I could use text-to-columns to take a list of values “A, B, C, D…” and separate them each into their own cells.

      So nice.

    9. Mimmy*

      I just tested it and I don’t think I did it right – it said that my data appears to be “delimited”; what does that mean?

      What I did was to make a simple list with names:
      Penny Baker
      Sheldon Cooper
      Leonard Hofstetter
      Amy Fowler
      Howard Wollowitz

      I accepted all the defaults and…nothing happened.

      1. Not a Real Giraffe*

        On the second step, you should deselect “tab” as the delimiter and select “space” as the delimiter instead.

        1. Mimmy*

          That worked, thank you!! I regularly do projects for my mom which always involves lists, and I think one project was a long class reunion list with the first and last names together. Now I know how to separate them if it happens again!

          1. Not a Real Giraffe*

            You’ll have to adjust whatever the delimiter is based on your existing list. Sometimes it’s a comma, sometimes it’s a comma and a space together. Whatever separates the two pieces of info that you want to be in separate commas becomes your delimiter:

            Cooper,Sheldon (the delimiter is the comma)
            Sheldon Cooper (the delimiter is the space)
            Cooper, Sheldon (the delimiter is the comma AND space)

        1. SixImpossibleThings*

          If you’re opening a file with suffix “.csv”, then Excel will ALWAYS delimit on the comma when you open the file. To get around this, you can rename your file from “file.csv” to “file.txt”, go into Excel, and select “File –> open” and pick your file.
          This will start the “Is your file delimited or fixed-width” and so on import wizard.

          Incidentally, when importing with the wizard, one of my favorite new tricks is to tell it to start the import at a row number >1.
          This has saved me a TON of “delete top 3 rows of imported file…” work.

    10. Tableau Wizard*

      Similar to this, if you have to repeat the text splitting, making use of these formulas can be helpful:

      Left(), Right () = takes the left most or right most characters in a string
      Len() = length of a string
      Find( abcdef, b ) = finds the place in abcdef where b is = 2

      When used together you can do something like this:

      = left ( A2, find(A2, “,”)-1)
      and
      = right (a2, len (a2)- find(a2, “,”)-2)

      the -1 gets rid of the actual comma
      and the -2 gets rid of the comma and the space (i assume you have a space)

      you can repeat this sequence, but after two or three, it gets super tedious! It can be really helpful for reformatting names though.
      hope this helps!

    12. Kate*

      Yes! I get a lot of weirdly formatted data that I have to make pretty in Excel, and this is super useful. You can actually select “Treat consecutive delimiters as one” to get around doing the double split that JB mentioned. Another thing to look out for is the “Text qualifier” drop down menu. I was going nuts trying to get a spreadsheet I was importing to stop deleting my quotation marks at the beginning of one field. I switched the drop-down for “Text qualifier” to “{none}”, and it leaves all the column as is.

      Also, my other favorite Excel trick is deleting blanks.
      Select the region you want to delete your blanks from (or ctrl-A), then from your home menu, select “Find&Select” > “Go to Special”, which opens up a pop up box. If you select the radio button for “Blanks”, it will select all the blanks in your selected region. Right-click, then hit “Delete”. It will ask you how to shift your cells, so I’ve usually had to do rows or columns separately, but this still saved me so much time.

    13. LBK*

      One neat trick with this feature is that you can use it for mass-converting numbers to text or vice versa. Just choose “delimited,” then don’t select any delimiters, and on the third screen select “General” (if you want to convert text to numbers) or “Text” (if you want to go the other way). There is the little “!” thing that will pop up when you have numbers saved as text that you can use to fix that, but I find that takes waaaaay longer for Excel to run that operation than the text-to-columns method when you’re using it on a huge amount of data.

      I find this most useful when I’m having VLOOKUP issues since they don’t work if the columns you’re cross referencing are in different formats.

      1. Changed*

        This also works for turning text into the date format of your choice – if you have text in that horrible MM/DD/YYYY format and the day isn’t over 12, Excel will mix it up when you convert the cells to Date formatting.

        Instead, go through text to columns with no delimiters selected and in the Column data format box choose Date, then MDY from the dropdown, and it’ll magically be converted into the correct date serial number which you can format as you wish.

        1. LBK*

          Oo, I didn’t even know that! I’ve spent so much time wrestling with the date() and datevalue() and text() formulas trying to get dates into the right format.

        3. Zahra*

          Oh my god, you saved me so much time! I didn’t realize there were so many ways to use “Convert text to table”.

      2. J.*

        Whenever I’m having trouble converting text to numbers (sometimes it’s fussy for no good reason and this strategy isn’t working), I’ll put the number 1 in a separate field, copy it, highlight the columns/rows I want to convert, then paste special > multiply.

    14. TheReluctantOtter*

      And you can use “CONCATENATE” to do the reverse.

      If A2 = Sansa
      B2 = Stark
      C2=concatenate(A2,” “,B2)
      C2 returns ” Sansa Stark”

      Can join the contents of multiple cells into one.
      You can combine this with transpose and delimiters like commas.

      There are several ways to apply it.

      1. Getaway Girl*

        This! I have projects that involve pulling data out of one database and converting it to fit the fields in another database. CONCATENATE allows me to take part of two fields and make them into one. There’s a bit of editing beforehand (removing everything after a specific character), but oh so much easier than what people were doing before with cutting and pasting. I also use the Text to Columns in the same project.

    16. That Would Be a Good Band Name*

      It’s super helpful if you get a list of names that are “FirstName LastName” and you need them to be LastName, Firstname” when used in conjunction with the concatenate formula. Split the names into Columns A and B and the in column C, =concatenate(B1,”, “,A1). I use this a TON when I get names from two sources and I need to be able to vlookup info from one spreadsheet to the other.

    17. Hanners*

      Text to Columns works with dates that haven’t been properly formatted in excel as well. You can tell it that the information in the column is dates in a different format than what excel is set up with and it will automatically convert all of the dates to a usable excel format.

    18. Fabulous*

      Alternatively, if they names are in two columns but you want them together, the formula is:

      =(A2&” “&B2)

    19. Lexi Lynn*

      It’s also very helpful if you have a list of email addresses you need to check. Use @ as the delimiter and then filter on the domain (the Gmail.com part). For the big domains, you’ll be able to easily catch where there are typos (like gmail.co or outlooook.com or noemail.com) and fix them before you send. It will prevent some of the bounces.

    20. Michael Golrick*

      And the reverse is useful also: =concatenate(A1,” “,B1) for instance will give you a full name pulled from a first name and last name field.

    22. MsSolo*

      Text to columns is also the easiest way I’ve found of swapping dates from US to UK – Select “Delimited” on step one, untick all the delimiters on step two, then choose the date format you want on step three.

    1. Princess Consuela Banana Hammock*

      I *love* conditional formatting. Especially with different colors for the cels based on their values. There’s something really satisfying about having something light up as green/yellow/red and being able to visually hone in on it.

      1. Detective Amy Santiago*

        Yessssss! A group I belong to has a tracking spreadsheet and we use vlookups and conditional formatting so when we update information on the various tabs, it changes things on the overview sheet.

      2. Laura (Needs to Change Her Name)*

        I do this for grading and my colleagues think it makes me look so fancy.

        We all have Ph.D.s.

        But it is my conditional formatting that impresses them!

      3. Anonymoose*

        Me too. In my field, this makes it SO easy to convert data into a heat map to present premilinary data super quickly without having to twist Excel’s arm.

        Also, Stephanie Evergreen’s data blog saved my life. Highly recommend. She now has a data viz academy if anybody out there does a crap ton of charts and whatnot in their work. :) I’d drop the link but it would get stuck in moderation and it’s really easy to just google ‘evergreen data blog’.

    2. Nan*

      So when ever I do conditional formatting, it takes forever and then any thing else I do with the sheet takes forever. I usually work on sheets that are 20,000 lines or so and probably 12 dozen columns. It does this no matter what version of Excel or what PC I use.

      It also seems to muck up the sort and find functions. They don’t work well once CF has been used.

      What’s the trick to make that not happen? It makes the conditional formatting so not worth it.

      1. Koko*

        Probably more RAM or smaller workbook/fewer cells formatted. I don’t have issues with CF slowing things down, but I do have some very large workbooks that can lag/cause short freezes when I try to do anything too much or too fast in them, even Ctrl + C to copy a large number of rows.

      2. Observer*

        Given the size of your spreadsheet, you should probably be using a database, possibly with some analysis tools, to be honest. But if you MUST use excel, TONS of RAM, and the fastest HD you can find (SSD)

      3. paul*

        right there with you. And sadly other software isn’t an option.

        Trying to work in a spreadsheet with–no joking-300,000 lines and about 25 columns is misery. Particularly on our old machines.

        1. datanerd*

          may I ask why other software isn’t an option? there are very good free open source database management systems out there (I am a huge fan of postgresql) with fairly intuitive user interfaces.

          1. datanerd*

            I ask as someone who has deep empathy for you– I once built an 85 MB excel file once with 409,000 rows in one of 3 sheets. It was a nightmare. Databases changed my life.

            1. paul*

              No budget for software or training in how to use it. And compatibility issues with our CRM on top of that.

              our CRM exports data directly as an Excel workbook. It’s…frustrating.

              1. Jonathan*

                If you have Excel, surely you also have Access too? I’m not a big fan of Access (I prefer using more robust and complicated database systems), but for a few hundred thousand rows, it should meet your needs and is often included in the Microsoft Office suite.

                1. Leticia*

                  Access does have a steep learning curve for people used to Excel. It’s not a great database, but it sure does beat using Excel as one.

      4. Jeff*

        +1 for database. This is too large for Excel. There is a reason there used to be a limit to how many rows/columns are allowed in a worksheet.

          1. Interested Bystander*

            XFD is the final allowed column. I typed =A1 into XFD1, and it made my 10 column, 22 row spreadsheet incredibly laggy.

      5. Samiratou*

        I don’t think this works for conditional formatting, but when I have processor-heavy calculations I go into the Formulas tab under Calculation Options and check “Manual” so it isn’t trying to update all formulas every time I make a small change. When you’re done you click “Calculate Now” and will update the formulas.

        I’ve also found that Excel has a harder time with wide worksheets than long ones, so 20,000 lines isn’t that much (though still on the high side for Excel), but when it’s 144 columns that will add to the lag.

      6. Anonymoose*

        Is there any way that you can roll up your data (perhaps summarizing categories and whatnot) onto a different sheet and then use the conditional formatting on just that sheet instead?

        Also, look into PowerBI. (It may be free with your company’s Microsoft office license, ours is.) PowerBI can handle more data and also allows you to create/share your data a lot easier and a lot faster (and a lot prettier) without having to munch on all of your memory.

      7. MsSolo*

        Once you’ve set it up, try copying and pasting the data over itself as “values and formatting only” (it’s under paste special) and then turn the conditional formatting off again.. This should reduce the load on the spreadsheet and speed it up, but it does mean that if you’re still working with the data the conditional formatting won’t update itself with any changes any more.

    3. Tin Cormorant*

      We used this a lot in our testing checklists. It was so pretty to put in “Pass” and have it light up in green, but “Fail” and have it be bright red.

    4. Jadelyn*

      I love it, but for some reason I always struggle with it, at least in any case where I need to do “use a formula to determine which cells to format”. I always wind up googling for the correct formula for what I want after a few minutes of wrestling with it. Which is weird, since I create complex nested formulas all over the place, but something about the way CF uses formulas always trips me up.

      1. paul*

        I feel 0 shame about just googling the syntax for any of the complex formulas in excel. I’m not going to remember concat off the top of my head.

        1. Samiratou*

          Aggregate functions. If you want to have summary data where that data changes dynamically based on filters, exclude error rows (instead of erroring your entire function), etc. this is a super useful function. Only available in Excel 2010+, though.

        2. Ann O'Nemity*

          Same. I used to take pride in remembering stuff like that but I’m beyond caring now. It’s super easy to Google it.

        3. J.*

          I have some electronic stickies with my dozen most frequently used formulas on my work laptop and keep the sticky note program pinned to the taskbar. It’s saved me so much time just copying and pasting them.

      2. Anonymoose*

        Hey – I have been using CF for yeeeears, and I still google that crap. Ain’t no shame! ;)

      3. Leticia*

        The trick with CF formulas is to do them in another column, that allows you to test the formula and control it better. Just make sure the result is True or False and then point the Conditional Formatting to that cell.

        I do it all the time to make a whole line light up green when it’s done, yellow for in progress or grey as disabled. It does mean that I have three times as many formatting columns as I have data, but they are hidden.

    5. Matilda Jefferies*

      I actually have a question about conditional formatting! Is it possible to format one column based on the text (or formatting) in another column?

      For example, I have Column B formatted so that dates within a certain range are red, and dates within another range are green. Now I want to make the formatting in Column A match the formatting in Column B.

      So if B1 is red and B2 is green, is there a quick way to make A1 red and A2 green as well?

      1. eee*

        unfortunately i don’t think there’s a way to do this in CF, you may have to go into VBA for that. I hope i’m wrong, but i remember that i used to try and google this every few months in the hopes that i was just googling improperly, but the answer seems to be “nope”.

      2. Rachel*

        There totally is! You just have to use “Formula is” instead of “Cell value is”–then play around with it. I don’t know that you can just simply say “If B1 is red, make A1 red” but you can definitely say “If the dates in B1 meet [whatever criteria you used to decide if they should be red” make A1 red.”

        1. Jonathan*

          To make this work the way you want it to, make sure to pay attention to where you want fixed (with a “$” symbol) or variable row and column references in your formulas

        2. Kali*

          Seconded! Select ‘formula’ and then it will be something like; “$A1>TODAY()”. Just make sure it’s not $A$1, else all the values in B will be based on A1 and not Awhatever. That tripped me up initially.

      3. irene*

        have you tried format painting?
        i don’t have excel on my laptop at home, but i could swear that one of our big error checking db exports uses format painter with a conditional. we have a sample column with the conditional formatting, then highlight it, click format painter, and highlight where we want it to go, so that we don’t have to re-type/code the conditional format each time we run the error check (which is weekly right now, but sometimes more often as we do major cleanup jobs)

      4. Susan K*

        Yes! You can do this with “use a formula to determine which cells to format.” If you have a table in rows 1-10 and columns A-B:

        1. Select cells A1:B10.
        2. Go to Conditional Formatting, New Rule.
        3. Under “Select a Rule Type,” click “Use a formula to determine which cells to format.”
        4. Under “Edit the Rule Description,” in the box for “Format values where this formula is true,” type =AND($B1>43008,$B1<43040) [substitute the dates you want for this rule; to get the numbers corresponding to the dates you want, type the date into Excel and then change the format to Number].
        5. Click the "Format" button and select the format you want for this date range.
        6. Click "Ok" to close the format window and click "Ok" to complete the rule.
        7. Repeat for any other date ranges you want to format.

      5. Akcipitrokulo*

        The way I do it – which is probably a bit of a bodge…
        Use a column way out of the way (like AZ).
        In it put in AZ1 “=if(B1=[your condition],A1)”
        Then do conditional formatting for column A – if A1=AZ1, make it purple.

      2. That Would Be a Good Band Name*

        I’m pretty partial to vlookup, but can one of you tell me why you prefer index matching? I haven’t used it, but if it’s better I’m game.

        1. Aardvark*

          I’d suspect it’s because vlookup requires you to look up the first value in a range, and you have to reformat your lookup spreadsheet to accommodate that. This rarely bothers me (I’m usually working with sheets I pulled myself, so I can specify the order) but I know it annoys colleagues who don’t. My hand-eye coordination/habit for having 50 zillion sheets/tabs open at a time makes index lookups too much trouble for me.

          1. datanerd*

            another reason is that it’s less vulnerable to getting broken if you change anything about the table you’re looking things up in– for example, what if you need to insert a new column before the column with the data you’re looking up? you can also use index match to get data out of a table that’s transposed (i.e. rows and columns flipped vs the way you want them in your new table.

            That said, and at the risk of sounding like a broken record, if you find yourself needing to constantly use vlookup and index match, you should probably be using a database instead of excel. I find it’s way too easy for excel sheets to get irretrievably messed up by something as small and inadvertent as a partial sort or one typo in a formula somewhere hidden amongst tens of thousands (or more!) cells.

            1. J.*

              I use index when I get a file from a client that they want me to add to their database, to double check none of the records are already in there and I’m not uploading duplicates.

              I can think of lots of reasons why you’d use it that straight up having a database isn’t going to replace.

              1. datanerd*

                but with good database management practices (primary keys, for example) you don’t need to go through that index step in excel. Don’t get me wrong, I still use vlookup and match frequently for one-off analyses or for setting up spreadsheet models. I agree it doesn’t always make sense to set up a database, but I do think people tend to overly rely on excel to do things it’s not all that well equipped to handle, and that databases would make most people’s lives easier.

                1. Aardvark*

                  That’s contingent on a bunch of factors, though. Yes, you could build a staging table and an ETL process to load in every single dataset, but that depends on:
                  1) Getting consistently formatted source data from all your clients, or investing the time to build a robust and flexible ETL process that can handle different column orders, names, etc. Depending on the frequency and variability of the incoming source data, that may or may not be deemed worthwhile by the powers that be
                  2) If your system is SAAS, you may not have the level of access or tools needed to dedup on import/create additional custom tables/etc.
                  3) That the provider of your data knows your primary key, or that you know theirs and import it into your data set and they never change it without telling you
                  4) That there is no additional human element of the process. We, for instance, get teapot distributor names from a couple different sources, and sometimes the Large Teapot Dispensary in our db may be Lg Dispens. (Tpts) in the client’s data. If they are not so kind as to provide us with a field that can bet matched between the two, a human who knows the client base–and often is not a db expert–may have to do some cleanup first. I, personally, don’t want to train everyone at the company to write SQL, and most of them don’t want to learn it.

                  ¯\_(ツ)_/¯

            2. Aardvark*

              As a database professional, I am so tired of that broken record.

              99% of what I do in Excel is a throwaway analysis of a small dataset for someone who wants 2 pretty charts in an email or a one-off ETL that I really really really don’t want to spend hours writing an SSIS package/python script/other process for. (See XKCD #1205).

              The LOE to turn on my local mysql instance, define 2 tables, reformat the strange spreadsheet I got from customer A into a CSV, loading that and an extract from system B into those tables, dealing with load errors, writing the sql statement, exporting that data, and either reformatting it for load into a target system or exporting it back to excel to make a quick and dirty chart or whatever before tearing down those tables >> vlookup + load + toss.

              I agree that if you’re repeatedly using the same dataset for a repeatable or shareable analysis, a database is the proper home for it. But ad hoc data processing and matching of small lists can be served by any number of tools, of which Excel is one. Also, the PITA that is writing two functions and going back and forth between sheets 2 times where 1 will do for my throwaway dataset is just…not worth it at all to me. I don’t really *care* if it gets corrupted after a zillion uses, because for me Excel sheets are the Mr. Meseeks of data–they solve my problem and return to the void having completed one short discreet task.

            3. Jonathan*

              Vlookup match (with the match for column references) is a nice compromise to have more flexible functionality

        2. SarahKay*

          Vlookup only works from left to right, while Index Matching will go in either direction. Also, I’m told (can’t verify, though) that Index Matching uses fewer resources, so if you have thousands of vlookups (Guilty, M’Lud!) it can really slow down your spreadsheet.
          I know I should use Index Match, but the problem is that I’m incredibly familiar with vlookup, so default to that.

        3. Leslie Knope*

          I also want to jump in here. I combine the Index and Match formulas in lieu of Vlookup. So often when I’m using these formulas it’s because someone has exported their view in a particular system or database–which have customizable views. Vlookup is based on the number order of the columns, whereas with Index Match you can base it on the title of the column.

      3. Beatrice*

        I love Index Match, and have also discovered Index Match Match recently. Index Match Match is helpful when you need to look up something from a matrix, and need the formula to identify the correct row AND column. For example, if you have a matrix of the number of births per state per year, and want to write a lookup to find the number for Georgia for 1980, you can use Index Match Match.

    2. Shelby Drink the Juice*

      I love vlookup. I’ve tried the If Index Match before and I just get confused. Vlookup for life!

      Also, concatenate.

  4. Meh-ing*

    Probably a lot of people know this basic one that I found out last week, but it blew my mind when I accidentally discovered you can delete an entire word by holding Ctrl + Backspace (rather than holding backspace down/constantly pressing it).

    1. Frank Doyle*

      Or Ctrl+Delete to do it on the other side of the cursor! Also Ctrl + the arrow keys to move to the end of a word.

        1. Mpls*

          Mouse click once to place the cursor, double click to highlight the word, triple click to highlight the entire pragraph.

        2. Miss Pantalones en Fuego (formerly Floundering Mander)*

          Ctrl – Shift – End or Home will highlight from your cursor to the end or beginning of the document, respectively.

          1. Miss Pantalones en Fuego (formerly Floundering Mander)*

            Oh, and Ctrl – Shift – Up or Down will select to the beginning or end of the paragraph.

    2. Koko*

      The Ctrl key has functionality like this across a lot of applications, allowing you to quickly and precisely skip around in various ways.

      In text processing, Ctrl + arrow key will move your cursor by one word at a time. Because Shift + arrow key highlights text, you can you Ctrl + arrow + Shift to highlight text one word at a time. While Home/End take you to the beginning/end of a line, Ctrl + Home/End take you to the beginning/end of a document or field.

      In Excel, Ctrl + Tab toggles between all open workbooks. Ctrl + Page Up or Page Down toggles between sheets in the current workbook. Ctrl + Shift + Tab/Page-Up/Down toggles in the reverse order, so you can use the two together to go back and forth between two workbooks – comes in handy if you have two monitors with two workbooks open and want to switch back and forth between the two without using your mouse or calling up every other open workbook in between.

      1. Changed*

        CTRL-arrow in Excel goes to the last filled cell in a row in the direction you pressed, or if the cell to that direction is empty it moves to the first filled cell in that direction.
        CTRL-SHIFT-arrow in Excel SELECTS to the last filled cell in a row in the direction you pressed, or the first filled cell after the empty ones.

        If you have no empty cells in column A or row 1, select A1, hold CTRL-SHIFT, and press right and down to select the whole table.

      2. dawbs*

        ctrl D duplicates the entry in the cell above it.
        THis is one of those things that is useless 90% of the time; only useful in odd little projects. But it’s insanely useful for those.

        1. Amelia*

          Not useless – it will duplicate formulas, changing cell references as it goes, so incredibly useful for filling down columns. (Say you have two columns you want to multiply by each other. =a1*b1, then fill down so that the next cell down is a2*b2. You can do this for any formula.) I basically have a tic in my left hand that’s ctrl s and ctrl d.

      3. DG*

        Learning about Ctrl + Page Up/Down changed my life. I make so many graphs and deal with so many complicated datasets with intricate formulae that needing to move between worksheets with the mouse was seriously impacting my productivity.
        Also Ctrl + Home/End to move to the beginning/end of a document, even in Notepad. Especially in Notepad, actually. And then throwing in Shift to select stuff.

  5. Mike C.*

    Biggest Excel trick for me – know when you’ve gone beyond the limits of the program. There’s a lot you can do with Excel, but once you reach certain data sizes or need other features it’s really time to move on to something more robust.

    1. Mike C.*

      Ok, actual trick: When you click on a cell, you can select the remaining cells in a direction with Ctrl-shift-arrow key, where the arrow is the direction you want to go. You can select an entire block by choosing two arrow keys at a 90 degree angle (left, then down, etc).

      2. Tableau Wizard*

        this is my favorite trick too! I can do so much without ever touching my mouse – it amazes people!

      3. Jadelyn*

        Gods bless ctrl-shift-right, ctrl-shift-down to grab an entire chunk of stuff. I use that one all the time.

        1. FedLiz*

          I use this too but haven’t figured out how to get around it when there are a bunch of extra blank rows at the bottom for whatever reason.

          1. Just a thought*

            They may not be truly blank (a space in a cell still counts), but this trick SHOULD select only those with data in them.

          2. I enjoy properly sized scrollbars*

            -Click on the first cell directly below your data
            -Select all the “blank” cells using the ctrl-shift-down-right
            -Right click the and select Clear Contents
            -Save your workbook
            This should both clear the weird “blank” cells and fix the scroll bar to match your data

            1. Magenta Sky*

              I’ve found it more reliable to select one cell in each blank row, then use to select the entire row, and right-click and *delete* the rows.

              Often, you have to save before it recalculates where the bottom is, though.

    2. LCL*

      Like, oh, using it as a way to display schedules, and trying to use different fonts and effects for night/days/weekends/rotations, etc? I have pushed it to its limits a few times, I had thought since there weren’t any formulas it would be easy to do, but excel wasn’t really designed as a graphic display program…

      1. Magenta Sky*

        Our national franchise used Excel (macros) to write a time and attendance program. It worked, if you had enough memory to handle a spreadsheet so big you couldn’t email it.

    3. Gaia*

      Do you mean like trying to manage a clean of all shipping addresses for a multi national corporation with roughly 30 years of shipping history across 100 countries?

      I don’t want to talk about it.

    4. DG*

      I’ve just started the process of trying to teach my fellow engineers some programming skills (LabVIEW, to be specific). They’re great at data processing in Excel, but often use it for tasks that would be far simpler in another environment.

      It just goes to show how different professional backgrounds can be – it just boggles my mind that somebody can be a whiz at CAD but not know how to make the computer print “Hello, World!”

  6. Cat H (UK)*

    I’m making a list and I want to but a button next to each item so that when you click it, it blanks a range of cells or marks them in red. I want to have multiple buttons, each blanking/marking red different cell ranges.
    It’s a shared to do list so I want people to be able to mark something as done.
    I feel like it’s something to do with conditional formatting but I’m not sure. Can anyone help?

    1. Koko*

      I wouldn’t use Excel for this. I’d stick with a project management tool for to-do lists. Excel is more for manipulating and visualizing numeric data sets.

      1. Emily*

        One Note can be pretty great for To Do lists too – and at least at my company it came with the Office package so no need to buy another product.

        Ctrl + 1 shortcut – once and it adds a blank checkbox, do it a second time and it puts a completed check in the box, a third time removes the box completely.

        1. NotThatGardner*

          just discovered my company has One Note in the Office package and am LOVING it. It’s easy to share, too – definitely recommend for shared to do lists!

        2. einahpets*

          Yes, this! At my new job (started two months ago), my department uses OneNote to put together agendas for our group meetings and simple work instructions on tasks. I had never used it before starting here but I’m liking it!

    2. LondonEngineer*

      This would be done with a macro I think (I am by no means an expert so if anyone else has better ideas listen to them). Also I’m using the 2016 version so some of this may be in different places if you are on 2010.
      FIrst make sure the developer tab is visible. There is an Insert option which looks like a toolbox. This lets you place the button wherever you want it. Once you’ve named it and added any description click the record option. Then select the area you want to be affected and colour it red. Go back to the developer tab and select ‘stop recording’. You should now have a button which does what you want.

      1. Changed*

        That will pretty much do it, but without fancier VBA code you would need a separate macro made and tied to each individual button, for each line.

        Without using VBA, you could use conditional formatting and an extra column – when someone puts an X in that column, the row in question turns red, or white-on-white (don’t assume an X, have it trigger if the cell is not blank (“”)). Alternatively you could use an if formula in your cells which returns blank if there’s anything in the cell in question, although that’s less friendly to editing the details.

        With VBA, I’d go for checkboxes – checkboxes are tied to a specific cell and flip it from true to false depending on whether the box is ticked, which you could use as the condition for the formatting on the line. You could have a button which automatically adds a fully-set-up checkbox to the bottom of your document whenever you needed one more.

      2. Linyarri*

        +1
        Also Alt F+11 will open up the macro sheet. F8 will let you step thru the macro to see what each line done. Then you can alter/fix as needed.

    3. Detective Amy Santiago*

      Instead of a button, you can add a column where you indicate that a task is complete and use “if/then” to say that if A2 = complete then A3:A10 are red.

    4. BusyBee*

      Ctrl+h find and replace
      Ctrl+alt+v for special features on pasting values

      Index match match formula. Say you have a table with subsidiaries across the columns and products down the rows and want to retrieve a particular combination of subsidiary+product. =index(table, match (product value, products column), match (subsidiary value, subsidiary row))

    5. Jadelyn*

      You’re talking about macros and VBA at that point. Which I’ve used a few times, and it can be helpful for sheets you’re distributing to less-Excel-savvy individuals, but I’d try to find a simpler way to do it first. Perhaps a “Complete?” column where people can choose either “yes” or “no” (use data validation to make the cell into a dropdown that will only allow those two options) and then conditional formatting for the related cells based on the contents of the “Complete” cell.

    6. BeetleJude*

      If you do decide to stick with excel, you can do a very basic macro and assign it to the button (and by basic I mean even I managed to do it!)

    7. AC*

      Create a toggle box next to each item. In the menu for the toggle, select the cells you want blanked/ marked in red and set it so if the respective box is checked they = TRUE (which equals 1). Then set conditional formatting for these cells so boxes marked true are the color/ formatting you want. If the cells you want formatted have text within them, then you could also use an adjacent cell connected to the toggle, and then use a formula so the cells you want formatted are formatted based on the value in the adjacent cell.

    8. Excel Geek*

      You can use conditional formatting. Example:
      *Put an “x” in cell A1
      *Highlight cell B1 and goto conditional formatting in the menu, select “new rule”
      *Select rule type “Use a formula to determine which cells to format”
      *In the formula box, enter everything between the parenthesis, but not the parenthesis (=A1=”x”)
      *In same window, select the format button, pick tab “fill”, select the color red, and hit OK
      *Now, test it out. Delete the “x” in cell A1, put an “x” back in A1!

    9. Kali*

      I base conditional formatting for the row based on a specific column. For instance, I have a page in my financial spreadsheet for anything I’ve ordered online; I put a ‘Y’ in the received column, and the whole entry greys out. You use Formula, and if [$A1=”Y”] then change colour, assuming your column is A.

    10. bigboicat*

      You do this by creating a macro and then linking a button on your sheet. You can write it so it only changes things you’ve selected instead of placing the button on each row/column.
      Look up “Excel-Easy” and there’s an entire tutorial on it. Once you have the basics down you just need to learn how to look up the name of the programming item you’re looking for (like what’s the name for column, whatever is selected, etc) and you can basically do anything.

    1. V2*

      This is THE big one! Once you start using functions like VLOOKUP (along SUMIFS, COUNTIFS, INDEX, MATCH, etc.), it completely changes how Excel works: instead of data being linked spatially, it’s linked by a common key. This allows linked queries to return a variable number of rows while leaving formulas that point to the data valid.

      1. Elizabeth*

        This is more of an approach than a tip but: learn the Excel terminology and how to use it when phrasing questions for Google. I’m really, really, really good at Excel and everything I know about it I learned from trying to google my problems or ideas. There is a wealth of existing knowledge out there already in the form of both bloggers (yes, people blog about Excel) and forums where people are willing to help you out with your problems, but sometimes you need to be a bit more specific about what you’re asking for (e.g. “put all data from sheet in one cell” is okay, “concatenate data from range in one cell” is better in terms of finding what you need).

        The other tip would be: if you can envision it, Excel can probably do it. I’ve only encountered one thing in the last ten years that I wanted Excel to be able to do that it couldn’t (or at least couldn’t with my version at the time). Everything else was just a matter of good searching and better application of concepts.

    2. DecorativeCacti*

      Can you share an example of how you use it? I’ve been playing around with Vlookup and Index/Match but I only came up with one possible use for it. I don’t know if I’m not thinking right or if my data just doesn’t lend itself to those functions.

      1. Detective Amy Santiago*

        At my old job, we had to update rosters on a weekly basis. So we had our master list of people who were registered and we would pull the current registration list and add or remove people as necessary. When I got the position, they were doing this by manually reviewing the two lists. The first thing I did was add unique ID numbers to the master list so that we could use a vlookup to compare the master list and the new roster. Saved hours of tedious work and eliminated human error.

      2. gingerbird*

        It’s good for a quick completeness test, making sure two sets of data are the same.

        But I use vlookup to add data together. So if you have a list of prices by counties, and a list of states and thier counties, you can use the vlookup to add each corresponding state to each county, along with thier price.

      3. You're Not My Supervisor*

        I used VLOOKUP and HLOOKUP at a previous job where we had to grade standardized tests for early development (Mullen scales of early learning, for example). We all had paper packets filled with tables to show percentile rank/T-score/whether a child’s score was below average/at target/above average depending on their age. IE, a score of 10 for gross motor skills could be a great for a 6 month old, but a not so great for a 12 month old. So first we’d get the child’s score, and then we’d have to manually look up their percentile rank and T-score on paper each time, for every category of development.

        When I put all the tables in a sheet in Excel and used the LOOKUP functions, people could just enter a child’s DOB and their score for a category, and Excel would return the resulting percentile and T-score. It saved us a TON of time looking up the items individually in the printed tables.

      5. Red Reader*

        I used it in a stats assignment recently – I had one list of players with MLB stats, and one list of players and their 2016 salaries, and I needed to find the overlap.

      6. Mouse*

        I use it pretty often when I have a selection of ISBNs and I need to find the corresponding titles and publishers. I have a great big master sheet, so I can use VLOOKUP to pull from that master sheet and insert the info I need for my selection.

      7. LBK*

        I think it depends a little on your data sets – I use it a lot for when I’m exporting data out of two systems or comparing two reports and need to join them together via a unique ID that’s in both data sets.

      8. Jadelyn*

        I use index/match for consolidating lists mainly. I’m in HR and I often have different lists of employees with various characteristics – job title, work location, team, supervisor, specific trainings completed, dates of hire, etc. – and I want to have everything in one list, but they may not be sorted so that they’re in the same exact order (especially for groups of employees with similar names) meaning that copying and pasting whole columns would risk winding up with bad data for some people. So I put both sheets in one workbook, use index/match based on the employee ID number to fill in sheet A with the data from sheet B, copy and paste values on those columns so it’s all data and no formulas, and delete sheet B. Takes less than five minutes and I know for sure that everyone actually has correct data and I don’t have to worry about spot-checking for inconsistencies.

      9. Elsajeni*

        “Elsajeni, here’s a list of all the students who took ENGL 101 last semester, with their names, student ID numbers, and grades. Can you…
        … add email addresses for the ones who got an incomplete?
        … compare it to this list of everyone taking ENGL 101 this semester and compile a list of current students who’ve previously gotten an F?
        … compile a breakdown of grades by gender and ethnicity?”

        Basically, anything where you have partial info about a group of unique people or things in one file, and more info about the same people or things in another file, and need to match them up. In the examples above, I’d be matching on student ID number and pulling data either from one semester’s ENGL 101 enrollment file to the other, or from last semester’s list of all current students in my program to the specific course enrollment file.

        1. Liz*

          Once you get to this sort of thing, it’s worth exploring databases (including Access). So much simpler to query in SQL than to do in Excel.

          1. datanerd*

            +1000! I have vivid memories of a grad school project I worked on before I knew how to use databases. My professor at the time urged me to take a database class, knowing full well that it would blow my mind to realize that what took me days to accomplish in excel could be done in SQL in about 15 minutes.

          2. Magenta Sky*

            Simpler to use, more complicated to set up.

            (And be very careful about file size. Access doesn’t do well in gigabyte sized data sets. Of course, Excel will light your desk on fire long before it gets that big anyway, so not a big concern.)

          3. Elsajeni*

            I’m sure! But most of the Excel files I’m working with are already the outputs of queries on our central university database — in most cases I could go back and rewrite a new query to give me all the parts I need in one place (and I have for one of those examples, the “find out who’s repeating this course because they flunked it” one, since it comes up every semester), but it is… definitively not simpler or quicker than doing a quick Vlookup on two Excel files I already have.

    4. Arya Snark*

      There is not a day that goes by that I don’t use a vlookup somewhere. It’s such a time saver!

      1. MarsJenkar*

        VLOOKUP and HLOOKUP are very useful, but for situations where I want to be able to sort the table in other ways (which is often), or I want to check for exact values (which is also often), I use a combination of INDEX and MATCH.

        Sample formula:
        =INDEX([data_array],MATCH([lookup_value],[lookup_array],0))

        The data array is a row or column (or part of one) that contains the data you want to pull.
        The lookup value is the value you want to match in the lookup array.
        The lookup array is a row or column (or part of one) is a ‘key’ for the data array, containing many possible lookup values.
        The 0 in the formula means the MATCH formula is searching for an exact value.
        It’s possible to make it work like a normal VLOOKUP or HLOOKUP by choosing -1 (descending) or 1 (ascending) instead, but it’d be simpler to simply use VLOOKUP or HLOOKUP in most such cases.

        2. Willow Sunstar*

          You can do arrays in Excel without VBA? Really?

          I have written macros in the past, but I will have to try that.

          1. MarsJenkar*

            The “array” in question is simply a range of cells. It can be a named range, or in the format of A2:A25 or the like.

    5. Jadelyn*

      I really prefer INDEX/MATCH to VLOOKUP – it’s more flexible – but VLOOKUP still has its place – as evidenced by a rough problem I was dealing with yesterday that I eventually had to use VLOOKUP to solve since I couldn’t get the INDEX/MATCH to work for it.

      But lookup functions in general are absolutely key with a bunch of stuff I do. Don’t know how I’d function without them.

      1. LBK*

        I only use INDEX/MATCH when I need to go backwards, since VLOOKUP can only go to the right. Otherwise I find VLOOKUP much easier/faster to write, although I suppose if I had more practice with it I’d get faster at it.

    6. eilatan*

      I like INDEX, MATCH a bit better than VLOOKUP because it lets you go left AND right from your key field. This is really handy when someone insists that the unique identifier for each record can’t be the first column and you have to do calculations with the records elsewhere. [eyeroll emoji]

      I do a lot of work with data extracted from a SQL server database, so the other thing I do is write macros that will automatically refresh the dataset and update all pivot tables–there are a fair number of people where I work who forget to refresh the pivot tables and then there is sadness and woe. This also means I don’t need to remember where every single darned pivot table is on the workbook.

    7. Zahra*

      More VLOOKUP goodness:
      Say you have a few columns that must return the data from a range formatted in the same way. Imagine product number, product category, product department, for example.

      Add a line at the top of your table. Now add in the column number where the information is in your lookup range.

      Your new formula: = VLOOKUP($A3,Sheet2!A$:F$,B$1,FALSE)
      Copy paste down and right. Your table is complete!
      $A3 so you’re always looking up the data in column A
      B$1 so your column number is always pulled from the first line of the spreadsheet.

  8. Nanc*

    I have no concrete helpful tips but often discover when I Rage Google how to do something in Excel or Word, the addition of an F Bomb brings up the answer (which, alas, is often that useful thing everyone always used was taken out in the last update because “nobody” uses it.)

      1. Jadelyn*

        It’s when you lose your temper after wrestling with a stubborn Excel problem or a formula that is NOT WORKING THE WAY IT’S SUPPOSED TO DAMMIT, WHAT IS WRONG WITH YOU, ARE YOU JUST TRYING TO DEFY ME, YOU SOULLESS LITTLE BASTARD??? and instead of just googling “how to do XYZ”, you let off a bit of frustration by googling “how the f*ck do I do XYZ goddamnit”.

        3. Ego Chamber*

          I know, right? When Windows updated to 8 and everyone hated it, I found the best workarounds by googling “unfuck Windows 8” (that’s how I got instructions for adding shortcuts and command lines to bypass that hideous tile setup—which I didn’t need or want because my laptop wasn’t a touchscreen, WINDOWS, and while tiles are ideal for a touchscreen interface, I wanted my laptop to look and act like a normal f#cking computer, WINDOWS—to launch directly to the desktop, etc).

          Tl;dr: Swearing at Google about frustrating problems is aces.

          1. Jadelyn*

            …I literally snorted out loud at your pointed aside to WINDOWS there. That was 110% my reason for hating Windows 8 and refusing to use it – if I’m not on a tablet or touchscreen, I don’t need touchscreen-based interfaces, now do I?

    2. Hey Karma, Over here.*

      I was so surprised when I did that! I see now I was rage googling. “making an effing xyz in word” OHMYGOD!
      Not only did I get an answer, but I had a mood altering laugh about the whole thing.

      1. You're Not My Supervisor*

        “Dear Alison, I rage googled and now I’m in trouble with my boss. What should I do?”

          1. Matilda Jefferies*

            I can’t even tell you the number of times I have raged at Microsoft for messing with my bullets. JUST LEAVE THEM THE F ALONE AND LET ME DO IT MYSELF ALREADY!!!

            2. eilatan*

              PowerPoint bullets are even more infuriating. Especially when your corporate template was designed by someone who Just Didn’t Care About It.

    4. Hermione*

      A SFW version of this is to include the word ‘solved’ in your Google query, because a lot of discussion boards allow the questioner to mark problems complete when the answer is posted. It helps you avoid being frustrated by others who had the same problem in 2009 and yet never posted whether they figured it out.

      1. Nanc*

        Great tip–I will probably still rage Google from time to time but it never hurts to throw in another keyword.

    2. Observer*

      That’s in both Word and Excel and it’s GREAT.

      Styles for Word is also really good if you are trying to get consistency in set of documents.

      2. SarcasticFringehead*

        Always always always styles. Map them to some keyboard shortcuts and you can format a whole document without touching your mouse. You can even make a game out of it! (Not that I would, of course. Because I’m a normal person with normal hobbies.)

    3. EW*

      YES. I want “abc” to look just like “xyz” highlight “xyz”, Format Painter, highlight “abc”.

      I love that you can double click on it and then just highlight away in the document or excel sheet.

      1. JanetM*

        Also, I just learned about ctrl-shift-c and ctrl-shift-v to copy and paste formatting — it’s the same as Format Painter, but (for me) faster.

        4. SarcasticFringehead*

          Plus, just like ctrl-c, any formatting you copy with ctrl-shift-c will stay in your clipboard until you copy other formatting or close Word, so you can use it intermittently as you’re working through your document instead of all at once.

        5. Nea*

          This is going to sound silly, but trust me, it’s magic. Tell the autocorrect feature that it’s actually a typing macro. If you start your “Replace” with a symbol instead of a letter (so it doesn’t change actual words), you can put an entire sentence into the “With” field.
          .vs –> Very sincerely yours, Fergus McFerguson, VP Teapot Inspection, My Royal Dalton Tea Cups, Ltd.
          @@ –> myverylongcorporateemail[at]ludicrouslylongcorporationname.com

          At one job, I turned autocorrect into a basic HTML editor. Saved tons of time!

          2. mic drop by google*

            so true! i used to have to do a lot of typing that involved giving sources for quotes in a particular format, and I set it up so that -abc would automatically turn into —Atlantic Banana Company, and -xyz into —Xenophagy Yearly Ziggaurats, plus some quick codes for em-dashes in general and a few other specific random things.

            For me the trick was really about figuring out what would be a common-enough tiny timesuck to warrant setting up a fix for it.

      2. Matilda Jefferies*

        Oh my gosh, this may be the exact answer to my question above! I have to run to a meeting now, but I will play around with it when I get back and see what happens. Whee!

      3. Miss Pantalones en Fuego (formerly Floundering Mander)*

        Love these tips but I gotta say, this is why I taught myself LaTeX for writing my PhD thesis. You change all the formatting by changing a few lines of code at the start of your document and it works it all out when you compile it. So much easier!

      1. Snark*

        Or if you assemble 150-page documents and just need to make sure all the paragraph spacing and so on is consistent.

        1. Changed*

          Watch out for this one, it will destroy any bullet points and (I think) superscripted reference numbers and “th”s after dates etc

    5. Sarah in DC*

      Double clicking format painter will let you use it multiple times, if you want to apply the same format to several different ranges. Just hit the esc key when you are done.

    8. Chameleon*

      The day I discovered Format Painter was the best day of my career.

      (Actually, it was the day I discovered the IF function, but…)

    9. Gandalf the Grey*

      I thought I knew excel, but was oblivious to painter!!!! With this knowledge I will become Gadalf the White….. of Reporting.

    11. Willow Sunstar*

      <3 this. It will save you tons of time, particularly on reports where formatting matters because a bunch of people print it out.

    12. CreationEdge*

      Along with Format Painter is the F8 shortcut, which simply repeats the last formatting you applied to your current selection. Font changes, color changes, borders/lines, etc.

  11. LSP*

    The best things I have learned from working with some Mircrosoft pros are:

    – Use your style sheets when creating a document. It saves so much time when doing the final formatting!
    – DO NOT use your space bar/enter to move content to the next page. That’s what page breaks are for.
    – If you want to go to the next line in, say, a list, but you don’t want a new bullet, hold shift when you hit enter.

    These may seem super basic to some people, but I am always surprised by how many people I know who don’t know these things, and regularly make our desktopping pros crazy because of it.

    1. Hey Karma, Over here.*

      If you want to go to the next line in, say, a list, but you don’t want a new bullet, hold shift when you hit enter.

      This. I always hit return and backspaced. This is very cool. Thanks!

      2. S*

        It’s called a soft return. It forces you to a new line, but keeps you in the same “paragraph.” So you won’t see any before/after paragraph spacing, either.

    2. DecorativeCacti*

      The space thing. Oh my god. Sometimes I turn on hidden characters and want to cry. “I don’t know why this won’t format right for me??” And there’s a numbered list in a table with an enter at the end of every line.

      1. Agatha_31*

        I mentioned it in a previous discussion on Word skills but the view formatting characters function is SO GREAT for when Word *looks* like it’s decided to just be a jerk, then you click the pilcrow and go “…. ohhhhhhhh! Yeah, there’s your problem.” Awesome enough for when I’m working on a document formatted by someone else, even better when someone calls me over for help and I have no idea what they’ve done to get up to that point – a quick view of their formatting choices often solves it. Using the little dropdown arrow next to the back button to view the user action history is also super, super helpful, because you can pinpoint precisely where they went wrong, or even if you don’t know, ‘undo’ one step at a time paying attention to exactly what you’re undoing until the issue at hand disappears, and now you’ve helped a co-worker fix something *and* learned another way Word likes to be tricksy.

        1. DecorativeCacti*

          I work pretty much exclusively with hidden characters on. So many people look Over my shoulder and are like, “What’s wrong with that?!”

          They’re usually the ones whom I’m fixing formatting for. Haha.

          1. Wheels*

            Me too. I first did word processing when there was. I option to turn it off and so it’s how I have always worked. Some people really panic about it and just last weeks someone was concerned that my email would appear like that to the recipient.

    3. S*

      and DO NOT USE spaces to position text!!! This is what tabs are for. I learned to type before single spaces after a period was a thing, so I tend to go through and search/replace double-spaces for single… and I’m always amazed to find that people have indented text using 12 spaces instead of a tab!
      If you’re in a table, you can still use tabs! Just hit CTRL+Tab!

      1. EW*

        This is my life right now standardizing documents. Ctrl H to find and replace all the double spaces after periods!

        1. datanerd*

          …. my comms department recently did this to me, but in reverse. They ADDED extra spaces after periods. Sigh.

      3. Angel*

        This is funny, because I grew up doing single spaces after full stops, but then I wrote a novel in manuscript format in my senior year of high school, which requires the double space, and I’ve never gone back. I can, but I honestly prefer the two spaces. It helps my eye tracking, I think.

        1. bridget*

          The author of one of my favorite books (Typesetting for Lawyers) notes that people often think this, but it is probably not true, because virtually all professionally typeset documents we ever read (published books, magazines, newspapers, this website) use single spaces between sentences. They seem to be read successfully.

      4. bridget*

        One of the many many wonderful things about single-spacing between sentences is that it is very easy to fix errors! My “word trick” is that when finalizing a document, do a “find and replace,” telling Word to find all ” ” [that’s two spaces in a row, if it doesn’t show up, because I know Alison has it set up to change two spaces to one] and replace with ” ” [one space]. I also always search for three spaces to make sure none of those sneaked in anywhere.

        My current job has a house style of two spaces between periods, and it’s super annoying because you have to manually check that you have done it consistently.

        1. RestlessRenegade*

          I’ve also had three spaces between periods pop up in documents I review (and sometimes four. Or six.) Instead of using Find+Replace All for each iteration, I just Find+Replace All two spaces with one (as you advised) over and over again until there are no double spaces left in the document. Of course, if people are using spaces to format, this will ruin them, but they kind of deserve it.

      5. Miss Pantalones en Fuego (formerly Floundering Mander)*

        This is pretty much every job application form written in Word that I have ever filled out. Fixing the formatting so that it looks good as a PDF instead of a printed form that you fill out by hand (which nobody ever uses these days) often takes longer than filling in the application itself.

    4. EW*

      Can you please explain style sheets? Or does anyone have a good resource for learning how to use these?

      I ask because I’m having to format a lot of documents to look the same (that other people created). In powerpoint, I can apply the desired template and then do a final cleanup – what is the equivalent for that in word?

      1. Emily, admin extraordinaire*

        I’ve linked a fairly thorough tutorial on my user name. Styles are awesome. In your case, I’d create a template (it can even be your Normal.dotm one) with the styles set up exactly the way you need them to be, then import the styles from that template into the document your coworker created. You can then apply the styles to the document as you clean it up.

        1. EW*

          Thank you! I found that in the google results but felt skeptical on the quality of the info based on the formatting.

        1. Elizabeth West*

          I made a template for my consultants at Exjob and I set it so when they copied and pasted from other reports (common, as some information didn’t change), it would automatically reformat to match the template. It was incredibly helpful, especially when they pasted a thing from a really old report in the prior format.

    5. purple otter*

      Paragraph formatting (right click within a paragrph), and select “window/orphan control” and “keep with next” under line and page breaks.

    8. Chief Strategy Officer*

      Have any tips for getting people to actually use their style sheets? As the official Word formatter at my company, I’m dying to know! They all complain about the templates but if they would just use the styles, our lives would be so much easier!

      1. Feline*

        You can lock a document to only allow users to use the styles in your style sheet. Review tab > Restrict Editing > Limit Formatting to a Selection of Styles, then click Yes, start enforcing protection and password protect that bad boy.

        Having multiple users working from the same template made it so much easier to compile their content into one document because it all just flowed in. Totally worth it to have to hear the angsty “but I want to bold with the B button” complaints.

  12. Machiamellie*

    in Excel, if you put the cursor at the beginning of a spreadsheet, ctrl + shift + end will highlight all of the cells to the end of the spreadsheet for you.

    Copy a cell, then highlight a bunch of other cells and you can paste into all of those.

    1. Drama LLama's Mamam*

      This is one of my favorite tricks to get around a large spreadsheet! shift+end+arrow also works to get to the end of a row, or the top or bottom of a column.

  13. Triangle Pose*

    This changed my life. Copy/paste formatting in Microsoft Word:

    highlight text with desired formatting, Ctrl+Shift+C, highlight new text, Ctrl+Shift+V.

    Applies desired formatting to your new text. Don’t know how I was a lawyer for 2 years without figuring this out. Ahhhhh!!!!

    1. LondonEngineer*

      You can also use the format painter tool to do this (Looks liek a paintbrush, should be in the ‘home’ tab)

    2. k.k*

      How did I not know this!?

      This post is making me think that my hatred of Word stems from the fact that most people don’t actually know how to use it.

    3. J.*

      Paste formatting (and its counterpart, paste values only) are two of the Office suite features I use more than anything else. It’s so wonderful.

  14. gingerbird*

    If you put a sumproduct around a sumifs, you can use multiple OR criterea. So instead of having sumif()+sumifs()+sumifs(), you’d use sumproduct(sumifs()), where the criterea is listed in a seperate collumn.

    Downside is that it makes your workbook really slow.

  15. CatCat*

    For the legal professionals out there, did you know that in Word, Alt+21 = §?

    I only learned this last year. No more copying and pasting or “insert symbol” for section symbols for me!

    1. President Porpoise*

      I couldn’t make this work for me just now, but I did learn in trying that if you hold Alt down, it gives you the keyboard shortcuts for the menus!

      2. Astor*

        Adding the note that you have to use the numberpad in order to make ALT+21 work. If you enter 21 using the numbers at the top of the keyboard it won’t work. It works for me with Num Lock on and off, but if it still doesn’t work I’d try turning Num Lock ON.

    2. Teapot Librarian*

      I change the keyboard shortcuts for this like that. I make the section symbol ctrl-shift-s. You can also do things like non-breaking spaces so that when you cite to a statute, you don’t end up with the section symbol on one line and section number on the next. ctrl-shift-space

      2. Charlottemousse*

        I changed mine to Alt+s at work on Word at work, but I once did out of habit that shortcut in Outlook and my message was sent! So mine may not be the best recommended change.

    3. Myrin*

      I was super confused about this for a moment but then I realised you guys probably use English keyboards – on German keyboards like mine, the “§” shares a key with the number “3” so is very easily accessible. Now I wonder why that is – does the stereotypical (and honestly, usually true) German bureaucracy (and as such, extensive use of the § symbol) extend to such things as keyboard design?

      1. Turtlewings*

        Now I’m curious where your # is, since that’s what’s above the 3 on English keyboards.

          1. MsSolo*

            UK style? Do you have a £ or euro on the 3 instead?

            (I was training people recently at a venue that had MACs with US keyboards which were set up as UK, which meant having to manually explain to everyone that @ wasn’t were the picture of it was on the keyboard, but was in fact where it usually is, which is fun when you’re working with people who first need email explained to them as “the thing you need to access the website”)

    4. Mango*

      I programmed my Word autocorrect to change “Ss” to §. Doesn’t help in browsers, but really speeds up my writing in Word docs.

      Autocorrect is under File > Options > Proofing > Autocorrect Options. You can add new words to the autocorrect dictionary there, also good for things you regularly misspell that don’t automatically get caught.

      1. Chameleon*

        I would guess that would lead to a gross loss of essential business. In my profession, it would assuredly mess up assorted dossiers and assessments, and lead to embarassment, excessive stress, and my dismissal.

        1. Catroina*

          I would gueSs that would only happen if you have weird iSsues with capitalization. If so, it is eSsential to correct those first ;)

      2. Arjay*

        Yes, I’ve added actual words like “manger” to autocorrect because 98% of the time, I mean “manager.”

    5. Delta Delta*

      I assigned shortcuts to the section and paragraph symbols when I started practicing law so I wouldn’t have to do the insert symbol thing. I think I picked something like command-alt-s and command-alt-p so it would be easy. I felt like a genius when I figured out this was even possible.

    6. D.W.*

      My dad is a lawyer. I just text him this and he is so excited! Thank you.

      He also just figured out that alt +20 is the paragraph symbol.

    7. DG*

      For scientists and engineers, ALT+0176 gives you the ° symbol. My far more experienced manager was always surprised at how casually I would put degree symbols into reports and even emails that way!

      And if you happen to find yourself discussing Norse history and mythology a lot, ALT+0240 gives you the “eth” letter, so you can spell Ragnar Loðbrok’s name properly.

      1. RestlessRenegade*

        Or Alt+0174 for registered, Alt+0153 for trademark, Alt+0181 for micro, etc. I use all these frequently!

  16. S*

    When I was first out of school, I spent a few years temping. It was the late 90s and I hadn’t really realized the full time-wasting potential of this newfangled “internet” thing yet, so when I had downtime (frequently), I would literally go through every option on every drop-down on Word and figure out what it did. I went on to become a technical writer, and this knowledge has been and continues to be IMMENSELY USEFUL. I only wish I’d done PPT and Excel, too.

    A few years ago, I sat down and figured out VLOOKUP and it’s now my favorite thing.

  17. Laura (Needs to Change Her Name)*

    Right tab stops in Word.
    Showed a student how to use them and she said “oh my god are you a wizard?”

    Also, CTRL+T for hanging indents. It only saves like one click but it feels so fancyyyy.

    1. JulieBulie*

      I didn’t know about Ctrl+T. Thank you!

      And Ctrl+E to center text. Been using MSWord since 1987 and only learned that one within the last year. (I don’t center much.)

      1. JulieBulie*

        It’s a tab that lines everything up on the right side rather than the left.
        You can also set up centering tabs and decimal tabs.

        You set these up the same way you set regular (left-align) tabs – either by going to Tabs by clicking the Tabs button at the bottom of the Paragraph formatting dialog, or by selecting your text and using the ruler and the little gadget to the left of the ruler to pick your tab type.

  18. Yas Queen*

    I have a few that come immediately to mind:
    – Pivot Tables! They take a little figuring out at first, but they’re so powerful for slicing, dicing, summing, and analyzing data. Pivot Tables 4 LYFE.
    – Use Alt + Enter to create a new line within a cell.
    – Use $ sign in formulas to always refer to fixed cell, i.e. $B$2.
    – Don’t even bother with thick black border except maaaaybe after you’ve done all your other cell formatting.

      1. DecorativeCacti*

        Related: ctrl+tab will get you a tab in a cell without just moving to the next cell. I think this one is just Word, though.

    2. Elizabeth*

      I joke-but-not-really about how Pivot Tables are life changing. People think I’m nuts when I say it, but then when I demo it for them with some giant spreadsheet of data that they’ve been manually sorting through, they always look like they want to cry with happiness.

      One more thing about the $ sign in formulas: if you press F4 while your cursor is in the cell reference, you can toggle between the different options (e.g. A1 vs $A$1 vs $A1 vs A$1) until you get to the one you need.

      2. NotAnotherManager!*

        I am president of my organization’s chapter of the Pivot Table Fan Club. It saves me so much time and effort, and I am winning new converts every day. Someone brought me a spreadsheet that had a lot of impressive coding and formulas in it but was WAYYYYY overkill for what they were trying to do. We made them a pivot table in about 30 minutes, and they are much happier with the results.

        1. Detective Amy Santiago*

          Basically? They are just a quick way to summarize the data in a spreadsheet.

          Like… if you have a sheet of information about teapot purchases and you want to know how many people bought each color, you can make a quick pivot table that will display that info.

          Your data would be something like:

          Name Order Date Color City
          Joe 1/1/15 Blue New York
          Sally 1/6/15 Pink Boston

          And you could format the table to display

          Color How Many
          Blue Teapots 6
          Pink Teapots 12

        2. Shelby Drink the Juice*

          I use them all the time. I work proposals but when I pull the report to see what’s in it it has 40 columns and 20,000+ rows.

          Using a pivot of the report I can pull out just the info I want. There’s 4 boxes to choose from on where to put the data so I filter by team, columns by year, estimate task in rows and sum of values (totals of estimates) in the bottom right box.
          Basically it takes a butt load of data and makes it into a table. But you can easily manipulate it. If I want to pull in or remove data, maybe filter it for only one year of values, etc.

          And everyone thinks I’m a wizard because of them.

    4. Finman*

      The next thing to learn in pivot tables are adding a formula. I use this frequently to get weighted averages of different buckets. Add a column (weight for rate) to your data with whatever you are weighting (average rate) against the other field you are using as your weight (amount funded). Then in your pivot table, add a formula of iferror(weight for rate / amount funded,0) and it’ll give you a weighted average rate for whatever slices and dices you need.

    5. Jubilance*

      Hitting F4 when your cursor is on a cell name like B2 will automatically add the $ signs! You can cycle through the $B$4, $B4, and B$4 by hitting F4 repeatedly.

      1. LBK*

        This one changed my life when my boss showed it to me earlier this year. So must easier than selecting all my cells, then having to click back into the formula to add the dollar signs!

    6. Eugenie*

      I JUST taught myself Pivot Tables and they’re GREAT! However, a big part of what I’m trying to do is find correlation between different elements (for instance, temperature variance and our monthly attendance). So far I can’t find a way to get an actual Pivot Scattergraph that adjusts with the table. Does anybody know a solution for this that isn’t just creating a normal scattergraph from the Pivot Table?

      2. Changed*

        I’m not one for graphs, but I am a big fan of the GETPIVOTDATA formula which might help here (as long as you make sure your pivot is finalized before you start using it).

        It seems pretty complex if you type it in, but if you type in = and then click a data cell in a pivot, you get one fully formed.

        Then I recommend copying your column and row titles from the pivot to wherever you want the pulled-out data to be, and change the formula so that the relevant “Item” fields in the formula point at the row header or column header so you can fill the rest of the table.

    7. Jadelyn*

      I have a love/hate relationship with pivot tables. I don’t use them often, so every time I do I have to basically re-learn how to use the fields and how to format the stupid thing. Plus side, they look Very Fancy and are great for impressing bosses who don’t know how to do them.

    10. Teapot Project Manager*

      Pivot tables are the best!

      And being able to filter or sort by color of cells. I frequently color code my spreadsheets and when they added being able to filter or sort by color I was so thrilled!

    11. Monika*

      For a fixed cell you can press simply F4. I.e. type b2 press F4 -> $B$2, pressing F4 again gives you B$2, pressing F4 again gives you $B2, pressing F4 yet again and you are back to B2.

  19. Systems Administrator*

    Need dummy text in Word just to be able to fill some paper? Type =rand(3,4) in and hit enter. The 3 is number of paragraphs, the 4 is the number of sentences per. Adjust appropriately. =lorem(x,y) does the lorem ipsum text, and =rand.old(x,y) does repeats of “The quick brown fox jumps over the lazy dog.”

    Great for when you’re troubleshooting a printer and need to run it for a couple pages.

    Useful trick in Excel: Don’t forget about the Filter button when working on a table of data. Highlight your header row, use the Filter button, and then you have built in sorting, filtering, and checks for blanks built into that header row without having to be careful of highlighting the whole table before sorting a column.

    1. NatKat*

      Thank you for the Word tip! I usually do the “lorem ipsum” but sometimes I want a very specific amount of text.

    2. JulieBulie*

      Weird. I did a rand.old(4,4). The first paragraph looks fine, but the subsequent paragraphs don’t have periods between sentences.

      This is a really cool tip, though. Thanks!

      1. JulieBulie*

        If you type “=rand(3,3)” without the quotes and hit enter, nothing happens? What version of Word?

      2. altissima*

        Sorry, really late answer, but: for this to work, autocorrect (“Replace text as you type”) must be turned on.

    7. SS Express*

      Amazing! Not only do I have 3 paragraphs of text just like that, they are all about Word so now I know even more tips. This is incredible.

  20. No Mas Pantalones*

    Ctrl + Z to undo. (Ctrl + Y to redo)

    I’m constantly shocked at how many coworkers of mine don’t know this. Keyboard commands are your friends and are faster than mouse commands.

          1. Sarah in DC*

            Yes! and the paste special window it brings up in excel has a ton of other useful things – you can transpose data, you can paste comments and column widths, its great.

          1. Shelby Drink the Juice*

            OMG, I never knew that. I hate having to use the mouse to paste special. You just made my day!

      2. Amber T*

        I was working with a coworker updating a bunch of excels (he was at the computer, I was sitting next to him). Every he saved, he’d go to the top left corner and open up the menu and manually save. I cringed hard (he’s ranked way above me and this is not on the list of things that are important to bring up).

        I also tend to forget how to human when someone is looking over my shoulder while I’m on the computer (“Amber, open the ‘Legal’ folder” – cue to me thinking “where the hell does L fall in the alphabet?? Is it after Q??”), so I ignore anyone’s little quirks while I’m watching them work.

    4. Janelle*

      I’m amazed how few people know control anything is an option. A huge amount of people have zero idea. Then I use it for something as simple as copy paste and you think I’d invented time travel. Lol

    5. Ahmea*

      Also, Ctrl+W to close the document.
      F4 to repeat the last action.
      Crtl+Shift+space for a nonbreaking space

      1. DG*

        Ctrl+W is one of my recent life-changing discoveries. What’s particularly nice is how many different programs have implemented it. It closes browser tabs, it closes documents in Notepad++. It’s close to being an industry standard, and it’s so nice when that happens.

    6. Thlayli*

      hope this isn’t already above I may have missed it – you can add shortcuts to tools you use often to the quick access toolbar. This is from memory so I hope I get it right: Right click on the very top toolbar and choose add, it allows you to search through every command to find the one you want. I use paste values a lot so I set it up as a special button on the top, so no need to go through three levels: paste, special, values. You can do this with literally any command in the entire nest of toolbars. This is in excel not sure if can do in word too but I bet you can.

      1. Tabby Baltimore*

        I like this, too, but found that I can’t change their position on the bar. If I want to have a particular icon at the end, I have to be sure I put it there last. If I change my mind, and want to put a different icon at the end, I can’t use the mouse to just click/drag the icon to the end of the line; I have to delete it from the line-up, then re-add it to the end of the line.

        1. Lexi Lynn*

          Have you tried moving them in the Options screen? File – Options – Quick Access Toolbar. You’ll see two panes – one with all of the commands and one with your Quick Access Toolbar. You can change the order there.

          Also, look at the bottom where it says “Import/Export” – Export all Customizations periodically so if you need to have Excel reinstalled you can import your old Quick Access Toolbar and not have to set it up again.

    7. Ophelia Bumblesmoop*

      I consistently teach my students that Tab moves the cursor between cells when I need them to log in to their school account for me to assist them. They are BLOWN AWAY. Over tab!

    8. Shelby Drink the Juice*

      Alt – (holding alt and hit the – key) will pull up the box to delete cells or entire row/column.

  21. Jilly*

    One of the core functions of my job is to write detailed analysis reports on cost proposals submitted for government contracts. I have to insert a lot of Excel tables into Word. Because different people may write different sections of the report and then it all gets merged together, page breaks move all the time. So when I insert a table into Word, I then highlight the header row, right click to pull up the menu, choose Table Properties, click the tab for Row, and then check the box for “Repeat as header row at the top of each page”. This way if my table ends up on two different pages, each will have the headers.

  22. Koko*

    Remove all hyperlinks from a Word document:

    Ctrl + A to select entire document
    Ctrl + Shift + F9 to remove all links in the selected text

    1. Myrin*

      I have to look up the second one constantly. I use it often enough to be needing it a fair amount of times but not often enough to actually remember it. (I also have a sheet with semi-regularly-used shortcuts on a pinboard above my desk but somehow this hasn’t been added to that yet. I’mma do so right now!)

    3. Janelle*

      I love you for this. I haven’t had much use for Wors in a long time but somehow I didn’t know this sven after years of my old company thinking everything should be in word and format until it died.

  23. Jennie*

    I have a Word Document that I want to make a Table of Contents with clickable links that will take the user to that section. I’m using Word 2010, but the Table of Contents feature is not working well at all. I haven’t had too much time to dig into it. Anyone else do something similar?

    1. CaliCali*

      My first thought is about the styles that you have set up in the document. The TOC draws off of the styles to create the TOC. Do you have your headings, subheads etc. set to the document styles that the TOC is using?

    2. MaureenS*

      To get Table of Contents working properly, the Headings styles have to be used. Check that, generate (or update) your ToC. It’s often CTRL+Click to use it to navigate with. And/or turn on the navigation pane.

    3. The Cosmic Avenger*

      The Table of Contents generator bases the table on Word Styles. So if you don’t have Header 1s and Header 2s, it literally doesn’t know which text are the section headers, and which are main headers and which are subheaders. If you use the Styles properly, it should create a TOC automatically the first time.

    4. President Porpoise*

      Yes! So, for me what I do is use table of contents and do the Custom option to format the way I like, then just use the ‘Add Text’ dropdown to assign headers to be included in the TOC. You just need to remember to update the tale (right click the TOC itself for the option) when you finish.

    5. Not a Real Giraffe*

      Yep, you insert bookmarks at the beginning of each section and the insert a hyperlink in the table of contents to the relevant bookmark (the bookmark option is on the right side of the “insert hyperlink” pop-up).

    6. Safe Now*

      The TOC will trigger off the outline level off your text. Use built in heading styles where possible, but if you just want a quick fix:
      Go to the paragraph that should appear in the TOC.
      Paragraph>Indents and Spacing>Outline level. Use the drop down to set the level you want, then generate the TOC.

    7. Mockingjay*

      You need Multilevel List Headings. In the ribbon, Home tab, paragraph. By the staggered list icon, select the dropdown arrow, then pick 1. Heading from the List Library.

      This allows you to apply the Heading Style Codes. When you insert the Table of Contents, Word will pull all the Headings. It takes a little practice to get the hang of it.

      One tip: Word 2010 has some quirks with headings and compatibility mode in .docx files. Old .doc code in the styles which shows up as a weird black bar instead of the number. Always save as a .docx file and do NOT select compatibility mode.

    8. Jennie*

      Thanks everyone! Sometimes, when things don’t work, I just sigh and move on. This gives me some things to try and start again with!

  24. Arils*

    YAAAAAS. This post gives me life!

    Is there a way I can search across all spreadsheets in Excel at once? I get daily reports on yesterday’s activities and it accumulates (so today’s report will include MTW information; next Monday’s report would include the previous MWTRFSS) .

  25. Emily*

    Excel: I recently just learned about name manager button in the insert ribbon. You can save a range as a particular name so that if you have a number of places referring to the same group of data, you don’t have to keep typing A1:G17 every time. Just call it “Data” or whatever and type that. It’s especially helpful because then you can just update the range in name manager if you need to add a column or row; saves time rather than find each formula to adjust individually.

    Word (and Excel actually): Templates. I’m in love with templates. I never thought it was a big deal until I mentioned it to a coworker and they were in awe. File, Save As, Save as Type – Word / Excel Template. I save all of my forms this way so I never worry about accidentally saving over the blank version.

    1. Finman*

      Learn to use offset in your named ranges and you will never have to update the range ever again. =offset(first cell in your range [like a1],0,0,pick a colum with a value in every row [A:A], pick a row with a value in every cell of the column [2:2]). Every time you add a new row/column to your data it automatically updates the range.

    2. Zahra*

      You can also select your range and type the name of the range where you see the cell reference to the left of the formula bar (i.e. where you see 2R x 4C, you could type “MonthLookup”). Instant range name.

  26. Jess*

    I just discovered how to print the header row at the top of every page when printing – go to Page Layout, then Print Titles and select the top row in the “Rows to repeat at top” section. So simple, so useful.

  27. Queen of Cans & Jars*

    OK, so this is not really a Word tip, but if you need to take a screenshot of something, maybe something you need to put INTO Word, use the snipping tool. It can take a screenshot of just the part of the image that you’re needing, which is something that’s SUPER helpful when I’m creating directions on how to do something. It’s pre-loaded onto your computer. If you can’t find it, just search for “snipping tool.” I thought it was something everybody knew about, but I’ve randomly enlightened my husband and multiple coworkers about this function in the past week.

    1. Jubilance*

      Conversely if you have multiple monitors, using Alt+PrintScreen takes a screencap of just the active screen, which was a livesafer for me since I use 3 monitors.

    2. Frank Doyle*

      I love the snipping tool so much I have it pinned to my taskbar.

      But there’s something even faster: if you’re in Word and you want to paste an image, you can open the image in whatever program is appropriate, and on the “Insert” tab of the ribbon, there’s a now a “screenshot” option, which will insert a screenshot of any of your open windows! Then just use the crop tool to crop out the outside of the window.

    3. Kalen*

      Alt+PrtScn will also take a screenshot of just your active window! I basically use this+the snipping tool for whenever I write up directions.

    4. NotThatGardner*

      only on PCs! On Macs it’s Command+Shift+4 but either way, grabbing just a part you need is so awesome & i am regularly amazed how many people don’t know about it.

    6. Momma Said Spock You Out*

      I’m an HRIS analyst, and I use the snipping tool ALL THE TIME to take pictures of configurations to put into checklists, documentation, etc. Everyone on my team has it pinned to their taskbar. (I think that a few of them have a fancier paid version called SnagIt, but the rest of us just use the snipping tool.) I probably use it at least thirty times a day, if not more.

      2. anony-mouse*

        If you want more fancy editing, you can use Greenshot. It’s free and open source and it can do more than the snipping tool. You can even set it up so that the Printscreen key starts the version of the tool that you need most – mine starts the rectangle selection, but I thing the default is capturing all screens.

    7. notthemomma*

      When I go out to train clients THIS is the absolute FIRST thing we go over!!! I let them know that if they do an ALT or CNTRL+PrintScreen I won’t even look at any errors they send me. Due to what else may be on their screens (protected customer info; think banking or medical level of sensitive) I don’t need to know anything other than what I NEED to know. Plus, when it comes to me its a size where I can read it, not just an army of ants having a picnic.

  28. Rara Rass*

    I use Excel spreadsheets for tracking tasks/issues/etc. so some of my columns have a lot of text. You can click Alt+Enter to essentially do a carriage return inside a cell. This changed my life.

    PS – Is there a newer, hipper word for “carriage return” that doesn’t make me sound so old?

    1. Not My Monkeys*

      I think carriage return makes you sound wise. Some things don’t need to be changed–like the save icon which I had to explain to my teenagers.

      1. Thlayli*

        A guy in my work told me to click on the little H when he was teaching me how to use a program he’d been using for years. I said “you mean the save button?” He did mean the save button. He thought the pic of a floppy disc was a H.
        I felt old.

    2. LBK*

      Sometimes people just use “new line” but that can be confusing – carriage return is ultimately what I end up googling whenever I’m trying to figure out the character codes for them in whatever language I’m programming in at the moment.

    5. anony-mouse*

      Actually, Microsoft line endings are “carriage return”+”line feed” – often coded as CRLF or \r\n (the n is for new line).

      On Mac it used to be just “carriage return” and on Unix based systems (including the newer Macs) it’s just “line feed”.

      That used to give me one hell of a headache when I was in college because I had a Windows machine at home but the computer pool in college was all Unix and couldn’t deal with my Windows line endings.

      I eventually figured out several ways to replace the line endings with the correct ones, but most people don’t need to worry about this anymore, because for the last decade or so it’s been the philosophy that every program should be able to at least read if not write all three kinds of line endings. The super simple plain text Editor on Windows 10 still can’t do it right, but everything else on Windows can.

      My trick:
      If you need a plain text Editor on Windows, don’t use the built in Editor but install Notepad++.
      To convert a file to Windows line endings press Ctrl+H, search for \n (or \r if the file came from an old Mac), replace with \r\n.

  29. Morning Glory*

    For those of you using Office 2016 – Excel’s 3D Powermaps are awesome at creating map visuals for presentations, reports, etc.

  30. Kimberlee, Esq.*

    I am a big user of Google Sheets over Excel, and figuring out how to do different formatting things with that has been really helpful. Format painter works the same way as in Excel, of course, but some of the basic mechanics in Sheets are different (better, imo). For instance, if you just select a cell and paste, it will use the formatting from wherever you’re pasting FROM, but if you “enter” the cell (hitting Enter, or double-clicking) and then paste, it will go with whatever the format is of the cell you’re pasting TO.

    I also just learned how to use COUNT to keep an ongoing count on our staff list of how many people are in DC vs NYC and Remote, and it’s so helpful.

    1. Detective Amy Santiago*

      There are also a bunch of add-ons you can add to allow you to do more functions in Sheets. The one that I like allows me to sort by cell color.

    2. AC*

      you can copy and paste different “elements” of a cell in excel. after ctrl-c, hit alt-e-s sequentially and it will bring up a menu for a bunch of different paste options. the most common for me are are alt esv for pasting value only, alt esf for formula only and alt est for format only.

    3. Lily Evans*

      I love that Google Sheets allows clicking and dragging rows and columns to rearrange them. I always forget Excel doesn’t do that and get annoyed because it’s such a convenient feature!

  31. saffytaffy*

    Can somebody explain to me how I can go through every “advanced” excel tutorial i can find and STILL NOT KNOW how to do the stuff I want like making a header that stays at the top while I scroll, or alphabetizing just one column but not the others, or… or…?

    3. Jubilance*

      If you want a header that stays at the top, use “freeze panes” under “view”

      If you want to alphabetize just one column (but have the other data move accordingly), highlight all the columns and then use “Data>sort” and sort on the column you want.

    4. Queen of Cans & Jars*

      For the header, you need to select the cell that for the row that’s just below the part you want to freeze. Go to View>freeze panes>freeze panes. If you want to freeze a column and a row, put your cursor in the cell just inside where the column & row intersect & do the same.

      This is where my snipping tool would come in handy!! :D

      1. LBK*

        This took me sooooo longer to figure out. Basically, the “point of freezing” will happen at the top left corner of whatever cell you have selected. So if you want to freeze both your top row and your leftmost column, put your cursor in cell B2, then click Freeze Panes.

          1. LBK*

            Right? I spent so much time trying to understand the complicated alchemy of how the hell Excel decided what to freeze. I still have no clue how it works when you have multiple cells selected – I just tested it by highlighting columns A:C, and it put the freeze point at the top left corner of cell F21. Why???

    5. Kalen*

      Honestly, the best advanced Excel tutorial is Google. Classes can be great for filling in general knowledge gaps or teaching you about functions you hadn’t gotten around to trying to mess with, but for day to day “How do I…?” or “Is it possible?” stuff there’s no beating Google.

  32. Emily S.*

    I really like being able to embed an Excel spreadsheet into a Word document. You get the benefits of Excel’s functions, but within your Word doc. Much nicer than using a basic table.

    This is under the Insert tab.
    Click Table > choose Excel Spreadsheet

    1. Koko*

      I can’t tell you how much I loathe tables in Word. Every time they can’t do something Excel can (like Equal Column Width) I get frustrated.

      2. Emily, admin extraordinaire*

        You can use Distribute Columns to automatically size the columns equally. (Distribute Rows works the same for rows, obviously). The commands are under the Cell Size area of the Table Layout tab in the ribbon.

      4. NotAnotherManager!*

        I recently put forth the idea that Word tables were awful and there was no reason to use them, absent very narrow circumstances. In legal, people love Word and hate Excel, but their tables (exhibits, designations, etc.) would be so much better and easier in Excel. Sort, filter, pivot…. and then paste it into Word if you absolutely must.

        1. Cloud Nine Sandra*

          If I can add one of those narrow circumstances: screen readers love tables in word, so for people with visual disabilities it’s the opposite of useless, it’s the easiest way to read data like that.

    2. irene*

      AH! THAT’S WHAT’S HAPPENING!

      Okay so there’s this weird bug where if you print from Excel to PDF, your nice formatting on charts and tables – thing grey borders or whatever? – gets thrown out of wack. BUT if you create the same table/chart in Word, it’s fine.

      For readability purposes, some of my reports to the VPs need to have lines between the rows, but just a standard thin black line. When it prints to PDF, it’s indistinguishable from the “thick black border” mostly, but they actually vary in width.

      So I started building the tables in Word and copying the data into them – only there’s a huge mess with how that happens and having to reformat. I carefully created templates for both sides and set up the Excel side so that when I copied it over, it wouldn’t override with any weird formatting (the Accounting style for numbers doesn’t work well, for example).

      Anyway, last week I noticed that my latest monthly reports were still coming out with irregularly sized borders and I couldn’t figure out what was going on. But if I’m accidentally doing something to embed the Excel stuff instead when I copy over data/charts, that might be the answer. I’m going to have to take some time to research it next week!

  33. Jubilance*

    I just learned how to wrap my VLOOKUP with an IFERROR statement to prevent getting those stupid #NA responses – no more using Find+Replace to get rid of them!

    Mike C already mentioned this, but the Ctrl+arrow buttons to highlight entire sections is a gold mine when you’re working with a lot of data.

    Also due to the data I work with, I use the CONCATENATE, LEFT, RIGHT, and MID functions in Excel to either combine things, or strip out values from another cell.

    1. Koko*

      I love ISERROR and MAX. I use this formula to calculate the difference between the current month’s revenue goal (C15) and the current month’s actual revenue (the Pivot data call) without going into negative numbers once we’ve reached the goal and to display the full monthly goal on the first day of the month when there isn’t revenue yet, instead of an N/A error message (which then allows other cells that rely on having the “$ to go” field containing an actual number to not break down).

      =IF(ISERROR(GETPIVOTDATA(“Amount”,$A$3,”Quarter”,3,”Month”,6)),C15,MAX(0,(C15-GETPIVOTDATA(“Amount”,$A$3,”Quarter”,3,”Month”,6))))

      1. Koko*

        Basically the above says:

        Check to see if revenue (pivot data) is valid. If the pivot call returns an error, display the revenue goal (C15). If it doesn’t return an error, subtract revenue (pivot data) from goal (C15). Output the larger of that number or 0.

      1. Jadelyn*

        IFERROR is a lifesaver – if you’re distributing sheets to people and they’ll be filling in certain cells, but you have other cells with formulas waiting to calculate based on those fillable cells, IFERROR keeps it from showing error messages when they’re partway through entering their data. It’s easier to use IFERROR to suppress the error messages, than to explain to people that “no, it’s just because it’s only seeing partially-complete data for the formula, just keep entering and it’ll fix itself once you’ve got everything in there”.

        For example, I have a salary range calculator that relies on IFERROR along with some index/match fun. Without the IFERROR, when someone chooses a department from the dropdown, but they haven’t yet chosen a job title or region (which they couldn’t, because the title dropdown populates based on the contents of the department cell), the range calculation cells start throwing errors, and it freaks people out. I use IFERROR to suppress that so that the cell stays blank until they’ve chosen all three fields (dept, title, region), at which point it can calculate the appropriate salary range and show them that.

    3. irene*

      I’ve been using ISERROR and IFERROR around my VLookups for a while, but this week I learned how to use ISNA!

      =if(isna(VLOOKUPblahblah)),”Error message”,”Good message”) – if I don’t need to actually know the Vlookup value and am using it to help sort or filter, this is a big help. Sometimes I get confused the next day “why is the key listed twice?” but “VALID KEY” is much faster to remember what i was doing. (I have a lot of projects where coworkers send me partial data that i have to then compare to my DB, check for errors, or other things where I might not have a full/valid dataset on either side.)

      A lot of times, I used IFERROR or IF(ISERROR(vlookup),””,vlookup) depending on what I was doing with the data, and using the IF(ISNA) will give me just one formula that’s always the same. Unless I want the actual value, of course.

  34. special snowflake*

    Is there a way to highlight duplicates even without removing them? I spend a lot of time de-duplicating datasets of addresses. So I remove all the duplicates with the button for it (my favorite tool) but that only takes care of exact matches. Not someone put Jim in one donation and James in another and so now he shows up twice even though he’s the same person. (the larger issue of cleaning out the database is just that – a larger issue)
    I feel like there’s a way to cut down on the amount of scrolling line by line through 20,000 rows of addresses and I’m missing it.

    1. LBK*

      Sometimes I just use find + replace to help sanitize my data for common formatting mismatches, although you obviously have to be careful with it. So, for instance, when cleaning addresses I’ll F&R for ” ste ” to ” Suite “, ” ln” to ” Lane “, and so on. Always put spaces around it so that you don’t accidentally pick them up in the middle of other words (eg accidentally inserting “Suite” into the middle of “Gloucester Road”. You could do something similar with replacing ” jim ” with ” James “. But I agree that nicknames are the bane of my existence when it comes to maintaining customer data.

    2. Elsajeni*

      You can select the column you want to find duplicates in and hit Conditional Formatting -> Highlight Cells Rules -> Duplicate Values, but it’ll still only catch exact matches.

    4. J.*

      It sounds like you could use conditional formatting. Select the column where you’re most likely to actually find the duplicate (name, address, whatever), and conditional format to change the background and text color if it’s a duplicate. Then use Filter > Filter by color. It will hide all of the ones that are unique and only show the highlighted ones.

      If you worry that you’ll never flag dupes that way (ex: Jim vs James AND it’s missing a comma in the first address), you can break out part of the name or part of the address (ex: =RIGHT(A2,LEN(A2)-FIND(” “,A2)) will give you everything after the first space if the name is smushed into one field) and do the conditional formatting on that field. You might get some false duplicates with Jane Brown and James Brown, but since you’re only highlighting and not deleting, it’s not a huge deal.

      2. eee*

        advanced level: do the conditional formatting for duplicates, but use it in conjunction with concatenate. For example, sometimes i’ll do something like last name + job title + organization. Duplicates of just one of these things are pretty useless–there are many Browns in the world, many nurses, many people who work at Kaiser. But it’s useful to check how many Browns who are nurses and work at Kaiser there are in the data! I find last name often gives me better luck than first name when using in concatenation. Also, if your data is messy, it might be good to put it through a few steps before concatenating/checking for duplicates. Luckily Excel is case-insensitive, so it knows that jim and Jim are the same, but it doesn’t know that Bean-Withers and Bean Withers are probably the same. i usually copy the column that I want to examine, paste it, and then set that column to be 1. Trimmed (so leading/trailing spaces don’t throw it off), 2. Replace any Dashes or Spaces with nothing, so Bean-Withers, Bean Withers, and BeanWithers will all appear as BeanWithers. Then I use the formatted column to check for duplicates with conditional formatting, and then can refer back to the original column to double check that the duplicate is real.

        1. J.*

          Yes, great addition!

          With addresses, I like to concatenate last name with the first handful of letters of the address. This is helpful if the same person is on there twice and one time they gave their apartment number but the other time they didn’t. So =LEFT(A2, 10) will give you just the first 10 characters of the field, which when combined with the last name will be a useful identifier.

          I also like to do a find for two spaces and replace with one space, to standardize typing errors.

          1. irene*

            Oh, that’s a clever way to check for name/address dupes!

            I do a lot of highlighting duplicate names in different columns, then sorting the address field alphabetically. It’s not the most efficient way and relies on my eye catching on similar lines of text. But when you have a giant office tower with 500 employees for only one of the businesses there, you end up with a lot of variations on “1 Main St Floor X Office Y” kinds of things.

            I’m going to have to see if your concat trick helps. One of my coworkers several years ago did something similar, and I saved the spreadsheet to examine, but i’ve never been able to entirely figure out if her method (using “find” and “match” I think?) actually worked any better.

    5. Jessie*

      Conditional formatting – Highlight Cell Rules – Duplicate Values :)
      It will only highlight exact matches though, so you’d have to do it in the last name column to capture all of the Jim/James issues.

    7. diaphanous*

      I had to do this years ago. I don’t think Excel can do it natively, but I remember finding a plug in that would allow you to set up synonyms or aliases and search/consolidate that way.

    8. Wren*

      Yes, conditional formatting. Highlight the column you want to check, Home > Styles > Conditional Formatting > Highlight Cells Rules > Duplicate Values. You can then filter by the color of the cells to see just the ones that match another cell.

    9. DataPerson*

      Conditional Formatting, my friend.

      Highlight the column(s) you want to examine and select conditional formatting>highlight cell rules>duplicate values.

      You can make it more or less fancy, but the default will color duplicate cells red. You can even filter by the color in the column to move them all together.

    10. special snowflake*

      Thank you all!! This is really helpful – and I’m now off to practice conditional formatting :)

    11. Pretty sure I know Excel*

      One way would be to add a “helper” column concatenating the columns you think might be duplicated, in the simple +A1&B1&C1&D1… no need for spaces. Double-click the fill handle to fill that down the column.
      Then, use conditional formatting on that helper column to highlight duplicates.

  36. MaureenS*

    Excel – Autofilter. So many options to sort your data. Filter to show just cells starting with “A”, or >10 or whatever. Pivot tables are also amazing if you need to sort and condense data, it’s a step up from sumifs() and countifs().
    Word – Show/Hide (aka Reveal Codes). It’s the weird backwards P-shaped button currently in the Paragraph area of the Home ribbon. So you can see where some ‘helpful’ person used multiple spaces to center a line of text, etc. Also see where the page or column breaks actually are. Proper use of styles & headings.
    Both – Seak and Destroy (aka Replace) – In Word, you can remove those pesky multiple tabs or returns using Seak and Destroy special characters. ^t = tab, ^p = return, etc. So if you Seak ^p^p and replace with ^p, run that a few times, now you have a document with only ONE return per paragraph. Make re-formatting soooo much easier.

    1. Aska*

      You can also search and replace (seek and destroy, I love it) formatting, fonts etc. I’ve used it sooo much to clean up documents (our company’s preferred font gets randomly (?) reverted to Calibri a lot). The Format and Special menus have been gold mines for fixing messy documents quickly. (As an indirect result, I’m now so used to reading documents with Show formatting on that I think they look odd with it turned off…)

  37. Jaguar*

    Whenever I’m asked about “tricks” in Excel, I always respond, “the hidden flight simulator!” However, I only ever used the flight simulator once (as I imagine is the case for most people), so I did some Googling before posting it here to see how to even get to it.

    Turns out it was in Excel 97. I guess I have to stop bringing it up.

  39. The Cosmic Avenger*

    OK, my list:

    – Word Styles. You make all section headers Heading 1, and then if you want to change it, you can change ALL of them by updating your H1 style. It also is MUCH faster than changing the font size, weight, alignment, and spacing every time you create a header. Also, it’s very easy to create a custom H1 header, then right-click on H1 in the Styles panel, choose “Update Heading 1 to Match Selection”, and poof! Your custom style is now the H1 for that document!

    – Autofit row height/column width (Excel). Under Format in the 2016 ribbon.

    – For any program, my favorite trick is Find and Replace. I clean up a lot of documents, and that’s probably saved me YEARS of work. The trick is to plan it out carefully, and then if you’re not sure or if you’re replacing something simple, short, or common, to do it one by one (keep clicking Replace, don’t use Replace All), and be ready to Undo.

      1. Emily, admin extraordinaire*

        You can also change the radio button in the Modify Style dialog box (right-click on a style and select Modify) from “Only in this document” to “New documents based on this template” and it will make your new style the default style (it saves the change to the Normal.dotm template).

    2. Chameleon*

      The beauty of using the Word Styles is that you can automatically generate a table of contents. Likewise, using captions for figures allows you to generate a table of figures.

      This was soooo nice when I was writing my dissertation because I didn’t have to hunt down page numbers for everything, and it could re-populate when I had to make revisions.

    3. Katelyn*

      This also greatly improves document accessibility for people who rely on screen readers. If you use semantic styles, the screen reader can scan from heading-to-heading so the person can easily find a specific section – if you just use font size / bold text, the screen reader has no way of knowing what’s a heading.

  40. Amber Rose*

    I inherited a form in Excel for taking meeting minutes (insert swearing).
    Somehow, this person managed to enter new lines within a single cell. I have no idea how they did this. Does anyone have any ideas? Enter obviously doesn’t work.

      2. JulieBulie*

        Eh, I worked at a place (back office of a bank) where they did EVERYTHING in Excel. (Without necessarily leveraging all of Excel’s features. Like they would have multiple blobs of data on a single sheet, but wouldn’t make them into tables, therefore you couldn’t sort/filter any of them. Easy to fix I know, but weird IMO. Then again perhaps they knew something I didn’t.)

        Those people couldn’t have done so much as a grocery shopping list in Word. If something looked like a nail, they would hit it with their Excel hammer.

    5. Queen of Cans & Jars*

      OMG, I have a coworker who will not use Word for ANYTHING, so has created agendas in Excel that I’ve been gradually converting to Word because I’m the one who has to take notes in them.

      1. Amber Rose*

        Sounds like my predecessor. Absolutely everything, even some of the manuals, are in Excel.
        One day when I have extra time I’ll probably work on converting them. Right now I’m trying to condense the worst ones. There’s no reason to have a separate Excel sheet for every employee’s training records.

    6. Teapot Librarian*

      I have the opposite, which is an inherited spreadsheet
      with paragraphs
      of text broken
      across multiple cells
      like this.

        1. Teapot Librarian*

          That is an accurate statement. Even worse, though, is that I reformatted the entire thing, sent it to the person who updates it telling him to use the new version, and then HE KEPT USING THE OLD ONE. But he adds things in the middle, not always at the bottom, so fixing it takes more than just adding the newest rows at the end to the new spreadsheet.

        1. Teapot Librarian*

          I reformatted it manually. There were reasons why concatenate wasn’t going to work. I think it was because it was rows and not columns.

          1. LCL*

            I had to do this with a number of legacy forms. The person who wrote them was more comfortable with word. Earlier versions of word were much easier to write in than Excel. I could never convince him a list of words is no different from a list of numbers, and Excel is aces at lists.

          2. eee*

            you can actually do this, if you ever need to do so in the future! I believe you do =Concatenate(Transpose([the column you want concatenated]))
            once you have that entered, you select the text of Transpose([the column you want concatenated]) and press F9. This will turn “Transpose” etc into the actual text of the cells you selected. I think doing this leaves curly brackets in the formula, delete those and be on your way.

            It’s a few too steps for me to use regularly, since i’m usually using this to work on say 10 or so things i usually just copy the text, paste->transpose it, and then concatenate it, but it’s good when you need to do it for a huge number of things!

          3. JulieBulie*

            Could merge the cells, copy the text, unmerge the cells, paste the text back into a single cell?

              1. JulieBulie*

                Oh crap, you’re right!! That’s how it works in Word, but I forgot that it doesn’t work that way in Excel! (I’m remembering now what a pain this is.) Sorry!

      3. Miss Pantalones en Fuego (formerly Floundering Mander)*

        I usually give up and use a text editor for this kind of thing. Copy and paste the cells that are supposed to be one line into something else that can find + replace line breaks, delete them, and then paste it back into the cell it’s supposed to be in.

        My personal favorite is a program called Notepad++ in which you can highlight all the lines you want to join and hit ctrl + J. I think it would also work if you paste the text into Word and then removed all the paragraph breaks.

  41. paul*

    Is there a way to turn off Word’s god-awful autoformatting? That’s what I want…Excel’s OK, but I really hate Word.

    1. The Cosmic Avenger*

      In 2016 it’s File>Options>Proofing>AutoCorrect Options. You can turn off just the ones you don’t like, or just turn off “Replace text as you type” altogether!

    2. Mockingjay*

      Change the paragraph and font defaults. Click to expand the popup, change settings, and then click Set As Default. Word will ask if you want to save to Normal.dot. Okay.

      You’ll have to do this periodically; Office updates tend to reset the Normal template for some reason. Another Office Quirk.

      1. paul*

        I do that and when I launch word again it seems like I’m still getting those damned extra lines and breaks and whatnot. But I only use word much every few months so it may be that random reset you’re talking about.

        1. Mockingjay*

          Word (and Office products in general) have a nasty habit of carrying over old style codes from documents, especially if you do what we all do, open an old document, resave with new name, and start editing from there. The Style Codes in Word never go away unless you delete them from the document.

          *Note: the following tip is for fairly experienced Word users accustomed to Style Codes.*
          Open the Style bar and dock it to the side of your screen. At the bottom are three little icons. Select Manage Styles. In the popup, click Import/Export. In the next popup, the column on the left shows the styles in the document. The column on the right shows styles in the Normal.dotm. Ignore the right. In the left column, select the codes which don’t belong (usually custom styles created by users) and click Delete. (Note: Word won’t allow you to delete hard coded styles that form the backbone of the normal template, so don’t worry about messing those up.) When finished, click Close. You may have to fix a few things in your document text because the old style code was removed. Just apply the correct Style Code from the remaining list.

          1. Dunnewriting*

            THANK YOU for this tip, I get some messy documents from a lot of coworkers that I need to clean up and this will save me loads of time.

  42. Hey_Sue*

    I love CONCATENATE. Our account coding system had 4 groups of numbers (13 numbers total) that had to be ordered one way on our monthly P-card reports and another way for the online reporting system (plus a leading “0” for good measure I guess). I would enter the numbers once (in 4 different data fields for the print report), and use concatenate to automatically reorganize them for the online system, plus add the leading zero, then just copy + paste them into the single field online.

    1. Goya*

      CONCATENATE is amazeballs. I don’t use it often in my current position – but the previous one, it was a daily occurrence.

      1. Sarah in DC*

        There is VBA out there that you can use to make a concatenate if function which is amazing and got me Excel god status at my last job.

  43. Alana*

    In Word:
    Ctl+Shift+A will change selected text to all caps, or all caps to lower case.
    Alt+A will allow you to make a vertical selection of text. This is super helpful if you are trying to copy data out of a scanned table.

    1. Not a Real Giraffe*

      Shift+F3 will adjust capitalization, too, along with changing everything to have just the first letter of each word capitalized.

      1. Alana*

        Thanks! I knew there was another key combo that did this as well, but of course couldn’t remember it today :-/

  44. LondonEngineer*

    One thing I recently found that was useful, when you have a really wide/tall sheet and you want to view columns/rows that are far apart at the same time, but you don’t just want to hide the stuff in between and freezeing only works on one area at a time: Next to the freeze panes button there is a ‘split’ option which allows you to set up areas which scroll separately.

    1. Agatha_31*

      I’m in the market for a job where I can work from home and make $1,000.00 a day. What’s the keyboard shortcut for that?

  46. Sarah in DC*

    Watch window on the formulas is one of my favorite secret excel tricks. It pins the contents of a cell to the top of your screen so if you have a formula on one tab and the inputs to the formula are on another you can see how it changes without toggling back and forth.

    Trace precedents or dependents is also great if you are trying to figure what changing a specific cell will do to your file.

      1. Sarah in DC*

        Both are on the formulas tab in the formula auditing section. Watch window isn’t available on Macs though, not sure about tracing precedents and dependents.

  47. Mabel*

    I have a couple of favorites:

    In Word, you can click the Special button in the Find and Replace (CTRL+H on PC) dialog box to be able to find paragraph marks, tabs, line breaks, graphics, etc. It is VERY handy when I want to replace all line breaks with paragraph marks or delete all graphics (after I have pasted something copied from a web page – there are always extraneous/hidden graphics that come along).

    In Excel, you can use Go To Special (CTRL+G, then click Special on PC) to select all objects, blanks, comments, visible cells only, etc. I use Go To Special to select visible cells only when I have selected non-contiguous cells (this is done by holding down CTRL and clicking on cells), and I want to do the same thing to all of them (bold, delete, change font color, etc.).

  48. nnn*

    Not quite Word, but incredibly useful if you have to upload or attach files, especially if your files are saved on drives with complex file structures.

    1. In Windows Explorer, highlight the file in question and Ctrl + C (or right-click and select Copy)
    2. Put your cursor in the File Name field in the upload/attachment window, and Ctrl + V (or right-click and select Paste).

    It will instantly insert the entire file path. No more navigating to just the right folder every time you need to upload!

  49. SCtoDC*

    I am very excited about this thread.

    Excel Tips-
    F2 to type in a cell without having to double click
    Copying data and using transpose in order to go from row to column or column to row

    Word Tip-
    To go from all uppercase to sentence case or vice versa, highlight text and hit Shift F3

  50. Lora*

    Goal Seek and Solver. I actually prefer to use Excel (well, OpenOffice Calc) to build the simpler process models for these features alone. My very first ChemEng prof (who recently retired, too bad for legions of students) showed me how to use Goal Seek and Solver to do equipment sizing and it has been hands down the most useful thing. Equipment sizing means the difference between a multi-million $$ shutdown and $2M/month water bills from the city municipal supply vs. everything running absolutely peachy with nothing but planned maintenance and overhead matching predictions within 10%.

  51. Althea*

    I make a lot of multi-tab spreadsheets, which typically feed up to summarized reports and so forth. Then the summarized versions have to be submitted to various parties like donors.

    I used to copy/paste values in order to break formulas so I could delete the details. What a PITA.

    The best way to do this that I’ve found is to move all the tabs you want to delete to a new workbook entirely. Then go to Data/Connections/Edit links and break the linked formulas between the two workbooks. This converts all linked formulas to values. No #REFs ever. And it takes about a 30 seconds and is error-free.

    1. Elizabeth*

      I’m also a big fan of locking individual sheets and then hiding them, so that they’re still there if need be but no one can see them and then accidentally mess with them.

      1. paul*

        Oh jeez I feel silly. I’ve known about those features but it never occured to me to use them to make my spreadsheets I send to our board look nicer/be less easy to break. Thank you so much, this will actually help me day to day.

      3. Jadelyn*

        Pretty much any fancy spreadsheet I create for use by other people has at least one hidden sheet in there. Hidden sheets are amazing – you can have “helper cells” that process the first part of a calculation and then your main sheet refs the helper cell to finish the calculation (helps keep formulas a manageable length sometimes), you can have data validation lists, raw data that the recipient isn’t supposed to see but you don’t necessarily want to completely delete it… and, once you hide the sheets, you can lock the workbook structure so that people can’t unhide them, although that’s usually not necessary, most people don’t know to look for hidden sheets anyway.

      4. J.*

        Me, too. Whenever I get a really big, messy file with a lot of data that needs to be cleaned up, I duplicate the tab then lock and hide the original so that if anything gets borked during the cleanup, I have the original reference point.

  52. The Cosmic Avenger*

    Oh! Also, viewing the formatting marks is incredibly helpful when you’re either fixing someone else’s document, or your document isn’t doing what you expect it to do (line wrapping, etc.). File>Options>Display>Show all formatting marks.

    1. Emily, admin extraordinaire*

      You can also click on the pilcrow (¶) button in the Paragraph pod on the Home tab of the ribbon to turn them on. I have them on by default, mainly because I spend a lot of time cleaning up formatting of other people’s documents.

      2. Solidus Pilcrow*

        Related, you can toggle table gridlines on and off, too (for when the borders are not set/visible). It’s on the Table Tools > Layout toolbar, View Gridlines.

    2. Solidus Pilcrow*

      I almost always have formatting marks on when working in Word. (Check my user name/avatar if you don’t believe me :) ) The only times I turn them off is for previewing final formatting or if I’m just reading text (not editing/formatting).

  53. Elizabeth*

    Named ranges. If you use formulas at all, you should be naming ranges (where ranges could be a single cell, a column or row, or a full table of data). It makes your formulas so much neater when you’re looking at something like “=VLOOKUP(C2,Clients,4,0)” as opposed to “=VLOOKUP(C2,A1:T638,4,0)”. You can tell at a glance what kind of data is being referred to just by looking at the formula, which is super helpful if you’ve got tons of formulas in a given sheet or workbook.

  54. Rae*

    That when you highlight multiple cells, the count and sum are displayed in the bottom right-hand corner. People always seem amazed when I show them.

    2. WisdomW*

      Right clicking the section with the count will also allow you to select “Distinct Count” and other measures, if you just want a quick calculation.

    5. LQ*

      Yes! People think I’m brilliant because I can add numbers they’ve highlighted quickly. It’s just on the bar in the bottom.

  55. Wannabe Disney Princess*

    I know this isn’t Excel or Word, but it’s for Outlook.

    If you use Conditional Formatting (under View, and then View Settings) you can make the email in your inbox come in in different colors depending on sender, keywords, subject, etc. For example, any teapot order that needs to go out overnight, I have come in bold red so it stands out.

    3. Christine*

      Ha, yeah, I used this with a micromanaging ex-boss. He wanted replies to every. single. one. of his emails, even if it was just an acknowledgement of “yes, I’ve read this.” (He didn’t know about the “read receipt” feature, as far as I could tell.) So I made his emails show up bold, larger font, and bright red. Never missed one again.

  56. Koko*

    Also, the Month() and Day() functions can be really helpful for making your data pivot-table friendly if you a timestamp field but want to be able to group all datapoints from the same day or month in a pivot table. Just create new columns for Month and Day that calculate off the Timestamp field, and then every row with a timestamp at any point in March will say March in that column.

    2. Aardvark*

      You can also group values in pivot tables (provided they’re all the same type of value–so if you have blanks it sometimes won’t do this). Right-click on the row labels and choose “Group…” If it’s groupable (numbers, dates, …???) then you’ll be able to specify the minimum, maximum, and range. Great for presenting data with a very long tail, or a bunch of decimals that can all be grouped together into a range.

  57. NatKat*

    First a tip: Surprisingly I found that people don’t know in Excel if you highlight the entire sheet by clicking the top leftmost square and then double click the first column it will auto fit the entire sheet, instead of doing it one column at a time.

    Second a request: Everyone raves about V lookup, but I just can’t get the hang of it and I consider myself pretty tech savvy. Does anyone have a guide that would be helpful or maybe devices that helped you remember?

    1. Koko*

      I actually prefer to use Match and Index together in most cases instead of VLOOKUP. I have a sticky note on my desktop with this cheat guide:

      =INDEX(Column I want a return value from,MATCH(Lookup Value Cell,Column I want to Lookup against,0))

      So if I want to find the contents of cell M23 in column A and spit out the contents of the cell in column B adjacent to the matching cell in column A:

      =INDEX(B:B,MATCH(M23,A:A,0))

      1. WisdomW*

        Throwing UPPER() or LOWER() around any string comparisons you’re doing will normalise the columns you’re checking against each other, so that “Steve” and “steve” will show up as a match, if you want that to happen.

        VLOOKUP also doesn’t work with Numbers Stored as Text when checking against columns that Excel knows are numbers, be sure to check that your columns are formatted correctly.

    2. Mel*

      My tip for VLOOKUPs (or HLOOKUPS, for that matter): use INDEX/MATCH instead. It takes a minute to learn, but it’s so much easier and faster and has fewer limitations (like how in a LOOKUP the data you’re searching for has to be in the 1st column of your array).

      But, if you’re set on LOOKUPS, consider this every time you type one in:
      -what are you looking up (this is the first cell of your formula)
      -where do you want to search for it (this is the second data set/array in the formula)
      -what column/row should I pull it from (this is the third cell and should be a number)
      -do you want the exact match (if so, enter false) if you enter true here, it will pull the data from the first match it sees, but if you copy your LOOKUP to another cell, it will just pull the next cell’s info from your array.

      LOOKUPS are great but they are really limited in comparison to INDEX/MATCH functions. Google them and give them a try!

    4. Little Twelvetoes*

      I concur that INDEX/MATCH is the way to. I’ve got a workbook with explanations and samples that people can play with. But I’ve never shared it with the outside world.

      If someone’s got a good idea for a way to share it, let me know.

      1. Perpetua*

        You could upload it to Google Drive (if you use it), or any other file hosting site, then share the link here in your username (under Website(Optional), when you start leaving a comment)?

        A workbook sounds great, I’d love to learn more about INDEX/MATCH!

        1. Little Twelvetoes*

          Well, that makes sense! I’ll give it a try with DropBox:
          https://www.dropbox.com/s/qdaeva7rr6rvr74/INDEX%28MATCH%28%28%29.xltx?dl=0
          (same link as the one in the user name)
          Hopefully, it works.

          In the workbook (which I created as template), the first few tabs try to explain first how MATCH works and then how INDEX works. Then finally, another tab explains how MATCH nests inside INDEX. The last tab has some data in a table and sample “exercises” for creating a formula with them.

  58. WisdomW*

    A tip for handling CSVs, which is a common occurrence when using Excel, is that the “comma” in CSV files is actually a regional setting, accessed via Control Panel\Clock, Language and Region\Region\Change date, time or number formats\Additional Settings, under list separator, changing this will change the separator in all of the CSV files in your system, useful if you want to use “Text to Columns” on any data what would have commas in them usually.

    Using this setting to quickly flick between separators allows you to handle whatever gibberish people send you.

  59. Red Reader*

    F4 in Windows!Excel = “repeat that same action I just did.”

    Command-Y in Mac!Excel = F4 in Windows!Excel.

  60. Mel*

    Excel:
    You can sum your column or your row by going to the end of the data in the next blank cell and on your keyboard hit “Alt + =” It’s a great trick that saves a surprising amount of time.

  61. gingerbird*

    Another one if you put a ‘ infront of a number you are typing in excel, it will format it as text. Great for when you just want to have to type in one account number without having to use text to collumns.

  62. nnn*

    Word question: Does anyone know how to remove a word from the spellcheck dictionary?

    There are some frequent typos I make that result real dictionary words, but are not the word I intended. I would love to make red squiggles appear under them to indicate to myself that I’ve made a typo.

    1. Teapot Librarian*

      Your frequent typos you can add to autocorrect, assuming at least that the incorrect word isn’t something that you also use regularly.

    2. Mockingjay*

      File tab > Options > Proofing. In Proofing, click Custom Dictionaries. In the popup, top right, click Edit Word List…
      Scroll through the list and select and delete.

      1. nnn*

        This is weird…when I click on Edit Word List, the only words in the list are those I have added to the dictionary (people’s names, etc., that I didn’t want to get red squiggles). There are no regular dictionary words in there

        1. Mockingjay*

          Yeah, you can’t edit the master Word list that Microsoft provides. Word’s grammar tool can’t distinguish actual sentence sense. It can only look for programmed patterns, not context. I have the same problem.

          1. BlueFairy*

            No, you can remove words from spellcheck! There’s a simple text file on your computer that you can add words to that you want spellcheck to mark as wrong. For example, I always want files on my work computer to mark “stand-alone” as wrong because we only use “standalone.” So I open up this file and type each “wrong” word on a separate line.

            On Windows 7, it’s in C:\Users\YOURUSERNAME\AppData\Roaming\Microsoft\UProof, choose the one for your language – “American English” is EM0409. Google Word Exclusion Dictionary for more info/help.

    3. Solidus Pilcrow*

      You’re probably looking for the exclusions dictionary. For example, you can add the word “pubic” as always misspelled so you don’t make the mistake of referring to the Department of Pubic Works.

      When I worked in financial processing, I once had “asses” in a doc instead of “assess.” It was caught before final release, but it made me look for a solution.

      Google “exclusions dictionary” to find where the file lives for your version of Word.

  63. Greg M.*

    So Excel does one quick thing that is nice now and then. a lot of programs will just print the left most page of something that goes off way to the right. Excel will chop it up and print the whol thing. Want a picture printed spread across multiple pages? drop it in excel, size it and hit print.

    Word can manage sources and tables of contents for you, it’s so useful.

    And finally the thing I use most often with Excel is Notepad. I’m a programmer and there’s times where I have very similar lines of text to either change to get something out of or put into an xml list. if you copy text into excel it treats tabs as being a different cell. in notepad you can find and replace with tabs.
    for example: you have a list of things in brackets, well find and replace the brackets with tabs and paste into excel. suddenly it’s all in separate tabs.

    or if you need to add brackets to everything on a list then put the list in column B and a brack in A and C and fill down then paste to notepad and find and replace out the tabs.

    now to do this you have type a tab in notepad, highlight it and copy it and paste it in the search box. if you just press tab in the search box it doesn’t work.

    2. J.*

      If you want a picture printed across multiple pages, you can also save the image as a pdf and when you go to print it in Adobe Acrobat, select the Poster button under the “Page Sizes and Handling” section of the print dialog box. You can fiddle with the percentage resolution to figure out how many pages you want to print it on (they’ll show you with dotted lines in the preview image where the page breaks are), and can even set the margin in between pages if your printer has weird border limitations, so that you can manually line it up right once you have the printed pages.

  64. Teapot Librarian*

    Adding personal auto-corrects. In law school I used them to save time typing defendant, appellant, and the like while taking notes. Now that I work in the public sector, the first thing I do when I get a new computer is add “pubic” –> “public” in the autocorrect. There is next to no chance in my subject area that I’ll ever actually mean “pubic.” And UNDO (c) to the copyright symbol.

    Also, the keyboard shortcut for comments in Word. ctrl-alt-m

    1. Greg M.*

      this is also a great thing for abbreviations. you have a long name you need to write a lot? add a shortform to autocorrect.

    2. cornflower blue*

      I became a department hero with this trick at an old company, when I discovered that they had been putting out documents copyrighted in the “Untied States of America” for over a year.

    3. Always Check Your Spelling*

      This is brilliant! I work at a company with “Poppy” in the name, and an employee recently sent out an email to every department (including the Board of Directors!) with the subject line, “Poopy” XYZ. It was hilarious and cringe-worthy!

  65. straws*

    This isn’t related to Excel functions (most of those tricks are mentioned already), but if I’m modifying a sheet, I always do 2 things:
    1) Add a column called “Order” and auto-fill with numbers, so I can always restore the data to the original order if something gets out of sync.
    2) After adding the column, but before modifying anything else, I copy the sheet and rename the first one “original”. This gives me a fallback if something goes terribly wrong, but it also lets me restore pieces of data using VLookup if I remove a column or get the sorting out sync.
    I don’t always have to rely on these steps, but on the occasions that I do need to use them, it saves so much time!

    1. Robin Sparkles*

      I do the same thing -the numbering system for original order and saving the original sheet!

    2. Miss Pantalones en Fuego (formerly Floundering Mander)*

      Hah, the auto-numbered column was going to be my tip!

      I find that I use it a lot when I have blank lines. I know that there are various ways you can solve that problem with a formula, but I find it quicker to insert an auto-numbered column, sort the spreadsheet in such a way that all the blank lines are together, delete them all, and then re-sort.

  67. Mockingjay*

    My favorite Word trick: Creating templates as .DOTX files.

    Start with a blank Word doc. Define or modify Style Codes to set up headings, pagination, font and paragraph formatting, margins, etc.

    When everything is the way you want it, go to File, Save As and select Save as type: Word Template (.dotx).

    When a user clicks this file, it automatically opens into a clean, unnamed Word document with all Style Codes preset.

    This has saved me SO much effort.

  68. J.B.*

    In word: show paragraph markings. This way you can sort through all the lines vs page breaks.
    Use tabs, not spaces, to align text.
    Use page or section breaks to send text to the next page.
    If you want one page with a different layout than the rest, make a section break and set the layout there.
    Headers and footers – same as previous is the default, if you want different ones on different pages make a section break and then unselect same as previous.

  69. LBK*

    Here are my three cheat sheet formulas that I keep in a Notepad doc:

    Convert “Last, First” to “First Last”
    =right(A1,len(A1)-search(“,”,A1)-1)&” “&left(A1,search(“,”,A1)-1)

    (where A1 should be whatever cell the name you want to convert is in)

    Convert “First Last” to “Last, First”
    =right(A1,len(A1)-search(” “,A1))&”, “&left(A1,search(” “,A1)-1)

    Case-Sensitive “VLOOKUP” equivalent
    This is a lifesaver if you use Salesforce since the system-generated IDs reuse the same ID just with upper case vs lower case letters, which a VLOOKUP can’t tell the difference between
    =index([1],match(true,exact([2],[3]),0))

    Roughly maps to these items in a VLOOKUP…

    =vlookup([2],[3],[1],0)

    [1] = column containing data you are trying to pull in (just select the actual column rather than the column number like a VLOOKUP)
    [2] = cell you are trying to match on
    [3] = column of data you are trying to match [2] against (should be a single column, unlike VLOOKUP where whole array is selected)

    ***NOTE: this is an array formula so you need to hit Ctrl+Shift+Enter instead of just Enter after entering the formula to make it calculate correctly

  70. cold brew raktajino*

    Using ampersand to search on multiple criteria, such as in an index match or vlookup situation. =index(‘oldtab’!C:C,match(A2&B2,’oldtab’!A:A&’oldtab’!B:B)) Then hit ctrl-shift-enter to use the formula as an array.

    Our usual use case:
    Say I have a spreadsheet with columns: A = First Name; B = Last Name; C = City; D = Expenditures. In another tab, I have a subset of the names and want to pull over the cities too. However, I can’t just look up using first name OR just last name, because there are so many common names. An obvious move would be to just concatenate the first and last names. However, that doesn’t always fit your needs, and this is more flexible.

  71. Emily*

    You can create interactive dashboards via Pivot Tables, charts on top of pivot tables, and the slicer functionality. It doesn’t get you all the way to something like Tableau, but it’s impressive.

    This is also functionality that you’re not going to be able to reproduce by learning a few lines of code. (This is in contrast to data management tools like match/index, which are fabulously useful relative to not having them, but also where if you use them a lot, especially with big data sets, I’d suggest thinking about learning to do in a command-line framework.)

  72. Kalen*

    One thing I use a lot for troubleshooting formulas is the the Evaluate Formula tool. It basically allows you to step through the calculations the system is doing on a formula, so you can potentially see where an error is happening or your math or logic is not working how you expected it to. It can’t cover everything – like what it shows for a sumif(s) or a vlookup is just the result, so there’s not a lot of additional insight you get into errors related to that. But if you have a formula with, say, a couple vlookups, you can see which one is returning an error and throwing the rest of the chain off.

    3. Jadelyn*

      Gods, yes. It’s so helpful when you’re trying to zero in on an error and you can’t figure out what you did wrong.

  73. Xarcady*

    Two little Word tips:

    1. In a table, if you tab too far, Shift + Tab will take you back.

    2. If you are trying to position text in various places on the page, for example, your name and contact info on a resume or spacing page number, title and chapter across a footer, and using columns isn’t going to help much, try creating a table, and manipulating the cells to put the text where you need it. Then hide all the lines in the table. You can center or left-or-right justify text in the cells and align the text top, center or bottom, as well as adjusting the size of the cells. I use a lot of one-row, 2-to-3 column tables in headers and footers, rather than using tabs, and it keeps the text where I want it.

  74. Mango*

    I do a lot of collaborative writing and editing in Word, which means I inherit a lot of wonky formatting that leads to inexplicable document behavior. Press enter once, and push the next eight pages of document into a quarter inch column on the right side of the page for some reason!

    Two things I’ve discovered that have saved me hours of frustration:

    1) The nuclear option: There’s a “clear formatting” button right in the upper right corner of the font menu. Select a misbehaving section or just select the whole document, click the button and whatever secret wacky formatting was hiding in there disappears. Downside to this is you have to then go put back any paragraph breaks, spacing, stripped out that you still want. Ctrl+Q does the same.

    2) More advanced, or if there’s a lot of formatting you don’t want to put back in: there’s a “reveal formatting” function. Hit Shift + F1 and a pane opens up on the right side of the document that shows you everything going on wherever your put your cursor. It even includes links that will open up the correct menus to fix it. I got a (portrait, not landscape) document that inexplicably had tab stops every quarter inch from -0.83 to 13.08 inches. Reveal formatting let me see all of them – even the ones outside the visual scope of the ruler, and open the tab menu to clear them out.

  75. cornflower blue*

    My best Word trick: toss it in the trash and use InDesign.

    (I couldn’t resist, I’m sorry. I accept thrown tomatoes.)

    1. Jadelyn*

      I’m with you, although I don’t have InDesign, just Publisher. But it’s still better than Word for anything that you want a decent level of control over the formatting and visuals of. I can’t tell you how many brochures and cover pages I’ve gotten in Word that I’ve just said “…eff this” and recreated them in Publisher instead, which is actually *faster* for me than trying to convince Word to do what I want.

    2. Guacamole Bob*

      I wrote my graduate thesis in LaTeX because I just couldn’t handle dealing with Word’s image handling and tables and references and stuff. It was a good reason to teach myself that kind of program, and everything ended up looking so nice!

      (I was in an interdiciplinary field where my classmates were split among Word, LaTeX and InDesign, which made it easier to find help when I needed it.)

      1. Miss Pantalones en Fuego (formerly Floundering Mander)*

        Me too! It was the pictures being pushed off the edge of the pages that finally convinced me to make the switch to LaTeX. The actual content of my thesis might not have been that great but I had the fanciest page references and hyperlinked everything and it looked beautiful.

    3. Ego Chamber*

      OpenOffice. It’s free, open software that does almost everything the Office Suite does. I’ve been using it for like 5 years, and Microsoft changing their business model to charging on a subscription basis for programs they have pre-installed on every Windows machine doesn’t do a lot to make me regret opting out of their Office Suite.

  76. J.B.*

    To get from a spreadsheet to a comma separated list:
    (I use this for SQL where clauses, but it could have other applications)
    Get a list of data in an excel column. Copy and paste column into a blank excel and save as .csv.
    Open the .csv file in notepad. Find and replace , with ‘,’
    (You will need to add the apostrophe at beginning and end.)
    Put in a where clause as data in (‘a’,’b’,…)

  77. saby*

    Excel:
    1) I deal with a lot of really long spreadsheets where new entries get added as new tasks come in. For older tasks that are completed and no longer updated, I occasionally will go in and copy + paste values to clear the formulas and make the spreadsheet run faster (some of them recalculate every formula every time you open the workbook).

    2) if you’re summarizing data into, say, tables and charts, and you want a different report for each client/group/category/what have you, you can create a dropdown to select the lookup value for your vlookups:
    -Select the cell you want your dropdown to be in.
    -Under the Data tab in the ribbon, go to Data Validation.
    -Change your Allow value to “List” and make sure “In-cell drop-down” is checked off.
    -Select the cells that include the items you want in your dropdown under Source.

    Make sure to set the cell you’ve chosen as the lookup value for your formulas. All of your charts will change magically when you select a different value from the dropdown!

  78. MaureenS*

    Excel – If you hold your cursor on the bottom right corner of a cell, it changes to a black ‘+’ sign. Now double click. It takes whatever was in that cell (or cells) and copies it down as far as it thinks your data goes.

    If you have cells with ‘101’ and ‘102’ and use the copy down, you will get a list that goes ‘103’ ‘104’ ‘105’ etc. If you have cells ‘101’ and ‘101’, your list will be entirely ‘101’s. It can do some basic patterns, i.e. 101 & 103 gives 105, 107, etc. Jan, Feb gives Mar, Apr, etc. Formulas will copy down as well, but beware, if you have to reference a single cell all the time, use the $ i.e. C$1 reference

    1. Frank Doyle*

      Okay, I use this feature all the time, but I have two questions about it:

      a) what’s it called? (So that I can google it easier)

      b) is it possible to set it so that it fills the cells with the formula but NOT the formatting? I always have to go back and fix my borders and it’s infuriating.

      1. Jeff*

        Yes. When you do this you should get a small popup with several options. Choose “fill without formatting.”

    2. Shelby Drink the Juice*

      I frequently filter my spreadsheets. You only do the autofill once on a filtered spreadsheet. It’ll overwrite/fill in the filtered cells too.

      You have to copy & paste instead. Ctrl C, Ctrl Shift, down arrow, Ctrl V.

  79. Scott M*

    I often make use of headings in Word, primarily to create a Table of Contents. I work in I.T. and often do a lot of quick “How To” documents. It’s amazing how describing a simple task can span 20 pages or more with screen shots and such. Breaking it up into topics with headings means that I can quickly insert a Table of Contents at the beginning/ This lets people jump easily to the information they need, and get a better overview of the entire document.

  80. Colorado CrazyCatLady*

    So many things. VLOOKUP is invaluable. Text-to-column and other data-cleaning functions like TRIM, LEFT, RIGHT, etc. I also just discovered that in Pivot Tables, if you use a filter, you can have it automatically generate a new worksheet to display the data for each option in the filter.

      1. Colorado CrazyCatLady*

        So, once you have your pivot table set up with a filter, in the top left hand corner of the screen is “Options” (if you don’t see it, make sure you’re clicked in your pivot table). Then just click “Show Report Filter Pages” and it creates new labeled worksheets for each item within the filter. It’s super useful.

  81. Maya Elena*

    You can format a pivot table to be in table form (so all headings are on the same line):
    After you make a pivot table, click on the pivot table. You’ll see the “options” and “design” tabs at the top. In the “Design” tab, go to “report layout”, and select “show in tabular form” and “repeat all item labels”. You can also take off the subtitle if you want.

    1. Amber T*

      There was a spam post originally posted – something about a loan scam. Cue the many sarcastic responses :)

    2. Countess Boochie Flagrante*

      What Amber said, yes. Honestly I kind of expected Alison would nuke my response along with the spam. It was the usual “blah blah blah I have no credit but these people gave me $100000 kind of thing.”

      1. SF2K01*

        Basically Vlookup scans columns and Index Match scans whole matrices. Due to that, it’s more flexible and powerful than Vlookup; there are less restrictions with positioning; you can freely add or remove data columns; able to look up larger values; it’s also literally faster for large data sets. Only downside is that the formula is slightly more complex. Lots of good info on the differences here: http://www.mbaexcel.com/excel/why-index-match-is-better-than-vlookup/

  84. it_guy*

    Excel Keyboard short cuts:
    +; Pastes date
    ++; pastes time

    It used to work in ALL of the MS products but they keep moving the goal posts……

  85. Goya*

    Excel – Mac:

    Command + 9: Hide Rows
    Command + Shift + 9: Unhide Rows

    Command + 0 (zero): Hide Colums
    Command + Shift + 0 (zero): Unhide Colums

    Keystrokes in general are my jam. They save so much “extra” work when you’re doing data entry. Like “tab” to the next field.

  86. Julia the Survivor*

    My boss was pushing me to work faster and I found an alternative to the time-consuming formatting of bulleted lists in Word, which I had been using for our meeting agendas. I’m sure you’ve all noticed Word’s tendency to arbitrarily change the spacing, indents, bullet types…arrrgh! Then you have to spend time trying to fix it…
    Instead of bullets, press tab for indents and a dash followed by a space to mark each item.
    If the words wrap to the next line, use Home or End and press enter at the end of the line/beginning of wrap, then tab the next line (no dashes), and do this each wrap till end of the item.
    It’s much faster! I was soon doing this automatically without thinking about it. :)

    1. Emily, admin extraordinaire*

      So basically you’re treating a word processor as if it were a typewriter. I would hate having to edit a document produced in this way (in fact, I have had to edit a document produced this way, and it took me hours to fix all the manual line breaks). Word’s autoformatting can be tedious, but there are ways to set it up where it won’t change spacing, indents, etc., arbitrarily. Look into the Multilevel List function (coupled with Styles) and how to Adjust List Indents, and I promise, it will be much faster than manually formatting bulleted lists.

    2. animaniactoo*

      Yeah, this is the kind of thing that I’d set up a style sheet to handle and just hit that style sheet to format the type.

  87. saby*

    Also: Advanced Filter in Excel has an option to filter to unique values only. Or you can use the “Remove Duplicates” tool under the data tools if you want them gone for good. Both under the Data tab in the ribbon.

  88. Lalaroo*

    Excel tip: NETWORKDAYS formula!

    In my job we have to track the age of cases, and we only count working days. We don’t include our office’s holidays, so we use the NETWORKDAYS formula which is so cool! You add a named range for your holidays, and then format the date calculation formula like this: =NETWORKDAYS(“start date cell”,”end date cell”,Holidays) -> so something like =NETWORKDAYS(A2,B2,Holidays)

    I also use lots of nested “IF” statements. For instance, when tracking case age, we have a column that calculates how many days late the case closure was. There are several variables that need to be taken into account for the formula:

    1. The formula relies on the case closure date in order to be calculated. If there is no entry in that field (because the case isn’t closed yet), I need to make sure there’s no #VALUE error. Therefore, I need to make an IF statement that says Excel should only calculate the formula if there is a value in the closure date field, and leave the days late field blank if there is not
    2. A negative result would make no sense in the real world. If the closure isn’t late, I don’t need to see that it was -12 days late, but just that it wasn’t late. Therefore, I need to make another IF statement that if the calculation results in a number less than zero, the cell will display zero.
    3. As I mentioned above, we calculate days excluding our weekends and holidays, so I need to make sure to use the NETWORKDAYS formula to get the right result.

    All of this results in the following formula:
    =IF(AB2=0,””,IF(AND(AB2″”,(NETWORKDAYS(AA2,AB2,Holidays))>0),(NETWORKDAYS(AA2,AB2,Holidays)),0))

    Which translates to:
    – If “closure date” field is empty, leave this field blank as well
    – If “closure date” is not empty AND if the number of network days from start date to closure date is greater than zero, enter the number of network days
    – If “closure date” is not empty BUT the number is not greater than zero, enter zero

    I seriously felt like an incredible genius when I got this to work properly! And I’ve done even more complicated ones since then!

    1. Jadelyn*

      Just at a quick scan, you could probably use MAX and ISBLANK to shorten that a little. So you’d get a formula that is:
      =IF(ISBLANK(C3),””,MAX(NETWORKDAYS(B3,C3),0))
      So it checks if the close date is empty, and if so, the cell stays blank. If not, it calculates NETWORKDAYS, then shows either the result or 0, whichever is larger (so negative numbers wouldn’t display).

  89. LabTech*

    Minor Excel formatting trick: Want to bold/italicize/underline part of a cell, but not the whole thing? Highlight the characters, and format like you normally would. If you want to strike-through or subscript part of a cell, you can do that by highlighting, then right click, go to Formatting, and select the format. It will only apply it to the characters you’ve highlighted.

    Minor data entry trick: holding Shift + Enter will highlight the cell above the one you just entered, instead of the default going to the cell below. Similarly, Shift + Tab will highlight the cell to the left (versus the right, as default).

    Can’t wait to scroll through these tricks. Love to play with Excel!

  90. LondonEngineer*

    Anyone have any good recommendations for macro/VBA tutorials? I’ve used them on and off and made minor edits and stuf to change ranges but there are so many options out there for tutorials and it would be handy if anyone had direct experience.

  91. Scott M*

    I love formulas in Excel. Often I use the “IF” formula for finding duplicates in a column. First sort by a column (for example, column B). Then , in a new column (say, Column C), I start at the second row down and create a formula like this : =IF(B2=B1,”Duplicate”, “”)

    Just copy that formula all the way down your spreadsheet, and it will display “Duplicate” for every row where the value in column B matches the value above, and nothing if it doesn’t

    2. Scott M*

      Wow, I just learned about Conditional Formatting for duplicates from a comment above, which makes my formula obsolete! Cool!

    3. Zahra*

      I just use =B1=B2
      It’ll say TRUE or FALSE. However, both of our formulas get screwed if the sorting is changed. So mark your duplicates, then go “Copy” + “Paste as value” before doing any additional sorting.

  92. oranges & lemons*

    This might only be of interest to editors, but here is my trick for preserving tracked changes when cutting and pasting in Word. To cut: CTRL-F3. To paste: CTRL-Shift-F3. As far as I can tell, there isn’t away to do this with copying.

    1. Christine*

      Oooh! Does this work on comments, as well? (I’m the office copy editor so this is very relevant to my interests.)

      1. oranges & lemons*

        Yes it does! I think even a regular copy and paste will preserve comments, but it will automatically accept any tracked changes.

    2. Shira*

      If the Track Changes option is currently turned OFF* in both the document you’re copying from and the one you’re pasting to, the tracked changes should be preserved when you paste. Glad to know there’s another way to do it!
      *Counterintuitive, I know.

  94. Goya*

    Excel – Mac:

    You can “switch” cells that are right next to each other (Ex: A2 & B2, but the data in each cell needs to be reversed) by clicking on the one (B2) of the two cells so it’s outlined. Hold the shift button while hovering the mouse pointer over the highlighted cell until you see the little hand icon. Click and drag the cell to the desired location (to the left of A2). You should see a green “bracket” to where you are dragging the cell. When you release, the cells should be in the desired position. If not…Keep Calm & Ctrl+Z

  95. Manager-at-Large*

    Here are some things that once you know they are possible – will come in very helpful the next time you need them.
    For Word:
    Learn how heading styles work together with the Navigation view and the Table of Contents. The Nav View is huge for making it easy to move around whole sections of text in the document just by moving the bar (drag and drop) in the Nav view.
    Learn how the automatic numbering works and how you can change it to the way you want.
    Learn how to lable images and figures and make a TOC for images based on those tags

    For Excel:
    Learn simple pivot tables – expecially how the Design options (report layout especially) change how you see the data a and how to use slicers.
    If you do the same manipulations with the same dataset over and over – learn to create a macro to do all or most of the repetitive formatting for you.
    Consider learning Power Query – this allows you to combine sheets, like vlookup but on any column pair, perform a lot of column formatting and manipulation, data filtering – and then create your final table that you can present or create pivot – and it will do it again automatically whenever you refresh your data sources.
    Learn a little more about conditional formatting for things you might want to do in the future so you know it is possible like: for a column of dates, set all cells that are later than March 01, 2018 purple font and those earlier than Oct 01, 2017 fill cell with yellow; learn how the icon bars and icon sets can give you a visual on a column of numbers – like sales figures or test grades –

  96. Regina Phalange*

    Combining columns into one cell with spaces.

    Example:

    A1 – Ms.
    B1 – Regina
    C1 – Phalange

    = A1&” “&B1&” “&C1 = Ms. Regina Phalange

    The & and the ” adds a space, too. It’s amazing.

  97. Faithful Reader*

    I once had an enormous file containing thousands of names (each name was in its own row, but in a single cell) that I needed to parse into separate cells (one cell each for the person’s title, first name, middle name, last name, suffix, etc.) The problem was, the names were all formatted slightly differently. Some had titles and suffixes and others didn’t; others contained middle names or initials while others were just first and last name, etc. Of course, my first instinct was to use text-to-columns with spaces as the delimiters, but I didn’t know how many columns I should add in order to not disrupt the other fields of data in the file when I ran the text-to-columns operation. I needed a way to count the number of “words” in each cell so I could figure out the highest number and then add that number of extra columns.

    Enter this wicked little formula, which I think I found via Google:

    =IF(LEN(TRIM(A3))=0,0,LEN(TRIM(A3))-LEN(SUBSTITUTE(A3,” “,””))+1)

    In this formula, A3 is replaced by the specific cell for which you’re trying to get a count. When you use this formula, you end up with a number, representing the number of “words” (or in this case, name components) in that cell. For instance, “Princess Consuela Bananahammock IV” would yield a result of 4, meaning there are four distinct words in that cell. “John Jacob Jingleheimerschmidt-Johnson Jr.” would also yield 4. “Dr. Mary M. Smith, M.D., Ph.D.” would result in a 6, and “Joe Davis” would give you 2. From there, I could sort or filter the spreadsheet to figure out the largest number, then add that number of columns and run my text-to-columns operation with confidence. (With the examples above, 6 is the largest number so I’d add six columns.)

    A bit wild, but when working with a series of enormous files that I was trying to format for import into a database, this little formula saved me a lot of time and trial and error. I have changed jobs a couple of times since then and I have yet to find another application for this string, but I keep it saved in a notepad on my computer in case I ever need it again!

  98. Han*

    Okay this isn’t much of a trick but I am OBSESSED with Concatenate on Excel – made my life SO much easier when I was working on RSVP lists, mailings, etc. Vice versa, the Text To Columns function for going the opposite way when someone who isn’t data minded thought it was fine to put first and last name in the same cell -___-

    1. Little Twelvetoes*

      Concatenate is also the bomb for avoiding extra entry. For example, I’ll have one cell where I enter a date or a year. Then I use that reference in a concatenate formula everywhere else I want to use it – for example if I want a column label for the prior year, the formula would be something like
      =CONCATENATE(“Prior Year “,YEAR($A$1)-1,” Amount”)
      I used $A$1 as a cell reference to be more understandable in my example, but in reality, I use a defined name (OF COURSE).

  99. FedLiz*

    The Quick Access Toolbar… people are still like “what did you just do?” when they see me use it. I like to keep filter and refresh all there.

    1. LCL*

      I’m still sore Microsoft took away the custom/floating toolbar feature. That was the most useful function of all for me.

  100. LBK*

    A few random shortcuts for Excel I use a lot…

    Ctrl + Shift + L = turn filters on/off
    Alt + H + V + V (after copying) = paste special values
    Alt + J + Y + P + T = convert pivot table to classic view
    Alt + J + Y + P + R = repeat labels in pivot table
    Alt + J + Y + T + D = remove subtotals from pivot table
    F2 = jump to formula bar
    Ctrl + arrow key = jump to the last cell with data in whatever direction you choose
    Ctrl + shift + arrow key = same as above but selects all cells from the cell you were on to the cell you jump to
    Ctrl + F = find
    Ctrl + G = bring up “Go to…” window
    Ctrl + H = find/replace

  101. Ruth*

    I loved the previous Excel post, and I’m happy we’re doing this again!
    So my favorite Word tricks are:
    1) Setting up a different header/footer on the first page – Double-click at the very top or very bottom of the document to open up the Header & Footer Tools menu. In the Options section, select “Different First Page.” (This comes up for me a lot because I show students how to do APA formatting, which requires a different heading on the first page.)
    2) Strip formatting from something you’re copying/pasting – Do NOT do Ctrl+V (this keeps the formatting). Instead, point your cursor where you want to paste, then right-click. In the menu that comes up, under Paste Options, select the clipboard with the A on it to keep the text only. (If there’s bolding/italicizing you want to keep, select the clipboard with the arrow for Merge Formatting.)

    1. Emily, admin extraordinaire*

      You can also do different odd/even pages, so you can have different headers/footers when you have a document meant to be bound like a book– pages numbers on the left side on even pages (which should be on the left side of a page spread) and on the right for odd; “TEAPOTS, INC.” on the even page headers (on the left, naturally) and “EMPLOYEE HANDBOOK” on the right headers. You can also specify section breaks (Layout tab, Page Setup Pod, Breaks, Odd Page under Section Breaks) to always start on an odd page, so your new section will always be on the right-hand page of the spread.

      1. Feline*

        One of my big tips for Word is always don’t use section breaks when you just need a page break. Basically, if you don’t know why you would need a section break, use a page break instead. Section breaks *look* the same because they push text to the next page, but using them for page breaks is like killing a fly with a sledgehammer instead of a fly swatter. You’ll eventually want to do something with your headings, etc., and you won’t understand why they won’t carry through your document. Because section breaks.

        Not that I have had to mop up a lot of section-break-laden documents or anything.

  102. TaxAnon*

    I’m an accountant, Excel runs in my blood :)

    Here are my favorite tips off the top of my head:

    -If you are working in a workbook with multiple tabs and keep needing to flip between them, click View > New Window to open a second window of the same book. Then you can view them side-by-side.

    -If you need to combine two different cells into one, use the concatenate function. For example, if you have months in column A and sales amounts in column B, in column C you can put =concatenate(A1,” “,B1) and it will return “January $5,000” or whatever. This is also super useful for creating unique identifiers to use for a vlookup.

    -If you want to put numbers in a cell but have it stored as text, just add ‘ before the numbers

    -Pivot tables are an easy-as-pie way to summarize a bunch of data. Just select the data, click insert>pivot table, and then you can choose what columns, rows, values you need.

    -Fill down – when you have either a value or a formula in A1 and want the same value/formula in the cells below it. Either highlight the cells (including A1) and press CTRL D; or hover over the bottom right corner of A1 until you get a sort of plus sign symbol, then click and drag down

    1. NotThatGardner*

      question for you, excel-blood!
      i love the fill down option, but when i use it on numbers (like a year value) it defaults to making each new cell the next number up in sequence (does that make sense)? I know you can click the contextual option menu and select just fill, but is there an easy way to make that the default instead?

      1. Goya*

        ditto! I don’t use this often, but I feel like when I want it to repeat the number it starts a sequence, but when I want it to start a sequence, it fills down with the original number!

        1. JulieBulie*

          If you want to repeat a number, copy the cell with that number. Then select the cells you want to fill and do a Ctrl-V. It will paste that number into all of those cells.

          (That is what you’re trying to do, correct?)

        2. HR Bee*

          I usually get around this by filling the first two cells with the same number (or date or day of the week or whatever), selecting both, and THEN click-dragging the little square at the bottom-right of the selection box. By putting two of the same value next to each other, you signal to Excel that you don’t want it to count/increment.

          Also – if you have some data next to the column you’re filling down, double-clicking the little square handle will automatically fill down to the bottom of your existing data. Very handy on long spreadsheets.

      2. TaxAnon*

        A couple ways:
        -type it as ‘2017 instead of just 2017 so Excel stores the input as text and will fill down that exact value
        -instead of highlighting the first instance, highlight the first two instances and then fill down
        -there’s always “paste as values” as well

        Hope this helps!

  103. MerelyMe*

    Here’s one that frustrated the heck out of me this morning: how, in Excel 2016 for Mac, do I get a tilde (~) over an a? Insert Symbol gave me emojis and flags, but no diacritical marks.

    1. cornflower blue*

      Hold down the letter instead of a quick strike. A box with multiple diacritical marks will pop up above your typing.

      1. animaniactoo*

        In case that wasn’t clear and you’re not used to using keyboard shortcuts,

        1) opt+n should result in what appears to be a tilde with an underscore
        2) type a (or any other letter) and it fills in the underscore space so that your finished result is ã

        Does this not work in Excel? If it does and you’re just not used to using the keyboard shortcuts, I can make a quick list of the usual suspects for diacritical marks and other special symbols.

        1. animaniactoo*

          Writing on the fly can bite you in the clarity stakes…

          Typing any other letter that would normally be used with a tilde results in a tilde over that letter, not always over an a, e.g. n will result in ñ and o will result in õ but g will result in ˜g since a tilde is never used over a g.

            1. animaniactoo*

              In that case, general knowledge for others you might not have on hand:

              ¨ is opt+u
              ¡ is opt+1
              ¿ is opt+shift+?
              ® is opt+r
              © is opt+g
              ™ is opt+2
              ø is opt+o
              98°, degree is opt+shift+8
              • is opt+8
              ¢ is opt+4

  104. Bea*

    I feel like this is a “duh” thing that I should have figured out a while ago, but I learned today that CTRL+; will enter today’s date in Excel and Access. Hitting CTRL+Shift+; will enter the current time.

    I also set up keyboard shortcuts in Word to show/hide tracked changes, one for insertions/deletions, one for formatting, and one for comments. It’s greater for toggling back and forth when I’m reviewing a document and don’t want to see tracked changes, but need to be able to check changes quickly.

  105. Seespotbitejane*

    For Word formatting issues, Show/Hide (the little paragraph symbol on the Home tab). It will show you hidden formatting, so if there’s a stray tab or a page break that you didn’t know about that’s screwing up your formatting you can *see* it.

    Also a big big fan of Quick Parts. When we make quotes we include a price list but the Canadian and US prices are different. Instead of retyping them every time, or copy/pasting from another document you can save them in Quick Parts and drop them in where you need them.

    And if anybody is doing mass correspondence (same letter/email to multiple people) and doesn’t know how Mail Merge Fields work, do yourself a favor and look that up too.

  106. J.*

    This one is really basic, but Excel’s Proper function has saved me a ton of time. =PROPER(A2) will take whatever text is in cell A2 and capitalize the first letter of each word and make the rest lowercase. So if you have a spreadsheet with names, say from a google form where most people typed their names the usual way but some people used all lowercase and some people used all caps, it will even it out so everything is the same format.

    Bless you, Proper, and your siblings Lower and Upper.

    2. Admin*

      This is fantastic! I’ve also used =TRIM(A2) to remove excess spaces from cells which contain text (i.e. ” Harry Potter” becomes “Harry Potter”). This sounds minor, but it’s prevented several of my mail merges from producing off-centered labels!

  107. WG*

    For Excel, Mail Merge Toolkit add-in. It’s mail merge on steroids and can do things like have multiple people cc’d or bcc’d and attach a customized PDF to each individual email. Huge time saver for customized mail merges.

    1. JulieBulie*

      Mail merge is good for things other than mail, too. It’s good for anything where you have a basic document that you need to produce a bunch of different versions of.

  108. Mimmy*

    I’ve been waiting for this post ever since Alison mentioned it last week! I may have to bookmark this for future reference.

  110. justsomeone*

    Mine is a DUH thing in Word but I LOVE it. We get a lot of emails from our branches in ALL CAPS and I need to convert them to sentence case and three is an easy button in Word that does it for me. *angels sing*

    1. Emily, admin extraordinaire*

      Yup. Also Capitalize Each Word or ALL UPPER CASE or all lower case or even tOGGLE cASE. It’s nice for formatting headings, too. (It’s the Change Case button, represented by an uppercase A and a lowercase a on the button, on the top row of the Font pod of the Home tab of the ribbon).

  111. J.*

    Oh, I just thought of another one. Using IF in combination with ISNUMBER will help you pull out partial info from a field.

    Say for example you have a column full of job titles, but they’re all super fanciful and elaborate nonsense, like Rainbow Assistant for Care of Unicorns and Sparkle Assistant for Care of Unicorns Special Snowflake Director of Sparkles and Rainbows for Division of Magical Creatures, and you want to narrow down to just the Assistants, Managers, etc. You can use =IF(ISNUMBER(SEARCH(“string of words you’re looking for”,$FieldYou’reLookingIn)),”Thing You Want When You Find the String”,”Thing You Want When You Don’t Find the String”)

    So in this case, I could do =IF(ISNUMBER(SEARCH(“director”,$B2)),”manager”,””), and all of the people with Director in their title would return the word “Manager” in that column, and everyone else would be blank. Then I can go through the blank ones and do =IF(ISNUMBER(SEARCH(“assistant”,$B2)),”Assistant”,””) to find the assistants, etc. You can use filter if you’re only looking for one, but if you want to be able to sort or something more complicated, it’s helpful to be able to create this stripped down category and not have to rely on the full field.

  112. Changed*

    Cool things from Excel:

    I found some VBA code online that will hook into Outlook, create a new email, copy a range of cells, and paste it into the email, and add a specified subject, to address, and from address. Then it will either send or display the email. I added some extra stuff so that it adds text before and after the range and includes the time the data was pulled, and I have a report built with one click of a button. (If you google this, make sure you edit the To address before you do anything else, the guy who made it has it set up to email his own email address with no confirmation. Data security first!)

    Turn a range into a Table, and point any data validation criteria at it. The data validation will automatically expand if you add more stuff to the bottom of the table, because the table itself expands.

    The indirect formula is your friend if you need to click and drag stuff about when it has formulas pointed at it. Without indirects the target of the formulas will change, which messes up anything which points at a range instead of a single cell. (In my case the use was for dragging around lines representing weeks of work to make a rota, and using formulas to count the number of weekends each rota was working).

  113. Aardvark*

    Find the next value in a partially populated column (if you say, have teapot leads/customers in column A and their most recent purchase in column B, and not all of them have a purchase): click ctrl + down.

    Excel eats leading zeroes and likes to convert things, which…just don’t ask me about the 7-Juls in a database import of number ranges or the duplicated db import rows, because I feel the proper amount of shame. You can prevent this by selecting the range you’d like to hold sacrosanct and either choose “Text” format from the dropdown at the top of the screen or right click, choose “Format…” and then specify Text. Same thing with dates — you can reformat dates (keep time formatting but don’t show it, change to YYYY-mm-dd, etc) by setting the cell format. You can also set custom formats. You can also prevent truncation of leading zeroes on import from a csv by going to the data tab, choosing “Get external data/From Text”, selecting delimited, choosing commas, and highlighting the column to import on the format screen and selecting “Text”. If you need to restore leading zeroes, you can use a formula like: =RIGHT(REPT(0,10)&B2,10)

  114. eee*

    Excel: If you have to do the exact same thing to a ton of sheets in the same workbook, you can select all of the tabs at once. Then anything you do in one tab happens simultaneously in all the others! This is the type of thing where if you add in a few extra steps, you can really limit the amount of customizing you have to do.

    Also, the Indirect function is amazing for this type of thing. If you want to basically write the same function over, but manually change parts of it in each thing–for example, if you want to have your cell equal to the cell in another workbook with the SAME tab name (which, if you are working with a grouped spreadsheet and want to do the same thing for like 20 tabs, you don’t want to have to do this manually), you basically use concatenation to have one cell write out the function you want. So for example =”‘[*the other filename*]”&MID(CELL(“filename”,$A$1),FIND(“]”,CELL(“filename”,$A$1))+1,255)&”‘!*the column you want to put it into*”&*the cell row number you want to pull from* (delete the *s and replace it with what you want)
    –> this displays as: ‘[*the other filename*.xls]iCo’!D9

    When you do INDIRECT(*that cell*), it actually performs the function that you’ve listed out. Now rather than going through all your 20 sheets and changing the name of the tab in the other workbook, it just does it for you. hooray!

  115. Argh!*

    This may be basic for most of you but I have been someone’s hero a few times when their emailed or downloaded document won’t print correctly or won’t print at all. Clicking “File” brings up a page of options we tend to ignore, but “Inspect Document” (with “Check for issues” as the name in the box next to it) can usually clear up any hidden code that’s gotten mixed in with the document’s code.

  116. No Longer Lurking*

    For Excel I find that just knowing that something is possible is half the battle.

    Excel has a subreddit I keep an eye on just to see what kind of solutions people are using. It makes it easy to Google for my own problems later when I know what kind of techniques/formulas people are using in similar cases.

    When someone else is going to be using a spreadsheet I build I put in 3 sheets (minimum). One for all the data I want them to enter, one for all the calculations (locked and hidden), and one for the output/pretty charts.

    If for some reason I have to troubleshoot someone else’s spreadsheet I find CTRL + ~ to be a godsend. The amount of times I find that someone’s ‘fixed’ special cases by messing with formulas/adding magic numbers and then forgotten about it when it’s no longer applicable are amazing.

  117. SarcasticFringehead*

    My very favorite keyboard shortcuts (some have already been mentioned):

    For Excel:
    ctrl + ‘ copies the contents of the cell above the one you’re working in exactly as-is (so if it’s a formula, it’ll copy the formula)
    ctrl + ” copies the contents of the cell above, but if it’s a formula, it’ll copy the result of the formula
    ctrl + l [lowercase letter “l”] formats your selection as a table, for Excel’s definition of “table”
    ctrl + ; inserts the current date
    ctrl + : inserts the current time
    F2 drops your cursor in at the end of the cell

    Word:
    shift + F5 cycles through the last three or so places where you made edits in your document
    ctrl + shift + F8 lets you select a vertical block of text
    shift + F3 toggles between all caps, no caps, and sentence case or initial caps depending on what you have selected
    ctrl + shift + c lets you copy formatting, and ctrl + shift + v lets you paste it
    ctrl + ] increases font size by one point; ctrl + [ decreases it
    ctrl + > increases font size by one level (so 12pt to 14, or 36 to 48); ctrl + < decreases it

    1. Feline*

      I’ll add a couple to this list:

      Excel
      F4 = do again. If you just inserted a column, F4 for another inserted column.

      Word
      Ctrl+K = Insert hyperlink
      F12 = Save as to skip straight to the old-school Save As window. This has saved much aggravation since the new save-as interface showed up in Word.

      1. SarcasticFringehead*

        F12 is fantastic! And I didn’t know about F4, but I’m sure that will get a lot of use.

  119. Fabulous*

    Is there a way in Excel to make words ALL CAPS?

    I keep having to copy the table into Word, make uppercase, then copy back to Excel. Would be nice if Excel had this function (or am I blind)!?!

      1. Fabulous*

        I don’t understand what that means. You’d have to do that formula in every cell you want uppercase? Seems like that’d be more work than to just retype what’s in the cell in Caps. I just want a blanket formatting, like how Word has.

        1. Emily, admin extraordinaire*

          Add a column next to the cells that you want to turn uppercase, type the formula in the first cell in that column, referring to the cell next to it (So if your column is B, type =UPPER(B2) in C2), then copy that formula down. Copy the result, paste it as a value in the original column, and delete column C.

  120. JJJJShabado*

    I code in SAS and when I have to write a bunch if-else statements based on data, I paste the data into Excel and concatenate (using & operator) and write the code. If I want it on multiple lines, I use char(10) to do a carraige return.

    I’ve typed enough math in my life to remember that Alt + 0178 is ² (superscript 2 for squared), Alt + 0179 is ³ (superscript 3 for cubed), and Alt + 0189 is ½ (fraction 1/2).

  121. camp counselor 4 lyfe*

    MAIL MERGE. I work for a summer program where we send home letters to parents detailing what dear little Sally has been up to. When I started, it was de rigeur for the assistant dean to be up late at night, manually customizing letters that had, at a minimum, a kid’s first and last name, cabin, counselors, and 3-5 activities they were signed up for. This, for a group of easily 60-80 kids.

    “There must be a better way to do this,” I said, and lo: there was. Pretty sure my boss still thinks I’m a wizard for that one.

    1. Emily, admin extraordinaire*

      Adding a series of content controls or Quick Parts with the options of activities in a bulleted list, and you’re saving even more time!

  122. Nea*

    This got buried in replies by accident. Basically, turn autocorrect into a typing macro by telling it to replace some short code starting with a symbol with repetitive sentences or phrases. Like the typing shortcuts on a smartphone.

    2. This Daydreamer*

      This was the trick I was going to add. It’s a real time saver and you don’t get so freaking sick of typing the same long phrase over and over again.

  123. beem*

    Word: Turn on grammar check
    (I think it’s in Options>Editing)

    It can find passive voice, cliche, wordiness, and everything else you’re not supposed to have in an essay.

    Make sure to get the screen where you can check/uncheck the boxes so you can fine-tune your spelling and grammar check.

  124. Solidus Pilcrow*

    Word tips for anyone reformatting docs or converting docs from one template or program to another:

    Two keyboard shortcuts that save lots of time:
    * CTRL + spacebar = removes all character formatting (color, bold, italic, size) from selected text.
    * CTRL + q = resets all paragraph level formatting (line spacing, tabs, size, etc) in the selected text back to the base formatting defined in the style.

    Notepad is your friend! Drop the entire mess into notepad and watch all the evil formatting disappear! Paste as plain text does the same thing, but it’s somehow not as satisfying. (Actually, lots of conversion work can be boiled down to getting the file as close to plain text as possible.)

  125. Eloise*

    I would urge people to know the difference between page and section breaks. They are NOT interchangeable. Random, unnecessary section breaks sprinkled around a document will ruin your whole day. Section breaks are for applying styles to a particular section of a document. Coincidentally, they may also push the text to the next page (or not, depending). If all you need is a page break, with no other changes, then for the love of all that is holy, just use the page break.

    1. Emily, admin extraordinaire*

      I had to edit a document (200 pages long) the other day that I think had been edited in Google Docs for collaboration, then downloaded back into a Word file. There was a section break at the end of EVERY PAGE. GAH.

    2. Tasha*

      If you need to remove unnecessary section breaks, start at the END of the document and work towards the beginning. This will allow you to retain headers & footers.

  126. Gaia*

    My favorite Excel trick is showing my IT department how crappy my laptop is by trying to open Excel.

    Guess who got a new laptop?

  127. LadyKelvin*

    Instead of trying to memorize the default keyboard shortcuts for symbols I use frequently, I just customize them to what feels logical for me. For example, I use the degree sign all the time, so I set the shortcut to CTRL+d and then it feels natural for me to insert it as I type.

  128. beanie beans*

    Bookmarking this page! I feel pretty advanced and Word and Excel and I just made a pretty decent list of notes. I can’t wait to shortcut the crap even more outta my next files!

  129. Girl in the Windy City*

    Format painter. And it’s available in most (if not all) Microsoft Office products!

  130. Tasha*

    If you highlight a range of cells, in the lower right hand corner of the tab you can see 1) the count of cells you’ve highlighted; 2) the average; and 3) the sum. If they are not all numbers, average and sum will not appear. This works great as a quick way to determine if there’s an ERR or NA in a large range.

  131. BlueFairy*

    My favorite advanced Word function that I discovered recently is highly specific but so helpful for me. It’s combining Bookmarks with Insert Object.

    As an editor, I often need to maintain tracked changes in a Word file for an author or another editor to review. But what if I need to insert a section which already has tracked changes into a second document? Turning on track changes in the second document will only track that a section has been inserted, not the internal edits. Copying and pasting without track changes on will just insert the final text without edits. (Another way to do this is the Spike, but it’s not as useful for my specific needs.)

    So: highlight the section in the first document, and Insert>Bookmark. Name it something you’ll remember and save. In the second document (with track changes off if you want to maintain internal tracking), choose Insert>Object>Text from File. In the pop-up window, choose the first document, click Range and enter your bookmark name, and click Insert. PRESTO!

    This is also useful for inserting an identical introduction or appendix into several documents without copying and pasting.

    1. Shira*

      If the Track Changes button is currently turned OFF* in both files (the one you’re copying from and the one you’re pasting to), the text should paste with the tracked changes still marked.
      *Counterintuitive, I know.

  132. Little Twelvetoes*

    If you want to refer to a cell in instructions, but that cell might move around when cells are inserted or deleted, use the cell address in a concatenate formula. Here’s an example I lifted from a current workbook:
    =”Plug rounding to cell (“&ADDRESS(ROW(D70),COLUMN(D70),4)&”), if needed”
    or if you prefer CONCATENATE instead of my lazy ampersand ways:
    =CONCATENATE(“Plug rounding to cell (“,ADDRESS(ROW(D70),COLUMN(D70),4),”), if needed”)

    Either way, the results of the cell will show:

    Plug rounding to cell (D70), if needed

    And then if a three rows are inserted above row 70, it automatically update to say:

    Plug rounding to cell (D73), if needed

    Set it, and forget it!

  133. Hare*

    My favourite keyboard shortcut I just learned is F12, which goes straight to ‘Save As’. I make lots of certificates for attendees, which basically means changing the name and date and saving many many copies, so F12 for ‘Save As’ cuts down on loads of unnecessary mouse-moving.

  134. Teapot Librarian*

    Anyone still reading? I just realized I have a question that might be more easily answered by this very smart commentariat than by googling. Is there a way to override an autocorrect *before* typing? For example, say I want a lower case i. I can type i, have it autocorrect, and then ctrl-z, but is there a way to prevent the autocorrect? I don’t want it to stop autocorrecting i to I, but just for isolated cases.

    1. Jonathan*

      I think Control Z is still your best bet, if you only want isolated cases to be over-ridden. You can change your settings to always or never auto correct, but not in between.

      1. JulieBulie*

        You can set up autocorrect to convert something weird like “zi” to “i”. Then type “zi” when you want a lowercase i.

        (I tested this time to make sure it worked the way I thought it did!)

        1. JulieBulie*

          Oops, that was supposed to be nested under Teapot Librarian, not Jonathan.

          (I also tested to make sure that typing “zipper” doesn’t result in “ipper.”)

    2. Sorry if it's too late*

      -File>Options>Word/Excel Options>Proofing>AutoCorrect options
      If you want the “replace as you type” completely off, just uncheck that box. Otherwise, scroll down to “i”>”I” and delete it.

      1. Sorry if it's too late*

        If you’re using 2007/older, AutoCorrect Options will be under the “Tools” tab

    1. J.*

      I have often found myself lamenting that ctrl-f doesn’t work when you’re searching for something in real life.

      1. Miss Pantalones en Fuego (formerly Floundering Mander)*

        Hah, me too! I kind of wish everything I owned had a GPS-enabled tag in it so I could just type ctrl-f “keys” or “favorite black sweater” and find them quickly.

  137. Safely Retired*

    When I copy from a web site and paste into Excel, whether it is one number or a whole table, I rarely want the formatting copied. So instead of Paste, I right-click the destination and choose Paste as text. No formatting is inserted.

  138. Linyarri*

    I created a ribbon and added all of the items from the other ribbons that I use a lot. Now I can access formatting, calculations, filtering, and macros from one ribbon instead of flipping thru them all.

    2. Miss Pantalones en Fuego (formerly Floundering Mander)*

      Ooh, I didn’t know you could make custom ribbons! That would cut out 80% of why I hate the ribbon thing. Useful if I’m ever at a job where I’m forced to use Word instead of LibreOffice. (Assuming I will have permissions to customize such things…)

    3. Changed*

      You can also pin things to the top bar (where it had the Save, Undo, and Redo icons by default). Just find it in the ribbon, right click it, and use the top option (it’s called quick access or something).

      I have Save As, New Document, Conditional Formatting, and a bunch of other stuff up there so I can get them without changing ribbon tabs.

      You can even use stuff that’s usually within a sub-menu – for example instead of Conditional Formatting you could go into the menu and add Colour Scales to the top bar, or the specific option to add thick outside borders.

      Probably a few others I should add up there too.

  139. Jonathan*

    For anyone typing formulas in Excel, once your desired function appears with the pop-up auto prompt, you can hit Tab to auto-fill in the rest of the function. So for example, if you’re using CONCATENATE, type “CONC” and then press Tab. As a bonus, the opening parenthesis also appears.

  140. Jonathan*

    This may already be mentioned above, but Ctrl + Page Up or Page Down navigates right and left between tabs in a Workbook.

  141. AreYouStillThere*

    If you type an asterisk (*) then space, it automatically starts a bulleted list. So nice when taking notes in meeting so you don’t have to touch the mouse to start a list!

  142. Robin Sparkles*

    So I read all about filtering for duplicate values or using conditional formatting but neither of these help me with identifying and counting unique values without using a formulaic method.

    Anyone have shortcuts that count the unique values in a cell? Basically- if Cell A rows are :Annie, Bob, Annie, Beth, James, Bob – I want to be able to say that the number of people are 4 not 6 (because Annie and Bob appear twice). Obviously filtering is one way- I also use pivot tables for a larger dataset but sometimes I just want a count of all unique values.

    1. Jonathan*

      This maybe overly complicated, but you can use a COUNTIF function in an adjacent column to tell you how many instances of each name appears (e.g., COUNTIF for Bob = 2. Then in the next column, you can have 1/the value in the COUNTIF column. Summing that row will give you the unique count. You could also just condense into one column with 1/COUNTIF.

      1. Jonathan*

        The syntax could look like this, if column A contains your names: in cell B2,
        =1/COUNTIF($A:$A, $A2)
        And then sum your column. This won’t account for any misspelled accidental name variants, of course.

    3. Changed*

      You can copy the column and paste it into a new sheet or workbook, then highlight it, go to Data -> Remove Duplicates. It will give you a popup saying “2 duplicate values removed; 4 remain”. The 4 is the number you’re looking for.

      It also works for multiple columns – if you have:

      Annie Late shift
      Annie Late shift
      Annie Early shift
      Bob Mid shift
      Bob Late shift
      Bob Early shift
      Bob Early shift
      Beth Mid shift
      James Early shift
      etc.

      And you want to find out which type of shifts each person has worked, you can highlight both columns and Remove Duplicates with both – it’ll only remove lines where both columns are duplicated in another line.

      Apart from this, you’re looking at nested SUMPRODUCT, COUNTIFS, and other similar stuff if you want to do formulas, or you could just insert a pivot table, add your column to the rows field, and select the row headers to see the count in the status bar at the bottom.

  143. Imaginary Number*

    Excel:

    – F4 to change a cell reference in an equation to absolute/constant without having to type $ (faster than shift + 4 multiple times.)

    – Use goal seek (under what if analysis in data tab) if you have multiple calculations and what to see what initial value will result in a certain result. You can also use Solver in data tab if you want to find an optimal condition.

    – Ctrl + shift + down to select all of the elements in a column starting with the cell you selected.

    – Tables allow you to reference columns by name instead of column number, which is useful when working with lots of data and only need to remember the variable name instead of the column you’re storing everything at.

  144. Imaginary Number*

    Word:

    – Make your own shortcuts. I’m an engineer and Word’s equation editor has actually gotten pretty good with a lot of LaTeX markup, which is great. However, it’s annoying to have to click on “insert equation” every time. It’s under Options, Customize Ribbon, then Keyboard Shortcuts: Customize at the bottom. I use Ctrl+Shift+F for inserting functions.

    – Ctrl + A for select all and Ctrl + H for find/replace.

  145. Director of Programs at small nonprofit*

    Super simple but helpful Excel keystroke–if you want to type on the next line in the same cell, press and hold Alt+Enter. It is like hitting Enter in Word.

  146. beanie beans*

    Here’s one that I learned recently that helps me modify data when working in filters, where normally you can’t work with it because of hidden rows that would be affected.

    Once your data is in filters and you narrow down your data. Go to Home, Find & Select, Go to Special, and Select “Visible Cells Only” it will select all of the text that is visible, but not the hidden data.

    From there you can do a number of things with just the visible data that you wouldn’t normally be able to do with filters – delete that data, do a find and replace, etc.

    Note that it just selects the cells, so as soon as you do something like click off of it, it will deselect and you’d have to do it again. And if you delete the data, once you remove the filters, they will be empty cells, not deleted cells, if that makes sense.

  147. EleonoraUK*

    1. Ctrl + Shift + L to put a filter on or take it off. So much quicker than putting a filter on via the ribbon, and much more straightforward than remembering which column you filtered by again, clicking Select All there, then finding the other column you filtered by, and clicking Select All again. Ctrl + Shift + L twice and you start afresh, just like that (the first one takes the filter off, the second time puts a brand new filter on with no selection).

    2. The subtotal formula is the absolute best, especially with function 109 (i.e. =subtotal(109,[range]). You mean you can give me a subtotal of whatever I’ve filtered by, which changes dynamically as and when I change the filter, and I don’t have to select the column for a total and take notes? Magic.

  148. Teapot Tester*

    I’m shocked no one has mentioned Ctrl+D – copies the text from the cell above it. For a whole row, highlight the empty row, and then Ctrl+D will copy the entire row.

    1. Elsajeni*

      Ooh, yes — you can also highlight from the cell you want to copy down any length, hit Ctrl+D, and fill that entire column with the same contents as that first cell. Useful if, for example, you need to fill in the “date” field in every row with today’s date. (If you want to copy in a different direction, there’s not a handy keyboard shortcut, but it works the same — select the cell or range you want to copy into, if it’s a range make sure you’ve selected the original cell you want to copy as well, and click Fill -> choose Down, Right, Up, or Left.)

  149. KV*

    I had classes in college on the old versions of Microsoft Office and had never used the updated Windows 10 versions, then came to Japan four years ago. Was given a nice new laptop with the updated Microsoft Office set, all of it in Japanese. Quite a shock there. Don’t have any cool tricks and in fact have forgotten some of the ones I used to know… but if you want someone who can use the program even when the whole thing is in a foreign language, I am your guy.

    So, if you really want to know the basics of a computer like the back of your hand, change your whole computer to a foreign language that doesn’t even use the Western alphabet and see if you sink or swim, LOL!

  150. bigboicat*

    FOR WORD:

    There’s a difference between “page breaks” and “section breaks.” This is interesting because normally you’d just create a page break and start on the next page, right? Instead, you can use a section break to start the beginning of a new chapter, section, etc. When you go to create bookmarks, a table of content (which can be automatically), sort through the information or try to export it in formats like pdf it will remember that and make your life SO. MUCH. EASIER. There’s even column breaks! Learn how to use them!!!

  151. Celestina warbeck*

    late to the excel party: you can copy and paste simple arithmetic. For instance if you wanted to divide a column of values by 100, instead of creating a “helper column” with a formula, you could copy a cell that contains the number 100, select your column/values, right click and paste special, and choose “divide”.

  152. Tiger Snake*

    When using Find/Find and Replace in Excel; the new-line-same-cell character is Ctl+J.
    (You do not see a character in your find search when you add this character, your flashing cursor will turn to a small flashing fullstop instead.)

    No one believes how useful the ability to add or change new lines across an entire spreadsheet is until they make it one of their usual go-to options.

    1. Tiger Snake*

      And I suppose the follow on for this; to add new lines inside a single excel cell, just hold down Alt+Enter.

      Not only is it useful if you end up with a table with a massive comments section, but new lines don’t affect nested functions. So you can separate your function into components that make it easy to write, review and correct.

  153. Shira*

    In the View ribbon of Word: Split and New Window. Split lets you view, scroll through, and edit two parts of a document at the same time in the same window. New Window is similar but gives you two parts of the doc in two different windows (very handy when working with a big screen or multiple screens!)
    I edit long documents all the time and it’s so useful to be able to have multiple parts of a document on screen at the same time (like being able to refer to endnotes in real time!)

  154. Aska*

    If you use a lot of macro-based references in Word (Table of Contents, captions, cross references and other stuff found in the References menu), you can update them all with Ctrl+A (to highlight the whole document) and then F9. I became an instant Word wiz with that one when I started this job, because a colleague created documents with like 40-50 captions and cross-references to the captions, and whenever she moved one (so Figure 20 ended up before Figure 19), she’d right-click each caption and reference and use Update field. (Warning: this will also magically reveal any errors in your references…)

  155. Grey*

    For everything Exel can do, I’m amazed at the one thing it can’t: Automatically center a spreadsheet on any computer its opened on.

    I could use a VB script to auto-fit it to the width, but that’s not quite the same thing. Plus, this is a spreadsheet that will be widely circulated via email so I’d rather not use a script.

  156. Monika*

    Here’s a formular that the world needs *coff* always good for some laughter when I trained Excel ages ago.

    =roman()
    It converts a number into a roman number. The result is text so you can’t continue calculating with it, but still fun. A teacher friend loved it to make worksheets for her classes.

  157. mic drop by google*

    This isn’t all that complicated of a trick, but it was a good effect.

    The default setting on word is to turn strings of dashes or underscores into a big horizontal rule that can be difficult to get rid of- I had a problem with it on my own time and eventually got fed up and figured out how to fix it. The trick is it’s actually a border line, not an object in the usual sense, so you can’t necessarily erase it but if you hit ‘no borders’ it will disappear. No big.

    Then one day, a coworker who was clearly fighting with a document asked me if I had any idea what they could do to make the horizontal rule go away.

    I walked over, hit ctrl+a and clicked one button, and walked away.

    WIZARDRY.

    (Then I came back and showed them how to fix it so they could handle it next time, of course.)

    1. mic drop by google*

      Oh, and one more– findreplace line breaks in excel, i think it was ctrl+j that you type into the “find” box, and nothing in the replace– it will look like they’re both empty but it TOTALLY works.

      I did that once and got rid of something like 90,000 extra lines in a sheet.

    2. Canadian Teapots*

      That sounds like it could’ve gone over badly, since it sounds like you hit ctrl-A, Del. :O

      Me, when I need to get rid of that horizontal bar, my workaround has been to select across the line and “Cut” it. Then I just never paste.

  158. Anecdata*

    I format exports from our database that are meant to be printed out and distributed on hard copy.

    My favorite trick is to use an extra column to keep track of whether a line is the first of a new group – and then use conditional formatting to, for example, put borders around each group of text.

    So if the databases export was a list of cities and states:

    Massachusetts, Boston
    Massachusetts, Gloucester
    Massachusetts, Needham
    Virginia, Arlington
    Virginia, Roanoke

    I add another column with a formula that returns 1 if it’s the first entry for a new state, and 0 otherwise (in this case: “IF(A1=A2, 0, 1), extrapolated throughout the column.

    Then conditional format applied to the whole row, where if the value in column C is 1, it sticks a thick border above it, and if the value in column C is 0, it whites out the text.

    When printed, it comes out nice and easy to read, and looks like:

    ———————————————-
    Massachusetts | Boston
    | Gloucester
    | Needham
    ———————————————-
    Virginia | Arlington
    | Roanoke

  159. The Claims Examiner*

    Hold Alt and type IR to insert a row above the selected row. Nothing fancy, but I have a spreadsheet where I frequently insert data by date, and if I don’t want to put it at the bottom and re-sort everything it saves a few clicks.

    Learning vlookup has made reporting immensely easier.

      1. Pseudo-Fed*

        In the same vein (excel):

        Shift-space to select an entire row; ctrl-space to select an entire column. Ctrl-+ to insert a cell or row or column, and ctrl– (control-minus) to delete same.

  160. First Time Caller*

    I love this thread so much, I want to print it out and roll around in it. Thanks for sharing all these cool tips and tricks! I think my favorite one has been mentioned already, but highlighting a word or sentence, and then shift+F3 to toggle between capitalization styles. We do a lot of allcaps headers in our documents and it’s a time saver.

    In Outlook, ctrl+enter to send emails.

  161. Teacher Teacher*

    I often need randomly-generated lists of dates for practice spreadsheets.
    I’ll enter a reasonable start date and end date, then switch to Formula view (CTRL+`) and note the serial numbers assigned to those dates.
    Back to normal view (CTRL+`), I use the function: =RANDBETWEEN(lowest serial #, highest serial #) and fill my column. Format the column as dates.
    To prevent the dates from recalculating, I then copy the column and Paste Special > Values to lock in those random dates as dates, not formulas.

  162. Cath*

    Not sure if it’s been said, but ctrl+; to enter today’s date became very useful on my first day in my current job.
    Frankly, I’m always looking up keyboard shortcuts, like turning on (ctrl+shift+l, I think) and applying filters (alt+down arrow, e for search) or ctrl+pg down for changing sheets within the workbook. It’s kind of hard to verbalize them, because they are so second nature to me now.

  163. samgarden*

    When you’re creating a header or footer in Word and you want to have something right-aligned, PLEASE FOR THE LOVE OF DOG, DON’T TAB ACROSS THE PAGE.
    In the header / footer design ribbon, click ‘Insert Alignment Tab’. Select ‘Right’ and ‘OK’. Et voila! You are right-aligned. There’s a bunch of nifty alignment options there, but this is the function I use most.

  164. Em*

    Oh lord: how has no one mentioned this yet? WILD CARDS. Specifically, the incredibly detailed guide to wildcards in Word over at Graham Mayor’s page. It basically lets you manipulate your text in incredibly advanced ways. For example, I used this when someone gave me a word file full of address labels, but I needed to sort through them and weed out individual names. With a bunch of wildcard find-replaces in a row, I was able to convert the labels into an excel list, which I was then able to filter and sort.

  165. LT*

    I didn’t think this was uncommon knowledge, but I taught it to my boss at work today- when sending out a Word document for multiple people to review, the sender or whoever “owns” the document will end up receiving different “parallel” drafts that people have reviewed, made edits to, and commented on. To see all the changes in one easy file (and to avoid having to go line-by-line in 2+ different versions), go to the Review tab, click on “Compare,” and from the drop down, click on “Combine.”
    You’ll need to decide which document to maintain as the original, and if you’ve opened many documents recently on your computer, it may take a bit of searching for the documents you’re reviewing. Then, select the Revised document you want to pull changes from, and click OK. There’s other settings you can select that will affect the way the documents are combined.
    After clicking OK, you should now see a new document that combines changes from the new and original documents you’re reviewing. The way it combines these edits may have some quirks (e.g. we saw a logo appear twice on our document but I think one was redlined as a deletion), and if you’re using anything newer than Word 2010, this annoying result happens where you’re sent to a screen with 4 smaller windows in it- the combined document, the two (Original and Revised) you pulled from, and a Review pane. Older versions of Word weren’t this complex, but if you don’t need those extra panes, close out of them and proceed in reviewing all the changes that have been combined into this new document.

Comments are closed.