Excel can be Exciting – 15 fun things you can do with your spreadsheet in less than 5 seconds

Posted on August 1st, 2008 in All Time Hits , Featured , hacks , ideas , Learn Excel - 101 comments

Who said Excel takes lot of time / steps do something? Here is a list of 15 incredibly fun things you can do to your spreadsheets and each takes no more than 5 seconds to do.

Happy Friday :)

1. Change the shape / color of cell comments

1-change-cell-comment-shape

Just select the cell comment, go to draw menu in bottom left corner of the screen, and choose change auto shape option, select a 32 pointed star or heart symbol or a smiley face, just wow everyone :)

1-change-cell-comment-shape-howto

2. Filter unique items from a list

4-filter-unique-items-in-a-list

Select the data, go to data > filter > advanced filter and check the “unique items” option.

3. Sort from Left to Right

5-sort-left-to-right

What if your data flows from left to right instead of top to bottom. Just change the sort orientation from “sort options” in the data > sort menu.

4. Hide the grid lines from your sheets

6-hide-grid-lines

Go to Options dialog in tools menu, uncheck the “grid lines” option to remove gridlines from your worksheets. You can also change the color of grid line from here (not recommended)

5. Add rounded border to your charts, make them look smooth

7-rounded-border-for-charts

Just right click on the chart, select format chart option, in the dialog, check the “rounded borders”. You can even add a shadow effect from here.

6. Fetch live stock quotes / company research with one click

8-fetch-stock-quotes-live

Just enter the stock symbol (MSFT, GOOG, AAPL etc.) in a cell, alt+click on the cell to launch “research pane”, select stock quotes to see MSN Money quotes for the selected symbol. You can fetch company profiles in the same way. Learn more.

7. Repeat rows on top when printing, show table headers on every page

9-repeat-rows-when-printing

When you are on the sheet view, just hit menu > file > page setup, go to the last tab, specify “rows to repeat”. You can “repeat columns while printing” as well from the same menu.

8. Remove conditional formatting / all formatting with one click

2-clear-formats-conditional

Just go to Menu > Edit > Clear > All to remove all the formatting from selected cell / range.

9. Auto sum cells with one click

3-auto-sum-cells-1-click

Select a bunch of cells and click on the Sigma symbol on the standard tool bar. Alternatively you can use Alt+= keyboard shortcut.

10. Find width of a column with formula, really!

10-find-column-width-with-formula

Just use =cell("width") to find the width of the column to which that formula cell belongs. Width is returned as the nearest integer.

11. Find total working days between any two dates, including holidays

11-find-total-working-days-between-2-dates

If you work on project plans, gantt charts alot, this can be totally handy. Just type =networkdays(start date, end date, list of holidays) to fetch the number of working days. In the above sample you can see the number of working days between New years day and September first of this year (labor day).

12. Freeze Rows / Columns in your sheet, Show important info even when scrolling

12-freeze-rows-columns-at-top

Select the cell diagonally beneath the row / columns you want to freeze (for eg. if you wan to freeze row 1&2 and columns A&B, click in C3), go to menu > window and click on freeze panes.

13. Split sheets in to two, compare side by side to be more productive

13-split-sheet-to-two

Just click on this little vertical bar on the bottom right corner of the sheet (see below) and drag it to create a vertical split. You can do the same way for a horizontal split as well :)

13-split-sheet-to-two-windows-compare-side-by-side

14. Change the color of various sheet name tabs

14-change-tab-color-excel-sheet

Right click on sheet and select “Tab color” option to change the worksheet tab colors. Group them with similar colors if you have lot of sheets, it looks nice.

15. Insert a quick organization chart

15-instant-org-charts

Click on menu > insert > diagram to open the above dialog, just select the organization chart option, enter node values and you have a pretty organization chart. Alternatively learn how to create org charts in excel.

15-insert-org-charts-howto

So what do you say now? Isn’t Excel Exciting? :D

Your email address is safe with us. Our policies

Written by Chandoo
Tags: , , , , , , , , , , , ,
Home: Chandoo.org Main Page
? Doubt: Ask an Excel Question

