SubscribeExcel can be Exciting – 15 fun things you can do with your spreadsheet in less than 5 seconds
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
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
2. Filter unique items from a list
Select the data, go to data > filter > advanced filter and check the “unique items” option.
3. Sort from 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
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
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
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
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
Just go to Menu > Edit > Clear > All to remove all the formatting from selected cell / range.
9. Auto sum cells with one 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!
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
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
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
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
14. Change the color of various sheet name tabs
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
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.
So what do you say now? Isn’t Excel Exciting? 
Trackbacks & Pingbacks
- Pingback by Calculate the the Number of Working Days [Excel Tip] on August 1, 2008 @ 3:21 pm
- Pingback by B On The Move - Tech News » Blog Archive » Calculate the the Number of Working Days [Excel Tip] on August 1, 2008 @ 3:28 pm
- Trackback by roScripts - Webmaster resources and websites on August 2, 2008 @ 1:15 am
- Pingback by My diigo daily 08/03/2008 « Rock Town on August 3, 2008 @ 12:43 am
- Pingback by ??.? » Blog Archive » ???? 08/03/2008 on August 7, 2008 @ 1:51 am
- Pingback by New York OA Trader | Excel Tricks and Tips on August 25, 2008 @ 6:42 pm
- Pingback by what is the freeze panes option in excel on September 6, 2008 @ 4:47 pm
- Pingback by 150 Lists: An Introduction « Benjamin Montgomery on September 8, 2008 @ 3:04 pm
- Pingback by Some Excel Tips and Tricks « It’s a Routinated Life! on September 13, 2008 @ 1:03 pm
- Pingback by 99 Microsoft Excel Tips to Make you Productive | Pointy Haired Dilbert - Chandoo.org on September 16, 2008 @ 2:16 pm
- Pingback by links for 2008-09-26 | Funny Web Pages on September 27, 2008 @ 12:00 am
- Pingback by Excel Links of the Week - Birthday Edition | Pointy Haired Dilbert - Chandoo.org on October 20, 2008 @ 6:05 pm
- Trackback by homepage templates on October 27, 2008 @ 9:10 pm
- Pingback by Extracting Excel Help : Getting Unique, Duplicate and Missing Items from your Data | Pointy Haired Dilbert - Chandoo.org on November 6, 2008 @ 11:58 pm
- Pingback by Best of the Web in 2008 : The “E” Zone on December 22, 2008 @ 7:34 pm
- Pingback by Happy New Year to all PHD Readers | Pointy Haired Dilbert - Chandoo.org on December 31, 2008 @ 3:44 pm
- Pingback by 100 Excel Tips & Resources for Everyone | Pointy Haired Dilbert - Chandoo.org on January 28, 2009 @ 12:46 am
- Pingback by 10 ways to use numbers in your blog post titles | Social Signal on February 24, 2009 @ 2:02 am
- Pingback by Learn Microsoft Excel Conditional Formatting (Tips | How tos - Save time, impress everyone) | Pointy Haired Dilbert: Charting & Excel Tips - Chandoo.org on June 9, 2009 @ 6:46 pm
- Pingback by Extract cell comments using Excel Formula | Pointy Haired Dilbert: Charting & Excel Tips - Chandoo.org on September 3, 2009 @ 10:11 am
- Pingback by How to Make Better Excel Spreadsheets - 10 Tips to Make Excel Files User-friendly | Pointy Haired Dilbert: Charting & Excel Tips - Chandoo.org on November 3, 2009 @ 9:25 am
- Pingback by How to Print Excel Comments - Print Excel Cell Comments using Page Setup Options - Excel Tricks | Pointy Haired Dilbert: Charting & Excel Tips - Chandoo.org on December 16, 2009 @ 9:05 am
- Pingback by 15 fun things you can do with your spreadsheet in less than 5 seconds on May 2, 2010 @ 11:54 pm
Comments
RSS feed for comments on this post. TrackBack URI
At Chandoo.org, I have one goal, "to make you awesome in excel and charting". This blog is started in 2007 and today has 350+ articles and tutorials on using excel, making better charts. 
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.
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?
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:)
I can’t get the comment to change. It just wants to draw a new autoshape.
@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
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.
Very nice, thanks!
Could you clarify “You can also change the color of grid line from here (not recommended)” What is the recommended method.
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!
@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..
I use the NETWORKDAYS function all the time and it just blows people away.
Dude,
networkdays is my fav.
Nice post.
-Nikhil
My favorite excel command Ctrl and ~
displays all formulas
Thanks you pointy haired Dilbert, this is definitely a great list, like always bookmarked for future uses
greats tips
i like command for displaying all formulas
thanks
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.
Great history class. Should have gone for Excel 97 while you were at it
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.
Fun things?
@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.
Tip #13: In case anyone is an excel newbie like me, to remove the new vertical bar, just double click on the bar.
thats cool duede
Unique text entries can be found easily — use COUNTIF function for each row. You can use autofilter to delete anything with result > 1.
To the person joking about Excel 97 — that version of Office is still the standard at my workplace. No joke.
Adv
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.
@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.
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.
Nr. 5
-> does not work. And I have Excel 2003!
@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.
I did not know excel is this much fun.
@ Adam & Chandoo…
For removing / filtering the duplicate entry / unique data…one can use the readymade menu from JMT utilities….very useful…
Thx so much PHD…tusi gr8 ho ji…:)
i cannot find CHANGE AUTO SHAPE option in Excel 2007 to design my comment box. Please help?
@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.
Its really made easy.
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…
Dear All
Thank you all very much. You guys have taught me a lot of new things in Excel. Keep continuing the good work.