Few weeks back I have invited all of you to share your excel keyboard shortcuts in a open thread. More than 50 people commented on that post and shared a hundred excel keyboard shortcuts with us. There were so many wonderful keyboard shortcuts and tricks buried in the comments section of that post. During the weekend, I spent sometime to collect all these beautiful shortcuts and arranged them neatly so that you can easily learn them.
Special thanks to all the commenters on the original post. Without you I couldn’t have learned these shortcuts.
Here is the complete list of excel keyboard shortcuts.
Shortcuts for Selection
Shortcuts for Editing
Shortcuts for Navigation
Shortcuts for Formatting
Shortcuts for Formulas
Shortcuts for Excel Options
Shortcuts for Auto Complete
Shortcuts for Everything Else
Note: I have *ed some of the most important shortcuts. These are very useful and extremely time saving ones. You may want to remember a few to boost your productivity.
Select the whole column
Selection
CTRL + SPACE
Select the whole row
Selection
SHIFT + SPACE
Select table
Selection
SHIFT + CTRL + SPACE bar
Save
Selection
CTRL + s
Select visible cells only
Selection
ALT + ;
Select entire region
Selection
CTRL + A
Select range from start cell to far left
Selection
SHIFT + Home
Select range from start cell to end in direction of arrow
Selection
SHIFT + End + arrow
Select a continuous range of data (e.g. pivot), no matter where your cursor is.
Selection
CTRL + *
Select blank cells
Selection
F5 + ALT + S + K + ENTER
Select all cells with comments
Selection
CTRL + SHIFT + O
Select all cells that are directly or indirectly referred to by formulas in the selection
Selection
CTRL + SHIFT + {
Select all cells with formulas that refer directly or indirectly to the active cell
Selection
CTRL + SHIFT + }
Selects all the way to a1 from cursor position
Selection
CTRL + SHIT + HOME
Select cells in the direction of arrow
Selection
CTRL + SHIFT + Arrow
Previous sheet
Navigation
CTRL + Page Up
Next sheet
Navigation
CTRL + Page Down
Launch GO TO Dialog (from here you can select special or jump to a cell or range)
Navigation
F5
Go to top left (will go to top left of freezed pane if set)
Navigation
CTRL + Home
Go to last non-blank cell
Navigation
CTRL + end
Go to previous sheet
Navigation
CTRL + PgUp
Go to next sheet
Navigation
CTRL + PgDn
Print
Navigation
CTRL + p
Toggle between workbooks in a given session of excel.
Navigation
CTRL + TAB
Change the type of cell reference from relative to absolute or semi-absolute
Formulas
F4
Repeat whatever you did last
Formulas
F4
Debug portions of a formula (select and press)
Formulas
F9
Sum range
Formulas
ALT + =
Enter array formula
Formulas
CTRL + SHIFT + Enter
Select array formula range
Formulas
CTRL + /
Display range names (can be used when typing formulas)
Formulas
F3
Evaluate formulas. (its easy to remember when working with some “tuf” formulas!)
Formulas
ALT + TUF
Copy a formula from above cell and edit
Formulas
CTRL + '
Display the formula palette after you type a valid function name in a formula
Formulas
CTRL + A (while writing a formula)
Alternate between displaying cell values and displaying cell formulas
Formulas
CTRL + ` (Single Left Quotation Mark)
Calculate formulas
Formulas
F9
Select all precedent cells
Formulas
CTRL + [
Select all dependent cells
Formulas
CTRL + ]
Format Selection (cells, objects, charts)
Formatting
CTRL + 1
Bold a cell’s content
Formatting
CTRL + B
Format Painter – Paste formats from selection
Formatting
ALT + EST
Format as number with 2 dp
Formatting
CTRL + SHIFT + 1
Format as local currency
Formatting
CTRL + SHIFT + 4
Format as percentage with 0 dp
Formatting
CTRL + SHIFT + 5
Hide row
Formatting
CTRL + 9
Hide column
Formatting
CTRL + 0
Unhide row
Formatting
CTRL + SHIFT + 9
Unhide column
Formatting
CTRL + SHIFT + 0
Display the style command format menu
Formatting
ALT + '
Sets/removes strikeout in current cell
Formatting
CTRL + 5
Show/hide the top bar when you have a group
Formatting
Crtl + 8
Single border around selected cells
Formatting
CTRL + SHIFT + 7
Sort
Formatting
ALT + DS
Insert hyperlink
Formatting
CTRL + K
Freeze panes
Formatting
ALT + WFF
Remove grid lines or (alt+t)ov(alt+g)[enter]
Formatting
ALT + WVG (2007+)
To wrap lines
Formatting
ALT + HW (2007+)
Save as
Excel Options
F12
Collapse the ribbon (press again to expand)
Excel Options
CTRL + F1
Opens print preview
Excel Options
CTRL + F2
Maximize the current window
Excel Options
ALT + SPACE X
Activate next window
Excel Options
ALT + TAB
Activate previous window
Excel Options
ALT + SHIFT + TAB
Close an excel workbook
Excel Options
crtl + F4
Split screens
Excel Options
ALT + W + S
Create a pivot table in new sheet (of course after selecting the range)
Everything Else
ALT + DPF
Create a pivot table in the same sheet.
Everything Else
ALT + DPN
Show visual basic editor
Everything Else
ALT + F11
Macro dialog
Everything Else
ALT + F8
Apply/remove filter
Everything Else
ALT + DFF
Keep filter on columns, but show all rows
Everything Else
ALT + DFS
Insert pivot table
Everything Else
ALT + NVT
Turn filter on or off
Everything Else
CTRL + SHIFT + L
Paste values only
Editing
ALT + ESV
Edit a cell, place cursor at the end
Editing
F2
Show in-cell drop down with previously entered values
Editing
ALT + Down arrow
Fills down value from cell above
Editing
CTRL + D
Add a comment or Edit comment
Editing
SHIFT + F2
Insert new sheet
Editing
SHIFT + F11
Insert row
Editing
CTRL + +
Delete row
Editing
CTRL + -
Copy
Editing
CTRL + C
Paste
Editing
CTRL + V
Cut
Editing
CTRL + X
Undo
Editing
CTRL + Z
Get a line break inside the cell
Editing
ALT + Enter (while editing the cell)
Clear all contents
Editing
ALT + EAA
Copy
Editing
CTRL + insert
Paste
Editing
SHIFT + Insert
Make chart/pivot chart
Editing
F11
Edit a cell in Apple Macs
Editing
CTRL + U
Copy the value from the cell above the active cell into the cell or the formula bar
Editing
CTRL + SHIFT + "
Copies whatever is in the cell to the left of it.
Editing
CTRL + R
Delete box (cell, row, column)
Editing
ALT + ED
Insert box (cell, row, column)
Editing
ALT + IE
Enter current date
Auto Complete
CTRL + ;
Enter current time
Auto Complete
CTRL + :
Thanks to the contributors
Here is a list of people who contributed these shortcuts.
Vipul, Dau, Stružák, Paul, Eliavs, Pavel S, Fabrice, Noone, Clarity, Jp, Pascal, Jair, Yoav, Nimesh, Bill, Patricia, Mike, Iesmatauw, Chrisham, Harvey, Pranav, Rohit Choudhary, Rohit1409, Rickard, Sachin, Gerald Higgins, Ericlind, Zzz, Felipe, Sridhar, Halva, Catherine, Lavkesh Bhatia, Rick Rothstein, Vishal Haria, Ak, Daniel Ferry, Mehdi Raza.
Thank you
Share your shortcuts
I know this post is unusally lengthy. But I wanted the list to be as comprehensive as possible. If you know some shortcuts that are not listed, please share them using comments.
http://feedproxy.google.com/~r/PointyHairedDilbert/~3/5WbvoSWmwY0/
Let us take a minute and bask in some glory, for, our little community at PHD had the most fantastic month ever.
That is right, January 2010 is so far the best month since I started blogging. We have broken all sorts of previous records on content, conversations, connections, traffic and revenue.
In January, we had 20 posts and 425 comments (highest ever). There were 117 thousand visitors reading 353 thousand pages. Our RSS subscriber base grew to 7790 (it was 2068 an year ago). January is the best month in terms of revenue too. We sold highest number of Project Management Templates and Formula E-books. The site even made more $s on Google Ads than it ever did.
Personally too, the month has been terrific. Jo and I celebrated our third marriage anniversary (we also finished 8 years since we started dating). I got promoted at work and Microsoft renewed my MVP award for one more year. Finally, I got my act together and started working on Excel School.
Awesome is the feeling that comes to my mind. I hope you started the new year with flying colors too.
The website success is all due to YOU. You have been kind enough to comment, e-mail, encourage, ask, share and learn. Without you, this milestone means very little to me. Thank you. Thank you very much.
To celebrate this little achievement, I have indulged in frivolous chart porn and made a small poster. See it below:
(I removed all numbers from revenue section. Lets just say, PHD has been keeping both my mind and pocket happy. )
Bonus Charting Tip:
Of course, we dont want to waste a post with some frivolous navel gazing. So, let me share a cool charting trick with you. You can highlight Maximum value in a chart (like above) by,
First format the entire series in a dull color (like above). To do this, just select the series, change the “fill color”
Now, click on the series, and while series is selected, just click on the maximum item once again. Now, the maximum item alone will be selected.
Just change the fill color of this item to some strong, contrasting color (like above).
That is all… (learn more: using color, charting principles).
PS: Excel School registration opens on Monday. There will be a post announcing it in the next few hours.
PPS: If you are saying Excel What?!?, you should watch this.
http://feedproxy.google.com/~r/PointyHairedDilbert/~3/U1GN0JsCQN8/
Today we will build a mortgage payment calculator using excel. But we will not build a boring excel sheet, we will build a mortgage calculator that is easy to play with.
A mortgage payment is a monthly installment that you pay towards a loan. Any mortgage loan will typically have,
loan amount
duration of the loan (also called as tenure of mortgage) in years
interest rate (APR) per year
Given these 3 parameters, we can easily determine the monthly installment amount (this will be the same amount for all months during loan tenure)
We are going to use Excel’s form controls (more on this below) to build a mortgage payment calculator like this:
Why you should not be boring and use the form controls
A form control is a button or check box or scrollbar or some other click-able thing you see in Windows. Do you know that you can add the very same controls to Excel spreadsheet to make the it interactive?
For example,
instead of asking a user to enter “yes” or “no” in a cell, you can ask them to click a check box.
instead of taking “age” in a cell, you can use a scroll bar and set the values from 0 to 100.
This way of gathering inputs is more fun, engaging and interactive.
Now that you find form controls hot and attractive, lets proceed and make a house loan payment calculator.
How is mortgage payment calculated?
As I said above, any mortgage (or housing loan) will have 3 parts – loan amount (p), loan tenure (n) and annual interest rate (r).
Given the values of P, N and R, we can find the monthly payments using Excel’s PMT formula like this:
=PMT(R/12,N*12,P)
[related: PMT formula syntax and examples]
We are dividing interest rate (R) by 12 since R is annual interest rate and we make monthly payments.
We are multiplying loan duration (N) with 12 since we are going to make monthly payments.
Making the mortgage calculator in Excel
We will use scroll-bar controls to take numeric inputs required (P,N and R) for the payment calculation. And we feed these values to PMT formula to find the monthly installment amount.
Step 1: Add a Scroll-bar Control
We will use this scroll bar to take “loan amount” input. To keep it simple, we will ask users to enter input in ‘000s. So, if the loan is $120,000, the input should be 120.
First add a scroll-bar form control to your excel sheet. To do this go to Developer Ribbon > Insert > Scroll-bar Form Control in Excel 2007. In Excel 2003 use View > Toolbars > Forms and select Scroll-bar control. (related: enable developer toolbar in excel 2007)
Add a Scroll-bar Control
Excel 2003
Excel 2007
Once selected, just add the control to spreadsheet by clicking anywhere.
Step 2: Set Properties for this Scroll-bar
To set the properties for the scrollbar control, right click on it and go to “format control” option. Now go to “Control” tab.
Here set minimum and maximum values for the scroll bar. To keep our model simple, just set minimum as 35 and maximum has 500.
Also, select a cell to link the scrollbar. When you do this, excel links the scroll bar to the selected cell. So whenever scroll bar is updated the cell gets updated too (and vice-a-versa). See this illustration:
Step 3: Add Remaining Scroll bars
Repeat the same steps for 2 other scroll bars. One for interest rate and one for loan tenure.
Make sure you set the minimum and maximum values in a reasonable range.
Step 4: Plug the values in to PMT formula
Now that the scroll bars are ready, just write the PMT formula. Assuming you have linked scroll bars like this:
Loan amount in cell A1
Interest rate in cell A2
Loan tenure (years) in cell A3
The formula will be,
=PMT((A2/12)%,A3*12,A1)
Remember, PMT returns value in negative numbers (as it is the amount we need to pay, not get). But you can make it positive (for display purposes) by multiplying it with -1 like this = -PMT((A2/12)%,A3*12,A1)
Step 5: Play with your Model
Now your mortgage payment calculator is ready. You can play with it by testing various combinations and finding monthly payments. You can easily see what happens when you increase loan tenure or decrease interest rate.
Download Excel Mortgage Payment Calculator
Here is the excel mortgage payment calculator file. Download and play with it.
Bonus – Making an Amortization Schedule
You can easily extend this model to add an amortization schedule to see how much of each monthly payment is towards principal and how much is for interest.
You can calculate principal portion for any month using PPMT formula like this =PPMT(R/12,M,N*12,P). Here “M” is the month for which you want principal amount.
You can calculate interest portion for any month using IPMT formula like this =IPMT(R/12,M,N*12,P).
Click here for help: PPMT formula, IPMT formula and Excel Financial Formulas.
Do you love form controls?
Do you use form controls in your spreadsheets? I find them pretty intuitive and use them wherever I can. I have made many complex spreadsheet models easy to understand and work with by just adding form controls. The beauty is that, they require no programming or anything. You just add them and link them to a cell.
What about you? Do you love form controls? Where do you use them most?
Learn More about Excel Form Controls:
Make a comparison chart using scroll bars
Create a check box in excel
Make a donut bar chart using form controls
More form control examples
http://feedproxy.google.com/~r/PointyHairedDilbert/~3/nAeCaBNPmmc/
Today we will learn to use Excel’s LEFT and FIND formulas. But what fun it is to learn a new formula on a Tuesday?
So, we will actually learn to use these formulas to solve the problem: “extract the username from an email ID”
How is an email ID structured?
Any email ID contains 2 parts – user name and domain name.
For eg. in my email id – chandoo.d@gmail.com – chandoo.d is user name and gmail.com is domain.
So how do we get the user name out?
As you can see, username always starts at left and goes up to the symbol “@”. So, If we write a formula to fetch all the characters up to “@” symbol, it will get us the user name.
This is where LEFT() and FIND() formulas enter the scene.
What does Excel LEFT formula do?
Excel LEFT formula will let you cut a portion of text from left. For eg. =LEFT("Long",2) will give you Lo. (syntax and examples)
So, to get the email username, we need to get all the letters in the left of email ID up to the location of “@” symbol. And how do we find the position of a symbol in a text?
We use FIND formula.
FIND formula gives the location of one text in another. For eg. =FIND("do", "chandoo") will give us 5 (the location of “do” in “chandoo”).
FIND will throw an error (#VALUE!) if the text you are trying to find is not available. For eg. =FIND("peace", "world") will throw #VALUE!
(syntax and examples)
Armed with these 2 formulas, now let us get that user name out of email ID
Assuming cell A1 has the email id, the formula for getting user name is =LEFT(A1,FIND("@",A1)-1)
We have to use -1 as find actually tells the position of “@” and we need all the letters up to “@”, but not “@”.
This is how it works:
Your homework:
How would you extract the domain out of email ID? (Hint: there is a right formula for everything)
Use comments to write your answers. Don’t cheat.
Learn more excel formulas:
51 Excel Formulas in Plain English – Syntax, Examples and Explanation
Excel Formulas & Working with Text
Excel Formula Examples & Tutorials
Learn Excel Formulas using my e-book – it is in a fun format & easy to understand
http://feedproxy.google.com/~r/PointyHairedDilbert/~3/sM_7ynZcLvE/
It is not everyday that a blog boasts of 1000th post. After blogging for 1999 days ie 5 years 5 months 24 days, finally, this is my thousandth post.
While this is not a real mile stone or anything, I want to use this post to say thanks.
Thanks to this blog, I have found passion for working with data.
Thanks to this blog, I am able to share my passion with all of you.
Thanks to this blog, I learn new things almost every day.
Thanks to this blog, I found such lovely audience.
Thanks to this blog, I got so many new friends and mentors.
Thanks to this blog, I am no longer worried about my finances.
Thanks to this blog, I have improved my writing skills.
Thanks to this blog, I have become a better individual.
Thanks to this blog, I wake up with a smile everyday.
Thanks to this blog, I feel connected, compassionate, creative and content.
Thank you PHD.
The next 1000 posts
It may as well take another 5 years before I reach the 2000 post mile stone. But here is what I have in mind for PHD’s future.
Make more users awesome in Excel and Charting. That means, more tricks, hacks, formulas, charting tutorials.
Encourage guest posts – If there is one thing I am sure about what I know it is “I know very little”. The blog certainly helps me learn new things everyday, but there is so much to learn and there is so little time. Guest posts are a great way to pick up new ideas and new techniques. You will see a lot more of them in future.
Have regular polls and contests – I am hoping to launch a poll or contest every month. It is no easy task, but I find the community interaction really good on these types of posts.
Write few more “series” posts – may be about financial modeling, small business management, better charting etc.
Start a weekly news letter – Our community has grown tremendously in the last 2 years. Keeping in touch with our members has become a difficult task. A news letter (sent out once every week or two) can be an easy way to send updates and share ideas.
Improve the forums – The PHD forums have amazing number of discussions on regular basis. My aim is increase the activity on forums by encourage more members to sign up and discuss.
Have More Videos and Screencasts – It is far more easy to learn by looking someone do it. Thus you will see more videos and screencast based tutorials in future.
Launch few more ebooks and products – I find that having my own products is the best and most effective way to monetize this blog. Over time I am hoping to get rid of the ads on the blog and rely only on revenues from my products.
Review and recommend quality excel and charting products – There are several high-quality excel products – from folks like Peltier, Jorge, Stephen Few, Charley Kyd etc. and we all could benefit much by understanding how these products work and how they can help us do more in less time.
Start Online Excel Training Classes – I have been making the videos and material for offering online excel classes. The course should start sometime next year. It should be exciting to see how this takes off.
It is not post until you get an excel tip. As usual we eat and drink excel tips. So here is one to make this a really useful post.
Format a number to be shown in thousands
To format a number in a cell to be displayed in “thousands”,
Select the cell, Hit CTRL+1 (or go to “format cells”)
From the “number” tab, go to “custom” format to specify a custom number formatting code
Specify the code as #,##0, " thousands"
Bonus – use #,##0,, " millions" to show numbers in millions
That is all.
Thank you once again. Without you, this mile stone means nothing to me.
http://feedproxy.google.com/~r/PointyHairedDilbert/~3/EQlvS4vEnp0/
We all know how to make comments on excel documents, just select the cell where you want a comment, press SHIFT+F2 and make the comment. But the comments are only visible when you open the workbook. What if you want to print out an excel file, but include all the comments as well?
Turns out you can do that with a hidden option in excel page setup.
To print excel comments:
Go to Page Layout Ribbon > Click on “Print Titles” button. This opens the Page Setup dialog. (In excel 2003 you can just go to File > Page Setup)
You should be in the “Sheet” tab, if not go there.
Do you see that sneaky little box called as “comments”? Click on it and select how you want to print comments.
You can print comments as they are shown or print them at the end in a separate page. The output will look like this:
That is all. Now you know the trick to print excel spreadsheet comments.
More on excel comments: change the shape of excel comment box | pimp your comment boxes | extract comments using formulas
Learn more about printing & excel and quick excel tips.
http://feedproxy.google.com/~r/PointyHairedDilbert/~3/mCJLqHQL_EE/
Jon Peltier can stand on his roof and shout in to a megaphone “Use Bar Charts, Not Pies“, but the fact remains that most of us use pie charts sometime or other. In fact I will go ahead and say that pie charts are actually the most widely used charts in business contexts.
Today I want to teach you a simple pie chart hack that can improve readability of the chart while retaining most of the critical information intact.
We will take the pie chart on left and convert it to the one on right. The beauty of this trick is, it is completely automatic and all you have to do is formatting.
Interested? Then just follow these steps. [more examples and commentary on pie charts]
1. Select Your Data Create a Pie of Pie Chart
Just select your data and go to Insert > Chart. Select “Pie of Pie” chart, the one that looks like this:
At this point the chart should look something like this:
2. Click on any slice and go to “format series”
Click on any slice and hit CTRL+1 or right click and select format option. In the resulting dialog, you can change the way excel splits 2 pies. We will ask excel to split the pies by Percentage. (In excel 2003, you have to go to “options” tab in format dialog to change this).
Select “Split series by” and set it to “percentage”. Specify the percentage value like 10%.
3. Format the Second Pie so that it is Invisible
Individually select each slice in the second pie and set the fill color to “none”. You can speed up this step by setting first slice’s fill color to none and then using F4 key to repeat the last action (ie setting color to none) on other slices.
That is all. We have successfully converted a gazillion sliced pie chart to something meaningful and simple.
Additional commentary on Pie charts
Pie chart is not the devil, a pie chart that fails to tell the story is. I think we make pie charts because they are safe. Next time you set out to make a pie chart, I suggest you to spend a minute and think about,
What is it that I am trying to tell here?
How can a Pie chart help my audience understand my point?
Can I use an alternative to pie chart?
I can promise you that in most situations using an alternative is better and easier than you thought. After all, that is why Peltier is on his roof.
http://feedproxy.google.com/~r/PointyHairedDilbert/~3/PDZxcbXkd28/
This is the fifth installment of project management using excel series.
Preparing & tracking a project plan using Gantt Charts
Team To Do Lists – Project Tracking Tools
Project Status Reporting – Create a Timeline to display milestones
Time sheets and Resource management
Part 5: Issue Trackers & Risk Management
Part 6: Project Status Reporting – Dashboard [upcoming]
Bonus Post: Using Burn Down Charts to Understand Project Progress
Tracking issues and risks is where most of the project management time goes. Once the project planning and organizing activities are in good shape, most of the project management activities are around risk management and issue tracking. In this installment of project management using excel, we will learn how to create a simple issue tracker template using excel and how to analyze issues using excel.
Issue Tracker Template
Excel is perfect for making an issue tracker template. Its grid structure and easy interface makes it totally easy to create and maintain an issue log. Here is a simple issue tracker template you can create in less than a minute.
The above template becomes very easy to manage with excel features like data validation, filters and tables (lists in 2003 and earlier).
More Robust Issue Log Template
While the above issue tracker template is good for most project needs, often you might need something little more robust. Of course, doing this is just a matter of adding few columns. For eg. it is common for project managers to keep track of the various types of issues and who is logging them, who is closing the issues. Here is an issue log template that is more robust.
Analyzing and Reporting Issue Status
Issues are part of everyday project management. It is important to keep track of various issues in the project and understand their progress. There are various ways to monitor the progress of issues using excel charts and pivot tables. In this tutorial, we will learn how to make the open vs. closed issues chart (see below).
To make the chart, we will use the issue tracker data from the template shown above.
We need to generate issue counts for the last 30 days from a chosen date like this:
The counts can be easily generated by using the COUNTIF Excel formula [tutorial] like this: =COUNTIF(issueOpenDates,Date)
We can easily make the counts cumulative.
Finally select the 3 columns above and make a line chart with 2 series. Adjust the chart formatting you have a simple “open vs. closed issues in the last 30 days chart”
The above chart can be a great way to start discussion about issue run rate.
Risk Management using Excel
We can use similar ideas to prepare a risk management plan using excel. The risk log is similar to issue log. But when it comes to risk analysis, the usual practice is to make a risk matrix to highlight key risks. This can be easily done in excel with the help of heat maps. This is your home work to figure out.
Download the Issue Tracker Templates
You can download the excel issue log template from here. Click the below link based on your excel version and the file type you prefer.
Download Issue Tracker Template [Excel 2003 and earlier]
Download Issue Tracker Template [Excel 2007 and above]
Download Issue Tracker Template [Both files in a zip]
What next?
The ideas presented here can be extended to do more complex analysis of issues and risks in your project. However the issues tracker systems can only go so far if we don’t ask right questions. Often when the project is going through a rough patch, it might be better to keep the issue trackers simple and focus on the work.
In the next installment of project management using excel, we will combine all the five parts to build a project status reporting dashboard. Stay tuned.
If you are new to the series, please read the first 4 parts as well.
Preparing & tracking a project plan using Gantt Charts
Team To Do Lists – Project Tracking Tools
Project Status Reporting – Create a Timeline to display milestones
Time Sheeet Templates and Resource Management using Excel
While at it, also check out the bonus post about Burn Down Charts.
What is your experience with issue tracker systems
Share your ideas and opinions on using issue trackers. What is the best and worst you have seen? In one project, we have used a very complicated issue log (actually a defect log) that took almost 5 minutes to create an issue. The system would produce nice looking 3d bar and 3d pie charts depicting the issue distribution, ownership and status. Our morning scrums were a disaster when someone choose to present these. What about you?
http://feedproxy.google.com/~r/PointyHairedDilbert/~3/mv-88IC6Hcc/
This is a guest post by Paresh Shah
In his latest book, Now You See It, Stephen Few discusses techniques and best practices to gain insights from data. Of course Excel does not directly facilitate most of this techniques, but the objective can be achieved without too much work for some techniques.
On pages 165 and 166 of the book, Few discusses how grouping related time intervals can facilitate analysis of data. As an illustration he explains that when viewing data of daily website visits, it helps in separating weekdays and weekends to differentiate expected traffic during these periods. The use of this technique would make it easier for the analyst to identify any anomalous movement in ether the weekend or the week day.
Fortunately excel combo charts can help you do that.
Given below is a combination chart of daily visits to a web site [ hypothetical ] where in the days of the week are shaded. The website visit data has been plotted as a line chart.
The website visit data has been plotted as a line chart. The shading has been achieved by using column chart – the data for the secondary series has been plotted on a second axis. A constant data value for Monday to Friday, 3 and a second constant value for Saturday and Sunday, 0 has been assigned for each date of the month. The secondary axis has thereafter been hidden. The maximum value for the second axis has been manually set at 3 to get the columns to run from the top to bottom and gap between the columns has been set to zero [ Format data series ->Gap width->No Gap ]. The secondary axis has thereafter been hidden.
The concept can be used for other groupings too, months grouped by year, by quarter etc without too much effort.
Download this excel combo chart and play with it to learn more
Click here to download the tutorial workbook and learn by changing things.
Added by PHD
Thank you Paresh. That is an innovative way to achieve zebra lines / bands on the charts to group related events.
Hello there, my dear reader, if you have enjoyed this charting trick, say thanks to Paresh.
Further Resources on Excel Combo Charts
Excel combo charts – What are they and how to make one?
Make a combination chart in Excel in 15 seconds [video tutorial]
Using combination charts to make a timeline to show project milestones [project management using excel]
PS: the link to Now You See It uses my Amazon referral ID. I suggest reading the book if your job involves telling stories using charts.
http://feedproxy.google.com/~r/PointyHairedDilbert/~3/8UQU6J7MXK8/
This is a guest post from Aaron Henckler.
Waterfall charts are great, especially for visually showing the contribution of parts to a whole. While there are several tutorials on how to make a waterfall chart online the end products of these tutorials rate low on the visually appealing scale.
The principle problem with these charts is the separation between the elements of the waterfall. They are always either pushed together (Example A) or left apart, without element connectors (Example B):
Example A
Example B
Many users of waterfall charts employ the separated (default) version (example B) opting to add in element connectors manually via Insert>Shapes>Line on the Excel tool bar. The frustration with this approach is that all too often the values of the chart elements will need to be updated or changed forcing user to manually readjust each of their connector lines in turn.
With some simple charting trickery in Excel 2007 one can easily make a waterfall chart with connectors that will update automatically as element values are changed.
A Better Waterfall Chart
Steps to Building a Better Waterfall Chart
List of data series (columns) needed for your chart:
Horizontal Axis Labels: in the example above North, East, South and West.
Base Values: What your element values will “sit on.” Essentially this is the white space beneath each charted element shown above.
Element Values: the meat and potatoes of your chart – the value of your elements as you want them to appear (above these are 40, 30, 20, 10 and 100).
Label Spaces: This is optional but it allows you to place the value of you data elements on top of their respective bars (this avoids the use of the annoying Label Position options available after one has used Add Data Labels).
Label Connectors: This is the key item needed to create the chart as shown above. You will need one column (series) for each of the data elements (excluding one for the total). For the chart above, four label connector series are needed.
Step 1: Enter all of the required series in a worksheet:
Horizontal Axis Labels – self explanatory
Base Values – A running total of the subsequent Element Values (Column C). Whereas nothing proceeds North in the example above leave its base value blank. Do the same for Total.
Element Values – These are whatever numbers you want to highlight in your chart. These are represented by the blue column segments in the above chart.
Label Spaces – Again this is optional. These will eventually hold text labels for the Element Values (Column C). The numbers here should all be the same and be some number about 1/4 to 1/3 the value of your lowest Element Value.
(to H. ) Connectors – Connectors 1 to 4 correspond to Axis Labels North to West in the example above. In the respective Connector column make the cell at the row corresponding to the related Axis Label equal to the sum of Column B + Column C (Base Value + Element Values). Enter the same value for the cell beneath.
Step 2: Chart data and adjust
1. Select your data, here A2:H6, and go to Insert>Charts>Column>2-D Column>Stacked Column>OK (to exit). Your chart should look like this:
2. Switch column and row data by right-clicking within the chart and going to Select Data…>Switch Row/Column>OK (to exit). Chart should now look something like this:
3. The top colored column element in each column (purple, aqua, orange and baby blue, respectively) is what will become that Element Value’s connector. To convert to these columns to connectors, in turn, right-click on the series (the first one is the purple column element) and go Change Series Chart Type…>Line>Line>OK (to exit). Repeat this process for the other connector column elements (aqua, orange and baby blue). After this step your chart should look like this:
4. Follow this up by formatting each connector in turn. Right-click on the connector and go Format Data Series…. Consider making the Line Color>Solid Line>Color black, Line Style>Width .25 pt and Line Style>Dash Type>Square Dot. Play around with these options as you see fit to get the best look. Again, do this for each connector.
5. Remove grid lines (optional), delete the Legend (necessary). Your chart should now look like this:
6. Go into you Base Values series (blue column element in the chart above) and eliminate the color fill and borders: right-click on a blue column element and go Format Data Series….>Fill>No Fill and Border Color>No Line>Close (to exit).
7. Format your Element Values series (red above, using same process in Step 6 to change the fill color and add a border.
8. Right-click on your Label Spaces series and go Add Data Labels…. Don’t worry about the value on the labels for now, they’ll be changed in the next step. Follow this up by formatting the Label Spaces series just like how the Base Values series was formatted (in Step 6). Make it so there is no fill and there are no borders. This is what you should now have:
9. All that remains is to convert your labels to the values of the Element Values. To do this for each label: click on the specific label twice (so that only the box for that label appears, as below).
Click a third time on the edge of the box that appears and then type the equals sign “=”. Now go back to your data table and click on the cell of the Element Value that you want appear in the label. Then press enter. This links the value of the label to the Element Value (if your Element Value ever changes so too will the text in this label). Repeat this for the other data labels in turn. The result is your Better Waterfall Chart:
Download the Waterfall Chart Template:
Please download the waterfall chart template from here [.zip version here]
Final Thoughts
I hope you will agree that this waterfall chart is more visually appealing that the examples at the start of this tutorial. In addition to a more professional look this waterfall will fully update (step heights, labels, connector positions) automatically whenever you change your Element Values. While the process of implementing this form of waterfall chart may at first seem cumbersome it can be quickly implemented with some practice and is a great item to have in your charting toolkit. Enjoy.
Note from PHD:
Thank you so much Aaron. You have taught us a very valuable tutorial. I really appreciate your effort in putting this together.
If you need to make a lot of waterfall charts, I recommend trying Jon Peltier’s Waterfall Chart Utility.
Hello there, Reader: If you like this waterfall chart tutorial, please drop a note of thank you to Aaron through comments.
http://feedproxy.google.com/~r/PointyHairedDilbert/~3/iRbTpGk-Bos/
We talk alot about making better charts and perils of bad charts here.
I want to know what you usually do when a co-worker or boss makes an ugly chart?
You tell them the chart sucks
You gently point out the mistakes of their chart and tell them some nicer and cooler ways to tackle it
You stay calm and send them an e-mail later (may be with a link to PHD chart pages or something like that)
You don’t care (and may be continue doodling)
Any other
Vote your option using comments.
As for me, I once saw my boss make an ugly chart, take a print screen of the excel window with that chart, paste it in the powerpoint and present it to a whole room of project sponsors. I just gritted my teeth and continued doodling because she is a difficult person to deal with.
Happy weekend
http://feedproxy.google.com/~r/PointyHairedDilbert/~3/VRzwyJLDa18/
Okay, I am trying to build something hush hush super cool using excel but hit a roadblock trying to load images from a URL to my excel sheet.
What is the problem?
I am trying to insert an image from a URL to my excel 2007 workbook using VBA. The code I have used is,
Dim pPath
pPath = range("picLocation").value
ActiveSheet.Pictures.Insert(pPath)
When I try to run it, the error is “Unable to get the Insert property of the Pictures class”
Now, after googling I bumped on this piece of code recommended to fellow VBA coders who had the similar problem.
With ActiveSheet.Pictures.Insert(pPath)
.Left = range("a1").Left
.Top = range("a1").Top
End With
Well, I tried that, but still the same error. After spending what seemed like a couple of hours I am turning to you.
Do you know how to insert images (from URLs) to excel workbook using VBA?
Paste your code in comments. Lots of love and admiration is awaiting…
http://feedproxy.google.com/~r/PointyHairedDilbert/~3/aTxPNGqZMEs/
Michelle, Who is a sweet lady and regular reader of the blog sent me this question via e-mail. (aside: why is she a sweet lady? Because she saw the new cell post and sent me pics of her son and told Jo and me are about to encounter most amazing experiences of our lives )
I handle the invoices we give to our customers (we build and sell furniture), but sometimes such customers can be stores or just regular folks that come directly to us.
For the stores I have to give an invoice that has a NCF number which is a tax related number that increases the cost in 16% but stores require this number which must be unrepeatable. Each company has a “list” of numbers that they can provide their customers. Regular customers (not stores) have no interest in this number, obviously trying to avoid the 16% increase in the price!
So far what I did was create a database of our customers with the basic info of each one. Many of our customers keep buying so this way I just have to place myself in the invoice and select from a list and with a simple vlookup I get the rest of the customer’s information: phone, address and RNC number.
Now… the RNC is the number that tells me if it’s a store or not. Stores must have their registration number (RNC) in order to receive invoices with NCF (boring, I know… I’m gonna get to the point in a sec). Ok, so if I choose my dear customer “Chandoo Enterprises” and this store has RCN, then I automatically need for the invoice to pull form “my list” of government assigned NCF numbers, the next one in line. If in my previous invoice (say invoice 1455) to another store I used NCF number A010010010100000002 (that’s how they look) for Chandoo Enterprises -invoice #1456- I am going to need the next number in line A010010010100000003 and so on. Now, say that my next invoice (1457) is not to a store but to John Doe, for him I don’t want an NCF number to show.
I keep thinking that there’s a very stupid and simple way to do this but I just can’t nail it!! My problem is to get to a formula that will work only when the RNC field has information and therefore avoiding NCF numbers to appear on non-store customers (not so complicated I think) and that it chooses the next NCF number in line, no repeating… this is the part that I have no idea how to do!! Is there a way to solve this without using VBA? VBA is scary!! Hehehe
Okay, that is a big question, but may be we can come up with a small solution for it.
If I understand it correct, Michelle is looking for something like this:
So, in order to generate such numbers,
First we will make 2 named ranges - customer_list and ncf_numbers. You know what they mean.
Now, we will enter the customer name in column B, and based on that we will fetch RNC number in column C.
For this, we will use VLOOKUP () formula. The simplest formula looks like this: VLOOKUP(B5,customer_list,4,FALSE)
But, simplest formula also has problems - it doesn’t handle errors and can return 0 when the RNC number field is blank. So we will add some fat to it, mainly on the front side. It now looks like this: =IF(ISERROR(VLOOKUP(B5,customer_list,4,FALSE)),”", VLOOKUP(B5,customer_list,4,FALSE))
In the third column, we will fetch the next available NCF number if the customer has an RNC. For this, we use a simple IF() and COUNTIF() formulas along with INDEX() formula. We write: =IF(OR(C5=”",C5=0),”",INDEX(ncf_numbers,COUNTIF($C$5:C5,”RNC*”))).
Above formula simple fetches the nth NCF number from the named range ncf_numbers if RNC number is not blank.
That is all. We now have a simple logic to generate invoice numbers that suit tax authority’s whims and fancies.
Download the example workbook:
You can download the excel tutorial workbook and see how you can generate such invoice numbers yourself.
More material if you are getting stuck with formulas
50+ Excel Formulas - Explained in plain English, Excel formula tutorials, Excel array formula examples
http://feedproxy.google.com/~r/PointyHairedDilbert/~3/aMVB6hmgC8E/
Cheryl asks via e-mail,
I was wondering if you could help me figure out how to combine the round formula with the rank formula? I need to first round all the numbers and then rank them. I have tried various ways to do this but they aren’t working properly. I really don’t want to add a column to round and then rank because I need to do this round/rank many times for this particular spreadsheet. There are also merged columns which makes it even more challenging. Before I go through the painstaking efforts to insert columns to round, I thought I’d check with you.
Of course, just as any other excel formula problem, we can solve this with … strong coffee.
Well, I am kidding, but coffee along with array formulas seems to help.
How to write an excel array formula to round and sort a list?
Assuming the data is in range C3:C12, and B3:B12 has running numbers from 1 to 10, the below array formula,
=SMALL(ROUNDDOWN(C3:C12;0);B3:B12)
when entered in the range D3:D12 will round and sort the range of values in C3:C12.
Remember, once you type the formula, press ctrl + shift + enter to make it an array formula.
More resources: Learn excel array formulas | Online Help on Excel Formulas
http://feedproxy.google.com/~r/PointyHairedDilbert/~3/S38PbXxGJuU/
We have received a chart for chart busters that required some fixing. I thought, this will be a fun exercise for you. So here it goes,
…column chart that shows daily, weekly or monthly data depending on the user’s choice. In daily the columns are displayed properly, but in weekly & monthly mode the columns are a fraction of the width they should be - why, and how can this be avoided? Bonus points if you can describe how to use an INDIRECT formula on the x-axis labels which is another problem I ran into whilst creating this mockup!
You can download the workbook from here.
Here is how it is looking:
Go!
Thanks Gordon for asking this question.
What is in it for you?
Eternal glory and a 10% discount on the Learn excel formulas - Lightning fast e-book.
Lear more about making better charts using these chart busters examples:
Asset Allocation Charts - Done the right way
Calorie chart - How much you should exercise for what you eat - fixed properly
Learn how to make dynamic charts:
Excel dynamic charts using data filters
Dynamic charts using INDEX() formula and Camera tool
Using scroll bar form control to make dynamic charts
http://feedproxy.google.com/~r/PointyHairedDilbert/~3/jxu26BsSUQo/
This is the first installment of Chart Busters.
Chart busters is a new series of posts on PHD and Jon Peltier’s blog. We take turns to exterminate bad charts and associated evils. Although our proton packs are still not perfect, together we are confident of tackling most ghosts trapped in bad charts.
In this installment we take a look at Asset Allocation Chart that looks like it is hexed.
The bad chart
Our reader DMurphy submitted a poorly made asset allocation chart,
If you are looking for an early contender, here’s one which came in to my wife from her Pension company this week showing (or at least attempting to show) the make-up of her investments.
http://www.box.net/shared/goynhfo7zb
The above image is an excel reconstruction of even sadder looking chart.
What is wrong with it?
Poor chart selection: Pie charts are good for 3-4 data elements. When we need to present 10 or so items, it is better to use a bar chart or a line chart.
Not grouping and sorting the information: In the first chart which is displaying Asset Allocation is made from data that has 3 different series - Bonds, Equities and Other. But the chart shows everything in the same way, thus making it difficult to understand how assets are allocated to various classes of investment. Also, the data is not sorted in any meaningful order.
Poor use of labels: The labels A,B,C … are non descriptive. They are also repeated on the other chart although they mean different things.
The Chart Busters’ Fix
Thanks to guest parachartanalyst Joe Mako, who contributed this fix:
I have taken Joe’s ideas and slightly modified them to create the below chart
Click here to download the above fix in excel and see it yourself.
What we have learned?
Zombies are scary, even when they are looking like donuts.
Always try to sort the data in some meaningful order before pushing it to charts
Use a variation of panel chart or color the series sensibly to bring out key differences
Try to avoid generic labels like 1,2,3 or A,B,C and instead use the actual values and category names
How would you have tackled this?
We dont know how open source the ghost busters were. But Chart Busters are 100% open source. Share your ideas and suggestions for improving this scary little chart to something that makes sense.
When ya gonna call…?
Consult chartbusters today. Send us your bad charts. All you have to do is fill out this google form.
Arent ya gonna read these… ?
What to do no when no one likes your pie | Non sucky excel chart templates
http://feedproxy.google.com/~r/PointyHairedDilbert/~3/kTIgOx6C7YE/
Ok, this is *totally* exciting. I am big fanboy of Garr Reynolds of Presentation Zen. His blog is one of the very first blogs I have started reading and I still read it whenever there is a new post. Few days back I saw on his blog that he is coming down to Malmo, Sweden for a keynote presentation at FBTB Conference.
My first reaction?
I went to the FBTB site to find out if I can attend the conference. But I learnt that the sessions are on Thursday and Friday (I am working on those days) and they cost 1,100 euros (gulp…).
But I thought… “may be I can get a chance to meet Garr and probably interview him”
So today morning I emailed him and asking if he can give me 15 minute interview. And guess what…
So here is your chance to share my excitement and ask a cool question to Garr.
The interview theme is “Charting Zen: Designing Better Charts and Telling Interesting Stories”
Drop a comment or tweet me @r1c1 with your questions. Hurry!!!
PS: Watch Garr’s presentation @Google on youtube or read his Presentation Zen book to get a feel of his work.
http://feedproxy.google.com/~r/PointyHairedDilbert/~3/gE8Zsp6FJaU/
Ok, so you have that neat little excel report ready and now you want to share it with your boss or client in a PDF format. So how would you do that?
The best option is to upgrade to SP2 of Microsoft Office 2007 (download the service pack 2 from office update) and then use the “Save As PDF” feature provided by MS.
The next best option is to install primo pdf. It is a free software that will create a new printer called PrimoPDF. You can print excel files to this printer and then it creates PDF files and places them in a location of your choice. I like Primo PDF alot and use it from Office 2003.
The third option is to install OpenOffice.org and open the excel file there and use the save as PDF options. This can be a little messy as openoffice tends to spoil the excel look and feel when you open the files in openoffice calc application.
The fourth option is to use one of the online conversion tools (they often messup the layouts) like Doc2Pdf. The annoying part with this service is that, you need to tell them your email id so that they can mail you the PDF when it is ready. There are few services where you can download the PDF online.
The fifth option is to upload the excel file to google spreadsheets and use the export to PDF feature of google spreadsheets. This is good if your excel file has very little formatting and not really complicated. As google spreadsheets doesnt have lots of features that are in excel, you might see broken content, alignment and formatting when you try to open an excel file there.
If you are using MS Office on Mac, then you have it easy. Just hit the print button, select the PDF option and you have the PDF ready.
Have I missed any Excel spreadsheet to PDF conversion methods? What is your favorite way to convert spreadsheets to PDF?
Related: Excel productivity secrets - part 1 & part 2
http://feedproxy.google.com/~r/PointyHairedDilbert/~3/cKJFX90YqIg/
This week we are celebrating Your Week @ PHD. That means you get to read the excel tips shared by other readers of this blog.
Unhide all the sheets using simple macro by Kat
My single favourite simple macro ever - to fill in the gap that Excel leaves. Unhiding -all- hidden tabs in a workbook at once. I install it in the personal.xls workbook, and save myself hours of clicking.
Sub Unhide_All_Sheets()
Dim wsSheet As Worksheet
For Each wsSheet In ActiveWorkbook.Worksheets
wsSheet.Visible = xlSheetVisible
Next wsSheet
End Sub
A KPI Dashboard using VBA and Charts by David
After learning a whole lot over the past few months from this site and others (so many I can’t even remember right now - but I will make a list soon of where I found things), I constructed a KPI spreadsheet (see link below). This spreadsheet allows our institution to create standardized KPI reports for university consumption.
I attempted to keep the colors muted even though I chose school colors (from the publishing guide) for the actual graph.
Features
The chart is dynamically configured in numerous ways. The user can control the title (via cell entry on left), color of bars (via color of data labels), number format (via number format of the first data cell), and the display and printing of the trend line. The KPI name comes from the sheet, and the vertical axis is determined based on the data (I find the maximum value and divide by 4).
To-dos
I would like the user to be able to enter new descriptive items via a form with the option to include variables (KPI_Name, KPI_Category, etc.). I would like the user to be able to include more than one chart on a page (some KPIs actually need to track parts of the whole).
I am sure there are more features I will think of as time goes on but I wanted to let others see this and hopefully be able to incorporate it into their/your own work. The file is available here: http://dl.getdropbox.com/u/749941/KPIs_PHD.xlsm. Hopefully I will be starting a blog soon to talk more about what we are doing here with excel and other products.
Using Find Dialog to Solve a Tricky Problem by Christy Lee
Introduction:
On a project I recently worked, we crunched several hundred (about 400) rows of data. The creator of the original document did not have any way to foresee the life this project would take on! So…there was only one field for ‘Name’ which contained the names of the team members for the corresponding step of the overall project.
Challenge:
As the project progressed, an individual may be added to multiple task teams. So, your name might be one of three in four records, one of ten in fifteen records, etc. Also, the team members could be added on the fly…you see how the complications arise quickly! Oh, and the project was run on three continents in four countries….
Each person was responsible for updating their pertinent information. Because of the complexity of that one name cell, filtering and sorting became cumbersome.
Solution:
Hide all rows except for the header row.
Do a search on your name (fortunately, in about three dozen team members, we had no duplicate last names!)
When the search results dialog comes up, select all of the records (select first, shift+select last)
Go to Format>Row>Show.
Whoo Hoo! There are all (and ONLY) the records that belong to you.
Array Formulas to the Rescue by Rajinikanth
This is the formula to find out Employees first login time and last logout time for the day.
Example : Suppose employee table is starting form Column B
then the table looks like :
Name Code
1001 rajinikanth
1002 srinivas
1003 vardhan
and the Login Data is starting from Column G in a sheet
Then The formula for First login :
{=1/MAX((B8=$G$7:$G$15)*($H$7:$H$150)*(1/$H$7:$H$15))}
and the formula for Last Logout :
{=MAX(($G$7:$G$15=B7)*($I$7:$I$15))}
A Big Warm Lovely Heartfelt Thank you to Kat, David, Christy and Rajinikanth. You are truly wonderful.
Be a part of the “your week” @ PHD
Come, be part of the your week celebrations at PHD. Click here to submit your excel tips. Your tips will be shared with all our readers during this week (May 11-15, 2009)
PS: If you have already shared your tips and not seeing them in this post, don’t worry. I am posting only a few everyday, so yours will be in the next 3 posts.
http://feedproxy.google.com/~r/PointyHairedDilbert/~3/Bkzwx7v2b5U/
First let me give you some excuses for not posting regularly.
Last month has been very hectic due to sudden travel to Sweden from Chennai. I could hardly manage a handful of posts. Moving to a new place meant I could find very little time to respond to reader mail. I am sorry if you have sent me an email and didn’t get a response. Thankfully, I have managed to carve out a new posting schedule since last week and I will be sticking to 3-4 posts per week going forward.
Now time for some good news. Our twitter follower base has grown to 3 digits. Yes, R1C1, PHD’s twitter presence has a hundred followers. If you are in to birds or micro-blogging you should probably follow me over there.
Moving on to this week’s excel links,
Using various shades to depict uncertainty in predictions
In a fine charting tutorial, Peltier teaches us how to show uncertainty in predictions using shaded lines. Even though I have never seen charts like this before, I think this is one of the coolest ways to use area charts. Go check it out for yourself.
Vlookup on 2 columns
Dick shares with us 3 techniques to lookup in 2 columns at a time. I prefer using either vlookup with helper column in the beginning or index with helper column in the end.
Spreadsheet Barbecue Grill, Really!!!
Finally a barbecue grill that looks like a spreadsheet. No comments.
NodeXL - Visualizing Large Social Network’s Data in Excel
NodeXL is an excel 2007 template for visualizing social networks. It is a collaboration between Microsoft Research, University of Maryland, Telligent Systems, Cornell, University of Washington. The project has several cool visualizations and comes handy when you need to visualize a large network of data (for eg. hierarchical relationships in a large, complex organization)
Questioning Data to Ink Ratio(opens a PDF)
In this beautiful and insightful newsletter, Stephen Few questions the “data to ink ratio” and suggests that we must raise our voice (ie use more ink) if needed.
Excel Dashboard and Visualization Bootcamp
Jon Peltier and Mike Alexander (of DataPig Technologies) are conducting a Excel dashboard and visualization bootcamp between May 20 and 22. It is in Frisco, Texas. If your job involves considerable amount of dashboard creation and visualization, you might want to attend a workshop like this and sharpen your skills.
Do you want to share a cool excel or charting related link ?
If so, drop a comment or send us the details at chandoo . d @ gmail. com. Emails from readers like you make me very happy.
Have a great week everyone
http://feedproxy.google.com/~r/PointyHairedDilbert/~3/xKGkRSTHzRM/
“Trial and Error” has to be the most effective ways to learn something new. Most of the stuff I learned in my life is through that. Be it insurance or excel or programming. My learning has always gone up when I make a mistake. I am sure most you agree with me…
So in this installment of spreadcheats we will learn about errors. Those annoying #SOMETHING!s that you see when your excel formulas have something wrong with them.
#DIV/0! Formula Error
This is the easiest of all. When you divide something with 0, you see this error. For eg. a cell with the formula =23/0 would return in this error.
How to fix #DIV/0 error?
Simple, do not divide the value by Zero. You know the answer anyway.
#NAME? Formula Error
The most common reason why you see this error is because you misspelled a formula or named range. For eg. if you write =summa(a1:a10) in a cell, it would return #NAME? error. There are few other reasons why this can happen. If you forget to close a text in double quotes or omit the range operator :. All these examples should return #NAME? error. =sum(range1, UNDEFIED_RANGE_NAME), =sum(a1a10)
How to fix #NAME? Error?
Make sure you have mentioned the correct formula name. If you are using excel 2007, when you are typing the formula excel shows all the matched formulas. In earlier versions of excel, if you use correct formulas, they will be automatically capitalized. For eg. if you type =sum(1,2,3) in a cell and press enter, it will be changed to =SUM(1,2,3). You can use this feedback to correct formulas.
Make sure you have defined all the named ranges you are using in the formula.
Make sure any user defined functions you are using are properly installed.
Double check the ranges and string parameters in your formulas.
#N/A Formula Error
This is one of the frequent errors you see while using vlookup formula. The N/A error is shown when some data is missing, or inappropriate arguments are passed to the lookup functions (vlookup, hlookup etc.) of if the list is not sorted and you are trying to lookup using sort option. You can also generate a #N/A error by writing =NA() in a cell.
How to fix #N/A error?
Make sure you wrap the lookup functions with some error handling mechanism. For eg. if you are not sure the value you are looking is available, you can write something like =if(iserror(vlookup(…)),”not found”,vlookup(…)). This will print “not found” whenever the vlookup returns any error (including #N/A)
#NULL! Formula Error
This is rare error. When you use incorrect range operators often you get this error. For eg. the formula =SUM(D30:D32 C31:C33) returns a #NULL! error because there is no seperator between range 1 and range2.
How to fix the #NULL! error?
Make sure you have mentioned the ranges properly.
#NUM! Formula Error
This is number error that you see when your formula returns a value bigger than what excel can represent. You will also get this error if you are using iterative functions like IRR and the function cannot find any result. For eg. the formula =4389^7E+37 returns a #NUM! error.
How to fix #NUM! error?
Simple, make your numbers smaller or provide right starting values to your iterative formulas.
#REF! Formula Error
This is one of the most common error messages you see when you fiddle with a worksheet full of formulas. You get #REF! error when one of the formula parameters is pointing to an invalid range. This can happen because you deleted the cells. For eg. try to write a sum forumla like =SUM(A1:A10, B1:B10, C1:C10) and then delete the column C. Immediately the sum formula returns #REF! error.
How to fix the #REF! error?
First press ctrl+Z and undo the actions you have performed. And then rethink if there is a better way to write the formula or perform the action (deleting cells).
#VALUE! Formula Error
Value error is shown when you use text parameters to a function that accepts numbers. For eg. the formula =SUM(”ab”,”cd”) returns #VALUE! error.
How to fix the #VALUE! error?
Make sure your formula parameters have correct data types. If you are using functions that work on numbers (like sum, sumproduct etc.) then the parameters should be numbers.
###### Error
You see a cell full of # symbols when the contents cannot fit in the cell. For eg. a long number like 2339432094394 entered in a small cell will show ####s. Also, you see the ###### when you format negative numbers as dates.
How to fix the ###### error?
Simple, adjust the column width. And if the error is due to negative dates, make them positive.
Further Reading on Excel Formula Debugging
Formula Debugging using F9 Key
Learn to work with Circular References - Tutorial on Generating Timestamps
Understand the difference between absolute and relative references
Tell me how you debug formulas? What is the most common error you get?
This post is part of our Spreadcheats series, a 30 day online excel training program for office goers and spreadsheet users. Join today.
http://feedproxy.google.com/~r/PointyHairedDilbert/~3/NComY-jbASs/
Making charts is one of the most common use of Excel or other spreadsheet software. But do you know a simple trick that can save you lot of time while using excel charting features?
Chart Templates or User Defined Charts
yes, using chart templates can save you a lot of time.
If you use a particular type of chart or formatting all the time, you can save all the steps involved in making the chart by using templates.
Here is a simple tutorial on using chart templates in excel.
1. Prepare your chart
First step is to to prepare a chart that you would like to save to template. The chart can be a formatted version of one of the typical excel charts or a more complex combination chart.
2. Now save the chart as a chart template
In excel 2007 you can do this by selecting the chart and going to design tab in the ribbon and clicking on “Save as template”
For earlier versions of excel, right click on the chart and select chart type and go to “custom types” tab. Select “user-defined” as the chart type and click on the Add button to add the chart to excel chart templates.
3. Use the chart templates
Next time you need to insert a chart, use the templates and save time.
In Excel 2007 use the templates option. In earlier versions, use custom types to find your already save templates.
Bonus tip: Moving Chart Templates from One Computer to Another
If you want to move all your chart templates from one computer to another, just go to My Documents \Application Data\Microsoft\Excel and copy the file XLUSRGAL to the other computer. Make sure you are not overwriting the existing XLUSRGAL file, but just add the sheets from one file to another.
If you are using Excel 2007, the chart templates are stored as *.crtx files. Just locate them and copy to target system. Usually they can be found in \AppData\Roaming\Microsoft\Templates\Charts for Vista and My Documents \Application Data\Microsoft\Templates\Charts for XP.
Free Excel Chart Templates to Get you Started
And here is a huge list of beautiful excel chart templates, around 73 of them. Download and use them free. Get even more in our excel downloads page.
This is part of our Spreadcheats series, a 30 day online excel training series for office goers and spreadsheet users. Join today.
http://feedproxy.google.com/~r/PointyHairedDilbert/~3/tR7VzCWlSdY/
Japanese Candlestick Chart or Candlestick Chart as they are popularly known are one of the most commonly used stock charts.
According to Wikipedia, a Japanese Candle Stick Chart is,
Candlesticks are usually composed of the body (black or white), an upper and a lower shadow (wick). The wick illustrates the highest and lowest traded prices of a stock during the time interval represented. The body illustrates the opening and closing trades. If the stock closed higher than it opened, the body is white, with the opening price at the bottom of the body and the closing price at the top. If the stock closed lower than it opened, the body is black, with the opening price at the top and the closing price at the bottom. A candlestick need not have either a body or a wick.
Today we will learn how to make a candlestick chart in Microsoft Excel in 4 simple steps. For our purpose, we will plot candlestick chart for Apple stock prices between Jan 26 and May 02.
1. First get the stock price data
You can get stock price details - open, high, low, close prices for the stock chart from anywhere. I have used google finance (here)
The data should be in this format for us to make the chart:
2. Insert stock chart
Select your data and launch insert chart dialog and select “stock chart” as type and “Open High Low Close Chart” as sub-type.
This will insert a chart like the one shown below.
However there is a problem with this chart: excel leaves blanks when stock market is on leave (for eg. weekends) so,
3. We change the axis type from time-scale to category
Right click the chart and select chart options and in the “axes” tab change the category axis type from “automatic” to “category”. This ensures that excel treats dates as categories instead of times and thus removes the blanks.
But when we do this, excel reverse the dates, thus your new chart would read from 02 march to 26 jan instead of the otherway around. To fix this, select the category axis, and check the categories in reverse order and value axis crosses at maximum category options. This sets the date order correctly.
4. We are almost done, now format the chart
Adjust axis scaling options, grid lines etc and you have a Japanese candlestick stock chart ready.
Download the Japanese Candlestick Chart Template
Go ahead and download the Japanese Candlestick Chart Tutorial workbook and use the template to make stock charts.
More Stock Charts using Excel
Sign up for e-mail updates or RSS Feeds to learn how to make more stock charts using excel as they are posted.
http://feedproxy.google.com/~r/PointyHairedDilbert/~3/OdSn20gSotY/
We are a big advocate of keyboard shortcuts. I think learning a handful of keyboard shortcuts can improve your productivity tremendously, whether you are using Excel or Firefox.
But using only keyboard shortcuts is like using only right hand. You can only do so much with one hand.
So, we have compiled 5 incredible ways mouse can help you be productive while using MS Excel.
Adjust Column Widths
This is my favorite. Select the columns you want to adjust width for. Double click on the column number separator line. The column width is adjusted automatically. This is a very useful way to format your tables. See the screencast aside:
You can use the same technique to adjust column widths.
Select Non-Contiguous Ranges using Ctrl+click
Ctrl+Click to select non-contiguous cells. Useful when you need to format cells matching a particular criteria or paste only few cells.
One click stock quotes with Alt+click
To get stock quotes in excel, enter a company stock symbol in the cell, alt+click on it to launch research pane. (works excel 2003 and above). Find out more.
Move selected cells by dragging the thick black line
First select a bunch of cells, now drag the thick boundary line to move the cells. Better and faster that ctrl+x, ctrl+v.
Auto fill by dragging cells
Ok, this is well known, but just in case you don’t know: You can auto fill a range with sequential numbers, dates, days of week, months etc. by entering first few values and then selecting them and dragging the range by clicking & holding the bottom right corner. When you do this excel will automatically fill the rest of cells with data based on what you are entering.
You can also customize excel lists so that you can auto-fill, lets say bank holidays in your country or types of beer in your pub. One more auto fill trick.
What is your favorite mouse based productivity hack?
Share with everyone using comments. Teach me a new trick and show that mice can indeed kick butt.
This is part of our Spreadcheats series, a 30 day online excel training series for office goers and spreadsheet users. Join today.
http://feedproxy.google.com/~r/PointyHairedDilbert/~3/GUg0y0-wpyU/
This blog has been a source of inspiration, learning and friendships to me. In the last few months I have met several interesting and unique personlaities, thanks to the blog. But knowing Robert has been one of the most enjoyable experiences.
If you are new to this blog, you probably don’t know who he is. Robert is a very exprienced excel user and the author of Executive Dashboards posts on PHD - a 6 post series describing how to create excel based dashboard reports.
He is very passionate about Excel, Data Visualization and Making information simpler and clearer. His dashboard related posts on this blog reflect all these things and showcase some of the best methods to create excel based management dashboards.
The good news is that recently he started a blog - Clearly And Simply that talks about information dashboards, data visualization, topics that he is passionate about.
I wanted to take a moment and recommend the blog to you all since he covers very relevant topics in visualization and information communication areas. For eg. take a look at the take care of customer care article, where he talks about building a performance dashboard for understanding how your customer service is doing. Or look at there is more than one way to heat a map article, where he provides beautiful ways to create heat maps in excel using VBA.
Robert’s blog has a very mature tone and is backed by all his experience. While some of the topics may be too advanced for our readers, it is certainly a blog to follow if your job involves fair bit of information visualization or you are a dashboard enthusiast like me.
I wish Robert all the best with his new blog, and I am hoping to see more quality articles from clearlyandsimply.com
PS: PHD doesnt post blog reviews usually. But Robert is an exceptional person and his blog, I am sure ,will be an exceptional source of inspiration and ideas for you and me.
http://feedproxy.google.com/~r/PointyHairedDilbert/~3/gJg5YavbTGk/
So you have built that spreadsheet report your boss wanted. And you were all eager to use it in your presentation. But in the last minute, your boss asked you to change average sales to total sales figures (or measure growth wrt to 2006 instead of 2007). You also want to grab an espresso before rushing to the meeting. Now what?
Simple, skip the coffee.
Of course, I am kidding, who would skip coffee for a bunch of formulas ? So, we use Find / Replace to do the dirty work while we let the roasted beans restore sanity. Just press Ctrl+` (it is the key next to 1), this will enable formula view. Now press Ctrl+H and change the spreadsheet formulas or input range en masse.
What are you still waiting for? Go get that espresso!
Do you know that you can use find / replace to change spreadsheet formatting too ?
This post is part of our spreadcheats series - Learn Excel in a month.
Excel Formulas Too Confusing ? You should get Excel Formula Help E-Book
http://feedproxy.google.com/~r/PointyHairedDilbert/~3/JDciX6aVxis/
Over the weekend I could get sometime to cleanup few of the pages on the blog. Checkout the free excel spreadsheets page, we now have almost 30 excel spreadsheet files for download neatly arranged in to areas like Top 5, Latest 5, Excel Chart Templates, VBA, Excel Apps. Check out the page yourself and download a file or two.
Ok, moving on to this weeks excel links from around web.
Note to new readers: Every week (ok, not really every week, but almost) I share few excel, charting, presentation related links that can help you become more productive and successful.
Excel to Twitter, now in Add-in Format
JP at Code for outlook and excel blog has taken one look at my excel from twitter workbook and immediately felt that, an add-in is a cool way to extend it. Being the compulsive VBA programmer he is, he just did that and posted the add-in for download at his blog. While there, also checkout his random sample data generator add-in, might come handy when you need to generate sample data.
Bars are good, only when focus + context are taken care
Jorge at his charts blog touches a very relevant and important issue. Often when you have more data, we plot bars without really emphasizing on focus and context. It is important to plot your data, but it is more important to highlight the key values and bring context so that readers will know the story better. He suggests using accordion charts, something that I have been experimenting in the last few weeks. check out more at Jorge’s site.
Use mouse to paste as values
Debra shares this wonderful little mouse based shortcut to paste copied values as cell. Just select the values you want to copy, right click on the border and drag the range to where you want to paste while holding the right mouse button depressed. When you are ready release mouse button and select one of the options shown (copy here as values only). You can do this and much using excel’s paste special features too.
Simple Expense Tracker using Excel 2007 Features
Monica, a software engineer in Excel team at MS tells us how you can build an expense tracking application using Excel 2007’s features like SUMFIS formula.
What would you do if you do not have excel 2007 or not want to use SUMIFS ? Well, you can build an expense tracker using Google docs.
Leaving the door open could cause excessive CPU load
Dick at Daily Dose of Excel discovers that leaving few ADO data connections open in the VBA code could lead to excessive CPU consumption and slowing down of every app including Excel. Make sure you are not doing the same mistake in your vba / macro code.
http://feedproxy.google.com/~r/PointyHairedDilbert/~3/UJ7WO4qcdUE/
January 2009 will be one of the most memorable months since I started blogging. It is a perfect example of how uncertain life is. The month started with me receiving MVP award, then the website went down due to excessive server load and then we crossed 2000 RSS subscribers milestone and celebrated it with a huge 100 excel tips post. It just shows how uncertain things can be.
We had 105k page views from 47k visitors in Jan 2009 despite the server outages. Our e-mail subscriber base crossed 500 members and total RSS subscriber base is now at 2040. There were 19 posts and 247 comments (wow) in last month. I am really thankful to our readers. You constantly motivate me to learn and share beautiful things about excel and charting. The journey is becoming more and more enriching each day for me. I hope you are enjoying the ride as much.
Here is a list of 5 best posts from Jan 2009, in case you have missed out the whole month or new to this blog, check this posts out to make sure you have squeezed enough from this blog.
- Automatically Insert Time Stamps in Excel [12 comments]
- Excel Formula Wish list [25 comments]
- 6 ways to stack your column charts [17 comments]
- Hide worksheet tabs in excel [14 comments]
- 100 Excel Tips & Resources [10 comments]
PHD needs your help, Please
Please take a moment and help me spread this blog. You can easily help by just saving this blog to your technorati favorites or
http://feedproxy.google.com/~r/PointyHairedDilbert/~3/sNgye4ZG_3M/
Remember the PHD Excel Formula Helper Tool? Good, now it is available as an e-book, so that you can take a print out of all the formulas or save it on your disk and learn formulas at leisure.
Click here to buy your copy today. It is just 5 dollars.
What is in this book?
The book has 51 pages, each explaining one Excel Formula in plain English. The page contains a simple layman definition of what the formula is about, easy to understand syntax and 1 or 2 examples. The pages are in high resolution so that you can either print or view the e-book and read comfortably.
See a sample page here:
You can access a Free low-resolution version of this e-book contents here: Excel Formula Help
What is not in this book?
This book doesn’t provide extensive help for excel formulas. Duh! it doesn’t even provide medium level of help on formulas. It is for those of you who are not sure what excel formulas are or how to use some of them without getting stuck. It lets you know what is out there without confusing you up. It lets you learn excel formulas so that you can use them with confidence. But excel formulas are a huge area and there is so much more to learn. This book opens the doors for you.
So what are you waiting for, go ahead and buy the e-book and use it to learn formulas fast. It is just 5$.
Dude, the book is 5$, what the heck ? You have a free version online
Yeah, that is the point. The free version is there so that anyone can learn. But no body can take a printout of this and stick at their desk or give it to the new colleague at work. That is why the e-book is launched. For 5 dollars you can buy this and have the file with you so that when you are stuck with a formula, you can quickly open and learn it.
So go ahead and buy it.
or, don’t buy it and browse the free version : Excel Formula Help
OK, now the fine print
Is there a return policy?
yes, yes. Feel free to return the file in the first 3 days saying it sucks or it didn’t help you learn excel formulas or whatever. I will return the 5$ to you. Since the file is merely a large resolution PDF of the free version available online, I wont be able to provide returns after you use this book for 89 days. That would be very bad for my bank balance.
Can I like buy one copy and take 100 printouts and distribute to my whole class or office floor?
Now, there is really no way I can stop you from doing that. Lets be frank, I ain’t no batman or big brother. I am just a small guy running a not so lame site trying to not screw up things. So, go ahead and take 100, or wait, take 1000 copies and send it your friends or colleagues. Just tell them how nice chandoo.org is and how useful it has been for you. Just don’t resell it.
Why I am doing that, because I am cool like that. Now go and buy the book before I add some weird copy right policy with 95 page agreement.
This is the first time I am selling anything
So, there is a slight chance that I might have made some mistake despite doing mock buys using my wife’s credit card etc. Just drop a comment and let me know if you see some glitch. The purchases are handled by good folks at paypal and e-junkie. Soon after the purchase you will be sent a link for downloading the file. You will also be shown that link on the payment confirmation (thank you) page.
Thats all. Now, go ahead and buy it or checkout the free Excel Formula Help version before making the leap.
http://feedproxy.google.com/~r/PointyHairedDilbert/~3/v_e9xjnbq74/
This post is part of our spreadcheats series - 30 Excel and Charting Tips & Hacks aimed to make day to day spreadsheeting fun and productive.
One of the most common charts is “YoY Growth Rates of Our Superawesomekickass Product”. While not everyone may have a superawesomekickass product, most of us in fact make a YoY (Year on Year) growth rate charts for various types of data.
A simple label formatting hack can improve the effectiveness of these charts by adding color to differentiate positive vs. negative growth (or mediocre vs. sky rocketing growth rates). See this example:
You can get this effect by applying custom number formatting to the data label. It is one of my favorite tricks in excel and I have written about it in introduction to custom cell formatting in excel, number formatting in excel using custom codes, showing decimal values only when needed articles.
How you can get colors in Axis Labels
First apply data labels to your chart and now select the data labels and press ctrl+1 (aww, come on now, you are reading this blog, you should what ctrl+1 means) and go to numbers tab. Select “custom” as category and specify the formatting code like this:
[blue]+0%;[red]-0%
Now, that was easy, isn’t it. But you might think, “Chandoo, what the heck the above format code is doing?”
Well, it tells excel to apply red color and add a minus symbol if the data label value is less than zero and apply blue color and add + symbol if the value is above zero. You see the custom number formatting codes have 3 parts in them, like this:
formatting for positive numbers ; formatting for negative numbers ; formatting for zero values
If you are noob (hi noob!) and lost in all these codes and square brackets, you may want to checkout our post on introduction to number formatting in excel to learn more about custom codes.
Ok, that was fun, What else you can do?
You can do much more using custom codes to format data labels. For example, a code like [Red][=0.15]0%; will, Oh, forget it, you can guess that. Just remember that excel allows only 3 parts in custom codes. So go nuts, but just thrice.
Like this post, I mean REALLY like it?
Awesome, You should sign up for e-mail updates or get our full RSS Feeds so that you never miss a post.
Why you should never miss a post? Because my friend, one of the posts might help you make your product superawesomekickass.
http://feedproxy.google.com/~r/PointyHairedDilbert/~3/kFXN86xdgt4/
Reem, one of the PHD readers, asks in e-mail,
Is there a way to prevent users from unhiding “hidden sheets” in an excel file - without using VBA?
or to put it in other words, can the “Format/Sheet/Unhide” be disabled for specific worksheets?
Here is a non-VBA way to do this. I am not sure if this is optimum, but it seems to produce results without much effort. And it doesn’t use VBA, just the VBA Editor.
Step 1: Right click on the tab you want to hide and select view code option
Step 2: In the properties window for that sheet, set “visibility” as 2 - xlSheetVeryHidden
Step 3: Now right click on the sheet name in project explorer area and select VBA Project properties
Step 4: Go to “Protection” tab and check “Lock” project
Step 5: and set password for protection, click ok
Step 6: when someone tries to open the VBA Code for that sheet to make the worksheet tab unhidden (visible), Excel prompts for a password
This trick is very handy when you are sharing workbooks with others and afraid that they may ruin the calculations or data.
http://feeds.feedburner.com/~r/PointyHairedDilbert/~3/519805511/
Reem, one of the PHD readers, asks in e-mail,
Is there a way to prevent users from unhiding “hidden sheets” in an excel file - without using VBA?
or to put it in other words, can the “Format/Sheet/Unhide” be disabled for specific worksheets?
Here is a non-VBA way to do this. I am not sure if this is optimum, but it seems to produce results without much effort. And it doesn’t use VBA, just the VBA Editor.
Step 1: Right click on the tab you want to hide and select view code option
Step 2: In the properties window for that sheet, set “visibility” as 2 - xlSheetVeryHidden
Step 3: Now right click on the sheet name in project explorer area and select VBA Project properties
Step 4: Go to “Protection” tab and check “Lock” project
Step 5: and set password for protection, click ok
Step 6: when someone tries to open the VBA Code for that sheet to make the worksheet tab unhidden (visible), Excel prompts for a password
This trick is very handy when you are sharing workbooks with others and afraid that they may ruin the calculations or data.
http://feedproxy.google.com/~r/PointyHairedDilbert/~3/MxZqhU6HEj0/
Stacked bar charts are a popular way to depict 2 more series of related data, like sales of 2 products.
But there are several ways to stack the bars in a bar chart. Here is a list of 6 ways to stack them
1. One on top of another
Advantages: Easy to create, takes less space
Drawbacks: Hard to compare, only first value starts at zero
2. Separated
Advantages: Easy to read and compare
Drawbacks: takes more space, needs extra calculation for the gap series
3. Mirrored:
Advantages: looks fancy and takes less space, good for large data sets
Drawbacks: needs extra calculation
4. Partially Overlapped
Advantages: Easy to compare, Easy to make
Drawbacks: One series dominates another, good where domination is needed (like this vs. last year)
5. Completely Overlapped
Advantages: easy to compare
Drawbacks: Needs extra formatting, not always produces good results
6. Hanged from Top and Bottom
Advantages: none
Drawbacks: difficult to compare, needs extra formula to calculate gap series
I like 2 and 5 and use them whenever I can.
What about you? How do you like your bars?
PS: for the purpose of discussion neglect other important chart elemets like labels, colors etc.
http://feeds.feedburner.com/~r/PointyHairedDilbert/~3/518717528/
Stacked bar(column) charts are a popular way to depict 2 more series of related data, like sales of 2 products.
But there are several ways to stack the bars in a bar chart. Here is a list of 6 ways to stack them
1. One on top of another
Advantages: Easy to create, takes less space
Drawbacks: Hard to compare, only first value starts at zero
2. Separated
Advantages: Easy to read and compare
Drawbacks: takes more space, needs extra calculation for the gap series
3. Mirrored:
Advantages: looks fancy and takes less space, good for large data sets
Drawbacks: needs extra calculation
4. Partially Overlapped
Advantages: Easy to compare, Easy to make
Drawbacks: One series dominates another, good where domination is needed (like this vs. last year)
5. Completely Overlapped
Advantages: easy to compare
Drawbacks: Needs extra formatting, not always produces good results
6. Hanged from Top and Bottom
Advantages: none
Drawbacks: difficult to compare, needs extra formula to calculate gap series
I like 2 and 5 and use them whenever I can.
What about you? How do you like your bars?
PS: for the purpose of discussion neglect other important chart elemets like labels, colors etc.
http://feedproxy.google.com/~r/PointyHairedDilbert/~3/vGwKaH7jLQc/
Juice Analytics, one of my favorite visualization blogs discussed about creating bubble charts that can depict search term competition among major travel sites in bubble bubble toil and trouble.
Chris, who wrote the article said,
The first tool we tried, simply on principle, was Excel 2003. As expected, making a … quality bubble chart in Excel 2003 is a hard problem. Here’s a draft of how far I got before giving in to label fatigue.
The bubbles themselves aren’t tough, but getting the labels right is hard. I’d love to see a solution, so if any reader wants to tackle it eternal fame can be yours.
Well, not that I would get eternal fame, but I wanted to give it a try, just for fun. Ever since I saw the NY Times Bubble chart on “how many times each political candidate used certain terms”, I have been itching to recreate it somewhere.
Here is the version I could create in Excel 2007
(larger version of the travel site search terms visualization)
How I made this?
I started with travel patterns data Chris shared
Then I used Excel formulas OFFSET() and ROW() and COLUMN() to rearrange the data in a tabular format (the original format is a matrix)
Then I sorted the table on bubble size
Now I made a bubble chart with 3 data series, one with bubble sizes >50%, one with 25-50% and the rest
I formatted each series and added labels to the first two series
Finally made some alignment and bingo
Download the excel file Travel Site Search Patterns - Excel Bubble Chart
(excel 2003 compatible, so you wont exactly see the above image, but one with slightly muffled colors)
How would you have designed the chart ?
Checkout other PHD Visualization Projects
How many Olympic Medals each country won in all those years?
Polar Clock to show time in Excel using Charts
Visualizing Test Cricket Statistics
What people are doing online - Dashboard Visualization
http://feeds.feedburner.com/~r/PointyHairedDilbert/~3/516630972/
Over the weekend I tried to move this site (chandoo.org) from one hosting provider to another. Whenever I do this, I dont feel so good and yesterday was no exception.
The move failed
Soon after changing the DNS Server settings to point to new host, I was getting HTTP 500 error. A closer look at error logs and I found a message “premature end of headers in index.php” which could mean several things according to mighty internet.
Well, I couldnt do much about this, so I silently changed back my DNS to old provider. I will look in to this issue again this weekend. So meanwhile if you see something funny on the blog hosting front, just hang in there and be with me. I am sorry if you are frustrated seeing a useless http error but beleive me, I am even more frustrated seeing the same errors.
http://feedproxy.google.com/~r/PointyHairedDilbert/~3/Wx2vgQc65Ig/
Does this sound familiar?
You have made an impressive presentation with lots of data and charts. You have analyzed the data so well to arrive at some really amazing conclusions. You have included several pie charts since they are easy to digest. You thought your audience are going to love it.
But your pie charts failed to evoke any response.
How to make your pie charts likable ?
Well, you don’t really want them to like your charts, you want them to like your insights, your
But, to get there, you need to shake up your audience, so that they take notice of what your charts are saying.
A simple trick for achieving this is showing charts in different formats (while retaining the meaning).
Here we will see 9 creative ways to alter your pie charts so that they can start a conversation.
Get some bottom aligned bubbles
Bottom aligned bubbles are a new fad in visualization. You can do them by using Excel’s bubble chart. Convert the pie values in to a bubble chart. Change the X and Y co-ordinates to align the bubbles at bottom.
How about concentric circles
Concentric circles can be a good alternative to pie chart and they are very easy to do using excel’s built in bubble chart. Just make all the X and Y co-ordinates as same.
Why not slices instead of pies
Using slices instead of pies is another simple and intuitive visualization trick. For this we can use bubble chart with axis adjustments so that bottom half of the bubbles is cropped.
Use a radar chart tweak
Using Excel Radar Chart, you can make a cool alternative to pie chart. Simply copy paste the pie chart values in to few more columns (you are seeing the result of 8 columns) and fire up a radar chart with area.
A stacked bar is often tastier
Of course, the simplest and most elegant of them all, a stacked bar chart. This is also very easy to implement.
Or even a regular bar chart
Use a tree map
Using a non-hierarchical tree map to replace pie charts is a good Unfortunately making the same in excel is a bit of manual job (or VBA). For smaller set of values, the manual job is worth the effect.
A simple alternative to manual job is to use Many Eye’s tree map tool
If circle is hard to swallow, a Square Pie can Help
Square pies are a simpler alternative to pie charts. They are easy to develop using conditional formatting. Here is a tutorial.
Show them in a tag cloud
Tag clouds are a famous visualization technique. They are very easy to do (either manually or automated) in Excel. Here is a tutorial for Excel tag cloud visualization.
Added later: All these charts are effective for fewer values (
http://feedproxy.google.com/~r/PointyHairedDilbert/~3/hWXunFMFCig/
Everyweek PHD shares some of the Excel links to help you learn better and become productive at using Excel. This weeks edition will help you with printing, creating named ranges, finding excel version.
Check your Excel Version using VBA
Often you might need to check which version of Excel user is running to avoid compatibility issues. Here is a VBA script that will do the task for you
How to print an excel sheet in one page?
Here is a very simple printing trick to get your excel sheet printed on one page
Excel Tutorial : Naming a range
JP offers a very good tutorial on creating named ranges in Excel.
Why you cannot open two excel files with same name
Microsoft Excel Team blog answers this question in one line:
“calculation ambiguity with linked cells. If you had a cell =’[Book1.xlsx]Sheet1′!$G$33 and you had two books named “Book1” open, there’s no way to tell which one you mean”
Got a link?
Please share with us through comments
http://feeds.feedburner.com/~r/PointyHairedDilbert/~3/509851632/
Everyweek PHD celebrates the art of visualization by showcasing some of the best infographic visualizations. These are both inspirational and fun.
Depicting User Journeys
User journeys are a very good way to depict how user interacts with a system and help designers and developers understand the system better. Wireframe blog features this unique 3d user journey map that is both eye candy and very useful.
How Game Controllers changed over time
Damien Lopez has designed these wonderful illustrations of game controllers have evolved since their birth. Pretty informative and fun to watch. Also check out comparison between Wiimote, Xbox and Play station controllers
Map of Internet Cables, How big internet is
The internet is an undersea world, but do you have any how massive it is ? Well, this picture shows it all, and shows it really well.
History of Internet
Ok, this has been doing rounds for a while now, the History of Internet video by vimeo is a pretty cool visualization explaining the history of internet and www to us. Do check it out this weekend (embedded below, if you don’t see it, just click the link above)
History of the Internet from PICOL on Vimeo.
If Milkyway is a giant metro system, this is how the transit map would look like
What is a weekend without some visualization fun. The milkyway transit map shows the milkyway galaxy and posistion of various systems in that and connects them with the familiar metro transit map layout.
Got a link or visualization to share?
Drop a comment or email me at chandoo.d at gmail.com. Happy weekend
http://feeds.feedburner.com/~r/PointyHairedDilbert/~3/507953038/
What is a time stamp?
Often when you use excel to track a particular item (like expenses, exercise schedules, investments) you usually enter the current date (and time). This is nothing but timestamping. Once the item is time stamped, it is much more easier to analyze it.
So how do I generate timestamps in Excel?
We all know about the now() formula in excel and how it tells you the current date and time. The only problem with it is that, when you press F9 or recalculate formulas, the now() will be changed to reflect latest date and time. Thus we cannot use NOW() as a time stamp.
A good alternative (although manual) is to use keyboard shortcuts CTRL + ; or CTRL + : to insert current date and time in the active cell. Since this places the value, we don’t need to worry even if the date or time changes. But the only issue is, every time you need a time stamp you have to press the 2 keys.
A better way to get timestamps in excel is to use a special type of formulas, called as circular formulas.
What the heck is a circular formula?
A circular formula refers to itself (or to another cell which in turn refers to this cell). Confused ? Well, let me give you an example.
=if(A2 tools > options > calculation tab and check the iteration box. In excel 2007, you can go to office button > excel options > formulas > iteration area.
As you can see the default max. iterations per formula execution is 100, which means, every time you run the circular formula, excel calculates it 100 times before presenting the value. So our formula =if(A2
http://feeds.feedburner.com/~r/PointyHairedDilbert/~3/505763012/
GoDaddy which hosts my site (http://chandoo.org) just sent me an email saying chandoo.org is consuming way too much server resources and hence needs to be disabled. They didn't give me any time to move the site or disable plugins that might be causing excessive server load. I have spoken with the customer care and they couldn't help me much since it is late in the night. I am hoping to get the site back up by tomorrow morning 9 AM PST. For the time being I have pointed the RSS Feed source to r1c1.blogspot.com where I used to write about excel really long time back. I will post updates about chandoo.org here. Stay tuned.You can mail me at chandoo.d at gmail.com.
http://feeds.feedburner.com/~r/PointyHairedDilbert/~3/503909378/chandooorg-facing-downtime-403-error.html
Every week Pointy Haired Dilbert celebrates visualization and info-graphics by showcasing some of the creative and fun charting work from around the web. Have inspiration and fun. Browse hundreds of fun visualization and graphics projects in archives.
New way to browse news as tree map
Newsmap is a creative and fun way to look at news. They organize news items on a tree map based on recency and relevancy of the news items (that are probably pulled from Google news). The background color indicates the news topic (red for world, purple for business, green for tech etc.)
Beautiful Time line of Apple Mac in the last 25 years
Wired Magazine celebrates 25 years of Mac by making this beautiful timeline piling all the Macs and other gorgeous gadgets Apple made over the last quarter century and loosely arranging them in a time line fashion. You can clearly see how they have migrated from dirtyish beige color to supercool white and blacks over the last few decades. [via Cool Infographics]
Oakland Crime as Heat Maps
Heat maps are a popular way to explore geographical data. Oakland Crime maps article (linked above) provides insights in to creating heatmaps using geographical data.
US Elections - Understanding the Swing Vote
Remember Robert Kosera’s charts to understand Swing States and how they voted over the last several elections ? Well, Pedro took the same data and presented it in a different way, pretty innovative I must say. [via Simple Complexity]
Accidental Maps
We spend lots of time to create charts and graphs to make sure they don’t look like accidents. But there is whole another of maps that are created by accident. You can see a map of Australia in the above puddle. Follow the link for more eye candy at Strange Maps
Happy weekend
http://feeds.feedburner.com/~r/PointyHairedDilbert/~3/501002679/
Happy New Year to all my readers. I am sure 2008 has been many things to many people, but I am hoping it has been an year of productivity and happiness for you. Here is to wishing a great, no, fantastic 2009 for you and your family.
If you were busy through out 2008 and couldn’t really catch up on all the fun excel and charting posted and discussed at PHD, here is your last chance to browse them. 2009 will be a fresh year. I am kidding, all the articles will be available in 2009 and beyond as well. But just go ahead and read them anyways.
Here is 10 posts from 2008 that are must read
Excel can be Exciting - 15 fun things you can do with excel
Become a Conditional Formatting Rockstar - Learn 5 cool Excel tips
Make beautiful charts - use these gorgeous templates
6 Charts you will see in hell
Select & Show one chart from many - Excel Charting Trick
Excel KPI Dashboards - 4 Post Tutorial and Free Downloads
15 Microsoft Excel Formulas everyone should know
Micro-charting in Excel - Alternatives Reviewed
Tracking your Mutual Funds and Stocks using Excel - Download included
Master Excel’s Paste Special - Learn these cool tips
Here is a list of top referrers to the site:
Life Hacker
Jon Peltier @ PTS Blog
Spreadsheet Page
Coliss
Digital Inspiration
XL Cubed Blog
Daily dose of Excel
I am so thankful to all my readers and commenters who supported PHD and helped me learn so many things.
Special thanks to Robert and Vishy who contributed to this blog through guest articles.
Once again, wish you all a very happy 2009
http://feeds.feedburner.com/~r/PointyHairedDilbert/~3/499540486/
Here is a very quick spreadcheat for the new year.
If you need to select all the cells in a series (either row-wise or or in columns) you can use one of these 2 shortcuts (and avoid unnecessary scrolling)
1. Select the first cell in the series
2. Press Shift + Ctrl + Down arrow
Or
1. Select the first cell in the series
2. Hold down SHIFT and Double click on the edge of cell in the direction you want to select
for eg. double click on the think black line (that you see when you select a cell) and excel selects all the cells in that direction
http://feeds.feedburner.com/~r/PointyHairedDilbert/~3/498744625/
It is the holiday week, that means less posts and more fun. We went to a friend’s wedding yesterday and had wonderful time. We are planning to celebrate Christmas at home with friends this year. How are you celebrating the holidays this year?
Off to this week’s excel links,
Editing Data Series references in Charts
Creating charts is probably the most time consuming tasks when it comes to using excel. Jon takes a look at editing data series formulas and provides a simple add-in that you can use if you make lot of charts on regular basis.
10 rules for good design
Garr at Presentation Zen lists ten simple rules for good design, even though he wrote it from presentation point of view, most of them are relevant for spreadsheet design. Take a look and implement one of them today in your report or chart.
Creating table of contents in your workbooks
Ever built a large work book with more than 5 sheets? It might be helpful to include a navigation sheet where you provide links to other sheets. Debra at Contextures blog shares few tips on making ToC for your workbooks.
Making beautiful charts using Adobe Illustrator
Even though excel is a very good tool for making charts often you may need a design quality software to make charts. Nathan at Flowingdata has posted a detailed tutorial discussing how to make charts using Adobe Illustrator.
Eight must have features for real time dashboards
Juice analytics lists down 8 features that dashboard makers should keep in mind while designing. Of course if you are stuck at building, do check our tutorials on excel based dashboards.
Happy holidays everyone
http://feeds.feedburner.com/~r/PointyHairedDilbert/~3/493245126/
A very quick spreadcheat for Friday. If you need to type content in a cell and you want to see it in multiple lines then use alt + enter to break the content in several lines. See to the right for an example.
Bonus tip: If you are using formulas to create content in a cell by combining various text values and you want to introduce line breaks at certain points … For eg. you are creating an address field by combining house number, street name, city and zip code and you want to introduce line breaks after house number and street name then you can use CHAR(10). Like this:
=housenumber & CHAR(10) & streetname & CHAR(10) & city & zipcode
Remember when you do this, you need to enable wrap-text feature for that cell from cell formatting dailog (ctrl+1) to ensure proper display.
http://feeds.feedburner.com/~r/PointyHairedDilbert/~3/489219122/
Stružák, one of the commenters on the 2009 Excel Calendar Template post asked me if I can rearrange the cells in the calendar so that the week could start on Monday. Since I didn’t have access to internet for a while, I have asked him to make the necessary changes and mail it to me if he can.
Being an amazing person he is, Stružák edited the excel file and mailed me back a rearranged version of the calendar with week starting on Monday and ending on Sunday.
Feel free to download this version of the 2009 (and beyond) excel calendar template if you prefer Monday as your first day of week.
Once again, thanks to Stružák
http://feeds.feedburner.com/~r/PointyHairedDilbert/~3/486889565/
Can you imagine building a complex worksheet without formulas? I can’t. While no one can dispute the usability of formulas, we all know how painful it is when an excel formula returns a mysterious error and we don’t know what is causing it.
When I learned IF() formula for the first time, I have spent a whole Sunday morning debugging a stupid error in a grade calculation formula.
So as part of our spreadcheats series, we will learn a handy trick you can use to debug formulas and fix the errors quickly.
Assuming we have a moderately lengthy formula like this
=IF(AVERAGE(B2:B6)
http://feeds.feedburner.com/~r/PointyHairedDilbert/~3/486647064/
Finally I got internet connection at home. The folks at Airtel Broadband are very fast and professional. They set up the connection in very few days. So I will be able to blog regularly from now on.
Moving to this week’s excel links you should browse:
Convert Excel Tables to HTML
Excel is very good when it comes to handling tabular data. But what if you need to convert a table in excel to HTML? No worries, use the tool linked above to get web tables from your lists
Back up excel files while you work using VBA
Have you ever worked on same excel file and saved it with a different name to maintain the versions? I do this all the time. Thanks to Debra, who came up with a simple macro to save a backup version of the file in a specified folder by appending current date-time stamp to the filename.
Charts that make you go wtf
Tony at SupportAnalytics shares 11 charts that will shake your head. Check it out for some Monday fun.
Introduction to Error Bars in Charts
My friend Jon explains the error bars in excel 2007 charts, a very useful feature. Until few days ago I had no clue what they do and this post definetly makes understanding them a bit easier for the Excel 2007 users.
Tell about your fav VBA code and win Excel goodies
JP, who is a regular commenter on this blog and author of Code For Outlook and Excel where he shares VBA code snippets has completed 100 posts and celebrating that by giving away free books. All you need to do is just tell him about your favorite code / usage.
http://feeds.feedburner.com/~r/PointyHairedDilbert/~3/485660335/
Grid lines provide great help in understanding values in a chart. Here is a handy trick you can use in the next bar chart to spice it up.
Here is how you can get this type of chart (we will call it a brick chart)
First we will make a regular bar chart
Now, let us assume we want each brick to be of 5 units width. So we take another column in the worksheet and enter the value 1 twenty times. This will be a dummy series that we will add to the chart. Just copy these 20 cells and paste them in to the chart. (just press ctrl+c after selecting the dummy value cells, and then select the chart you made in step 1 and press ctrl+v)
Now we will change the dummy series’ chart type from bar chart to column chart. Just select the newly inserted series in the chart and right click and select chart type. In the chart type dialog, change the type.
Now the new chart will look like this. We will adjust the secondary axis parameters so that the columns will span the entire height of the chart. Just use the format axis dialog for this.
Once the columns are of sufficient height, we will adjust their fill color to transparent (none) and line color to white. This will produce the following effect.
Finally, remove the unnecessary chart junk like axes and you have a neat looking brick chart.
Download the excel brick chart template and play with it.
PS: making this type of charts is slightly difficult compared to normal charts. What we have done here is, we mixed two types of charts. These are called combination charts. We will explore more about this type of charts later.
http://feeds.feedburner.com/~r/PointyHairedDilbert/~3/480190413/
Update Sources