101 Responses to “Excel can be Exciting – 15 fun things you can do with your spreadsheet in less than 5 seconds”

  1. Tony says:

    Great tips! Another good one is highlighting a bunch of cells and changing the autosum visual at the bottom right to be Average or Count instead of auto sum.

  2. [...] from a list, sorting data from left to right, freezing panes, and coloring your worksheet tabs. Excel can be Exciting : 15 Fun things to do with Microsoft Excel [Pointy Haired Dilbert - [...]

  3. [...] from a list, sorting data from left to right, freezing panes, and coloring your worksheet tabs. Excel can be Exciting : 15 Fun things to do with Microsoft Excel [Pointy Haired Dilbert - [...]

  4. Adam says:

    one note on the “unique items” tip – this only works with numerical data – if you’re looking to weed out unique text entries, no dice – I work in Excel a lot with names and proprietary tags and would love a way to select a unique text entry – any suggestions?

    • badOedipus says:

      You can filter duplicate text entries as described above if you use the advanced filter option, however it will not allow you to do an additional filter on an adjacent column afterwards without duplicating the data first.
      The method detailed below will allow you to filter our duplicate entries based on a conditional format.

      Select the range from which you wish to filter out duplicate text entries
      Click on Conditional Formatting > New Rule… > Use a formula to determine which cells to format
      type the following formula in the text box:  =AND(COUNTIF(“RangeAddress“, “FirstCellAddress“)>1, MATCH(“FirstCellAddress“,”RangeAddress“, 0)<> ROW()) making sure to substitute accordingly.  Note: RangeAddress should be absolute(“$A$1:$A$20″), and FirstCellAddress should be relative (“A1″).
      Set the format to fill the cells with a color, depending on the application I use either a faint off-white to down play the color or a bright yellow to really make it pop – the choice is yours.
      Ta-da your duplicates are now colored.  You can now filter by color if you use 2010 to see only duplicates or only unique records (unique being only one record per value). Pre-2010 you can sort by color to get them at the top/bottom of your list.

       
       

    • Adrian says:

      Maybe if you use Access.

      • Vincent says:

        First post on chandoo.org, wahoooo!
        Anyways, after reading the above comments I just realized there’s a similar way to flag duplicate values with a formula, and one that works for strings as well as numbers. If you’re working in column A with a header row then your IDs will be in cells A2:A___. The following formula can be entered in B2 and filled downward to return FALSE when the ID is a repeated value, i.e. it is not the first instance of that value:
        “=MATCH(A2,$A$2:$A$__,0)=(ROW(A2)-ROW($A$1))”.
         
         

        • Martin says:

          Re: =MATCH(A2,$A$2:$A$__,0)=(ROW(A2)-ROW($A$1))

          That’s a brilliant formula – I shall use that.

          Many thanks for sharing!

  5. Mazy says:

    OK, my hint, I think this excel function have never been documented or referred even in manuals:)

    If you want to insert a part of a worksheet as picture (e.g. you want to include a small chart to a preformatted excel document), do the following:

    DRAW anything, a square, circle, etc.
    SELECT the cells you want to insert as a picture
    SELECT the object you made (square,etc.)
    PASTE

    Voila:)

  6. PK says:

    I can’t get the comment to change. It just wants to draw a new autoshape.

    • Tom says:

      To change the shape of the comment In Excel 2010 – I had to Customize the Ribbon [FILE, OPTIONS] and add a “Format” tab to the Main Tabs to allow the Format tab to be available all the time. Now I can Edit the shape.

  7. chandoo says:

    @Adam.. It works for text data for me, which version of excel you are using, all these tips are tested in Excel 2003.

    @PK … when you select comment to edit (shift+f2) click on the border of the comment, then go to bottom left corner in the screen and select draw > change auto shape. Should work in excel 2003 and above. Let me know if you see some problems :)

    • Renate Callahan says:

      nope, it still doesn’t work. There is no draw -> change auto shape available for me. The left bottom corner of the screen just shows ‘Ready’ and if I right click on it it shows a lot of other things to activate, none of it is Draw or Auto options. I use Excel 2007

  8. MM says:

    You’ve missed an important step in your first tip. The Drawing toolbar must be active for this to work. Mine is not on by default, so I have to take the extra set to turn it on.

  9. Jason says:

    Very nice, thanks!

    Could you clarify “You can also change the color of grid line from here (not recommended)” What is the recommended method.

  10. Aaron Riddle says:

    The graphic designer-side of my job hates Excel, but the business owner side of me finds it to be essential. These tips help bring both sides (designer / business owner) closer together. Thanks!

  11. Chandoo says:

    @MM.. you are right, I have assumed the draw toolbar is on… thanks for pointing it out.

    @Jason… “You can also change the color of grid line from here (not recommended)”, I said that to convey changing grid line colors is not recommended, as it can scare people or otherwise make your sheet look extremely busy… but you can change the color if you wish.. :D

  12. Tony says:

    I use the NETWORKDAYS function all the time and it just blows people away.

  13. Dude,
    networkdays is my fav.

    :-)
    Nice post.

    -Nikhil

  14. anonymous says:

    My favorite excel command Ctrl and ~
    displays all formulas

  15. Keith Dsouza says:

    Thanks you pointy haired Dilbert, this is definitely a great list, like always bookmarked for future uses :D

  16. etechcheck says:

    greats tips
    i like command for displaying all formulas
    thanks

  17. Wade says:

    Great tips!
    However, #14 You need to right-click on the tab not the sheet.
    #10 You can just left click and hold on the right most line of the column letter. Also, another tip a lot of users don’t know is that you can change a section of columns you want to one width by highlighting a column with the width you prefer and left-click-hold on the bottom right corner of that column letter and drag it through as many columns as you need.

  18. Excel can be Exciting : 15 Fun things to do with Microsoft Excel | Pointy Haired Dilbert - Chandoo.o…

    Who said Excel takes lot of time steps do something Here is a list of 15 incredibly fun things you can do to your spreadsheets and each takes no more than 5…

  19. hey says:

    Great history class. Should have gone for Excel 97 while you were at it ;-)

  20. 0751firewire says:

    Hey, HEY

    Don’t be rude. It’s a waste of everyone’s time – including yours. Totally unnecessary.

    ******
    Thanks for this post! I really enjoyed reading these tips. I will bookmark this post and will also subscribe to your weekly newsletter!

    Thanks so much.

  21. Chandoo says:

    @Wade: you are right, you have to click on sheet name and not on sheet

    and #10 was meant to show another way to find column width, but yeah, I always use the left click hold technique to see if the width is enough for me. Thanks for sharing it with everyone :)

    @0751firewire: thanks :)

    @everyone… I am happy so many of you liked this post and enjoyed these small but very useful stuff hidden away in the Excel.

    • LEO DA VINCI says:

      Dear Chandoo,
      I have discovered you only 3 days back . I want a help from you . I am using a software which makes a grid file of lat,long and elevn. data (x,y,z) on 25m into 25m mesh size . I feel that this grid file which is made from a xcel csv sheet containing random x,y,z points can be made on xcel sheet itself. Can i do that ? example of a source data shown below

      x y z
      100 50 12.5
      200 40 14.0
      220 75 12.0
      202 60 15.0

  22. [...] Excel can be Exciting : 15 Fun things to do with Microsoft Excel | Pointy Haired Dilbert – Chandoo.o… [...]

  23. Outspoken says:

    Tip #13: In case anyone is an excel newbie like me, to remove the new vertical bar, just double click on the bar.

  24. Roger says:

    Unique text entries can be found easily — use COUNTIF function for each row. You can use autofilter to delete anything with result > 1.

  25. LD says:

    To the person joking about Excel 97 — that version of Office is still the standard at my workplace. No joke.

  26. [...] Excel can be Exciting : 15 Fun things to do with Microsoft Excel | Pointy Haired Dilbert – Chandoo.o… [...]

  27. shivshankar says:

    Adv

  28. [...] Find total working days between any two dates, including holidays [...]

  29. Arti says:

    I tried second tip to remove the duplicate entries from the row by copying it in another location but its not working if I use data in A’ th column as

    A1 aa
    A2 aa
    A3 bb

    and I am trying to copy the unique records to column B.
    The above scenario is not working if duplicate entries are present in A1 and A2.
    It will work if duplicate entries are present below first record.

  30. Robert says:

    @Arti

    Autofilter as well as advanced filter needs titles of the columns in the first row. If you have only the three items in your list, Excel assumes, the first “aa” is the title (field name) of your list, not an entry in the list itself. As a result, Excel writes into column B again the first aa as the title and the second aa and bb as the 2 entires.

    Simply insert a row above your list and give your list a name in cell A1. Then it should work.

  31. miragana says:

    Good day!
    It is very informative and has a very good quality in it.
    I like it…

    http://www.Squidoo.com/MPI
    mliragana.blogspot.com

    Thank you very much for your time.

  32. [...] more than 5 seconds to do. Happy Friday 1. Change the shape / color of cell comments Just select thhttp://chandoo.org/wp/2008/08/01/15-fun-things-with-excel/MI-INFO Tutorials – Excel BasicsFor large worksheets that span more than one screen of [...]

  33. [...] Excel can be Exciting : 15 Fun things to do with Microsoft Excel [...]

  34. [...] on September 13, 2008 Few weeks back, I came across a post about some useful tips in MS Excel – Excel can be exciting . So, I thought I’ll collate some of the helpful tips and tricks that I’ve come across while [...]

  35. Melli says:

    Nr. 5
    -> does not work. And I have Excel 2003!

  36. Chandoo says:

    @Melli .. Welcome to PHD…

    Are you sure rounded borders are not working. I have made this example in Excel 2003 and they are working alright for me. You have to select the entire chart to change borders to rounded, not the plot area alone.

  37. [...] Excel can be Exciting : 15 Fun things to do with Microsoft Excel | Pointy Haired Dilbert – Chandoo.o… (tags: work windows useful tutorials tutorial tricks toread tools) [...]

  38. [...] But often we leave the last steps for manual processing. The article addresses one such problem (extracting unique cells from a range) and tells us how we can automate the whole [...]

  39. homepage templates…

    I just wanted to share this nice address, where you can get wordpress themes for free. I use one of the designs for my own blog and it was really easy to install. Just activating it in admin and the job was done. :-)…

  40. I did not know excel is this much fun.

  41. Ketan says:

    @ Adam & Chandoo…
    For removing / filtering the duplicate entry / unique data…one can use the readymade menu from JMT utilities….very useful…

  42. [...] Excel can be Exciting – 15 fun things you can do with excel [...]

  43. rayna says:

    Thx so much PHD…tusi gr8 ho ji…:)

  44. [...] > and un-check grid lines option. (Excel 2007: office button > excel option > advanced)… Get Full Tip 50. To hide a worksheet, go to menu > format > sheet > hide… Get Full Tip 51. To align [...]

  45. [...] Beautiful City Photography” and “10 Companies Hiring for Work from Home”. (They’ve also included “15 fun things to do with Microsoft Excel”, which may be the most terrifying title in blogging [...]

  46. [...] Learn Excel Formulas in Plain English | Executive Dashboards in Excel – 4 Part Tutorial | 15 Excel Fun Tips [...]

  47. [...] Related: How to change the shape of cell comments from rectangle to any other shape [...]

  48. [...] on excel comments: change the shape of excel comment box | pimp your comment boxes | extract comments using [...]

  49. Francis says:

    i cannot find CHANGE AUTO SHAPE option in Excel 2007 to design my comment box. Please help?

    • Chandoo says:

      @Francis… Excel 2007 has made it little difficult to change comment shapes, but it is still possible. First add a regular shape (like rectangle) to the worksheet. Now select it. This will show a new ribbon called “format”. From here, you can find the change shape tool. Add this tool to Quick Access Bar.

      Now Select the comment cell and edit comment. At this point, use the change shape tool from QAT to change the shape of comment.

      • Renate Callahan says:

        all right!! Thanks, this answers my question posted above. Yes, now it does work and it looks great! :)

  50. Ken Buffong says:

    Its really made easy.

  51. Paul says:

    Its a bit of a faff in 2007, not sure if its just my work computer than won’t let me change the default shape for comment boxes… But for this one workbook i’ve added a simple:

    ActiveCell.Comment.Shape.Select
    Selection.ShapeRange.AutoShapeType = msoShapeVerticalScroll

    Or you can change msoShapeVeriticalScroll to any shape you like…

  52. VENKATRAMAN V S says:

    Dear All

    Thank you all very much. You guys have taught me a lot of new things in Excel. Keep continuing the good work.

  53. nazia says:

    thanx so much…it really is of gr8 gr8 help to me…… :))

  54. Deanna says:

    The color sheet tab option has disappeared. Was there and working fine but now when I right click there isn’t an option to change the color of my sheets. How can I get this option back?

  55. Sanjay says:

    Hello,

    Can I know the name of the last person who saved the file last.

    In a team of 10 members working on a shared excel file, this information will help me to know the name of the person who modified the file.

  56. Shouvik says:

    @Sanjay: Open the workbook – Click on File -> Properties -> Click on the Statistics Tab for the information you are looking for.

  57. Too nice thanx 4 sharing

  58. mer says:

    Wish I could see those images, because they’re now blocked by photobucket. Next time use imgur, or host it on your own server.

  59. mimi says:

    Brilliant!
    Helped me teach my pupils loads in I.C.T today!
    LOL

  60. irha says:

    you don’t have references :(

  61. Hussein says:

    I liked a lot this web site

    thanks

  62. Rahul aggarwal says:

    @chandoo ji
    can we change default comment cell box shape in excel 2007or 2010?

  63. saravanan says:

    Hi Friends

    Can we Increase the Column width >500 in excel 2003.

    Pls help…

  64. losraiders says:

    I really like tip #1 but I’m how can you do it if you’re using Excel 2007.  I don’t see the drawing toolbar…I believe it’s gone in 2007 but not certain.  I did see the autoshape when I select the commnent and right click but nothing happens when I select it.

    • Chandoo says:

      This is possible in Excel 2007 (and 2010) too. Follow below steps:

      1. Add any drawing shape.
      2. Select it and go to format ribbon
      3. Right click on Edit shape and add it to “Quick Access toolbar”
      4. Now, remove the shape
      5. Select comment cell.
      6. Edit comment.
      7. Use quick access toolbar to change the shape to anything you want.
      • Sudhir says:

        This is awesome Chandoo ! Tip#1 I was most impressed. #6 I was not able to replicate – if you meant Alt + (Mouse left) click, it did not work. But manually triggered the reference – but was unable again to make it available as embedded “auto look up” in the sheet itself.

  65. steve says:

    wow! this site is awesome

  66. murtaza says:

    Some tricks are not working with Excel 2003

    But others are too cool thanx 

  67. [...] and un-check grid lines option. (Excel 2007: office button > excel option > advanced)… Get Full Tip 50. To hide a worksheet, go to menu > format > sheet > hide… Get Full Tip 51. To [...]

  68. [...] Using too many tab colors on your excel workbooks [how to do this] [...]

  69. ken says:

    Can anyone help. I want to be able to hide a row  for exampe row A if the Cell  A1 is empty after i have sorted the rows.
    I can write the macro to sort the list then I am stuck.

    Any HELP OUT THERE.

    Regards ken

  70. leo says:

    why isnt my thing working
     =NETWORKDAYS(“11/11/2011″,”12/12/2012″,[holidays])

    • D Gamlath says:

      It’s not going to work that way. At least not with the parentheses. Try entering the two dates in two cells and referring those cells within your formula :)

    • Sudhir says:

      It will work – a date in formula is entered as follows:
      =NETWORKDAYS(DATE(2011,11,11),DATE(2012,12,12),0)

  71. Marcia Fay Cobb says:

    I’m doing an address directory.  All I want to do is find out how to delete a blank line or move the second line up to the first line in the cell?  Appreciate any help you can give.  Thanks.

  72. Shivani says:

    How to make comments of different shapes in Excel 2010?

  73. [...] http://chandoo.org/wp/2008/08/01/15-fun-things-with-excel/  (although I cannot get the first activity to work where you insert the shapes. I am  using Office 2011 if anyone else figures it out) [...]

  74. Jignesh says:

    we have shared the workbook, so other user can access and feed data at their respective fields, all user can view list of users accessing the shared file,but unfortunately if a user removed from list of user name then that user will be disconnected and whatever changes made will be proved to be useless as file become exclusive.

    Could you please anybody help me out how to protect the username list so nobody could removed from the list.

  75. Abdul Azeez says:

    how to change font color in cell by using formula

  76. Nadalvski says:

    Hi Chandoo,
    I bumped into your site two days ago and am hooked to it.
    Very helpful and elaborate articles.
    Thanks.

Leave a Reply