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? 😀
7 Responses to “Project Dashboard + Tweetboard = pure awesomeness!!!”
I would like to see actual hash-tagged DM tweets go out to the specific information consumers. That would be an interesting way to communicate the key daily data to interested parties.
A Twitter-like secure application like Yammer might be a good fit with this.
For example, how about daily tweets to selected user groups (secure) that would display sales, bookings, cash receipts, cash disbursed and a second version that would show the same info for MTD, QTD or YTD figures.
@Dan, it would be great. I did not taught about implementing it on this dashboard because twitter is blocked to the whole intranet here. However, there's a discussion here about how can we send these tweets to blackberries (probably through e-mail) automatically. (I'd like to see this implemented on a jabber restricted network as well, but here it'll probably not happen)
The wrap-up versions you mentioned doesn't apply to my particular scenario, but on a sales tweetboard it would be a great tool indeed - choosing who will receive which message according to hashtags. I'll think on something, thanks for the advice. 🙂
(Ah, btw, I'm Fernando... 🙂 )
@Dan: That is a fun idea. Instead of tightly integrating twitter functionality with a dashboard, i think it would be cool if we have a "tweet this" button that users can click after selecting a range of cells. We can easily show a dialog with the concatenated output of the selected cells and ask user to edit the text and eventually "send to twitter".
For eg. you can select the annual sales figure cell and click on "tweet this" button upon which a dialog will show the value. Then you can pre-pend it something like "DM @boss look at our sales this year: "
@Aires.. thanks once again.
Wow it looks really good. Not sure though how much the tweet facility would help in real world project management, but certainly having a dashboard on a project should be a key deliverable when learning how to manage a project
The other use of this is during the software development life cycle especially when you have parallel streams of development and testing going on. Using a dashboard is a quick way for everyone on the team to see where the project is at and how it all fits together.
Regards
Susan de Sousa
Site Editor http://www.my-project-management-expert.com
Hi Chandoo,
I purchased the project management toolkit but the dashboard shown above with the imbedded scroll bars. Is it included in the project pack??
Thanks
Sue
The gantt chart section of this dashboard is similar to one I have recently created: http://xlcalibre.com/hr-dashboard-gantt-chart-traffic-light-reportIt has a similar approach with scroll bars, but has a couple of additional features. I've tried to incorporate a traffic light report element, and also allow the timescale to adjusted so that can view it by days, weeks or months.I really like the other tables that you've incorporated, I may well try to replicate them to improve my version!
I am a monitoring and evaluation consultant in international development, and one of the services I offer is to help non-profits and foundations develop performance dashboards. I often advise them to develop dashboards for ongoing programs, rather than for one-time or pilot projects, because of the time involved. I am trying to find out from a few people how long it takes you to develop a project management dashboard, and to what extent the indicators vary from one project to the next.