Instead of copying and pasting, you can quickly copy data from a web browser to Excel, by dragging and dropping.
To see the steps in action, you can watch this 30 second video.
Last month your revenue was $40,000 and this month it’s only $30,000? What happened? In Excel, you could print a nice report that shows each revenue stream for last month and this month, so you can compare the amounts.
You could even create a bar chart to compare the different revenue streams.
The bar chart lets you see the differences for each stream, but maybe you’d like to see how each revenue stream contributed to the overall change in revenue. A waterfall chart will let you see the changes that occur between a starting point and an ending point. In Excel, you can create a waterfall chart by building a column chart, and making some changes to it.
The first step in building a waterfall chart is to create a table that calculates the individual changes, and a running total. In the example below:
To start the waterfall chart, select the range in the thick border (A1:F10), and insert a clustered column chart.
To focus on the revenue stream changes, you can hide the series for the running total:
To remove the Run Ttl series from the legend:
Next, you can add a bit of formatting to make the column chart look more like a traditional waterfall chart. To widen the columns:
To lighten the gridlines:
In this revenue chart, up is good, and down is bad, so you can change those series colors to red and green. If the End series is red, change it to a different color, to avoid any confusion. To change a series colors:
And here’s the finished waterfall chart. I also added data labels with a custom number format, to show up and down arrows.
The example shown here is a very simple waterfall chart, with only a few changes. If you need to make lots of waterfall charts, or more complex charts, you should invest in Jon Peltier’s Excel Chart Utility. To use the utility for the revenue stream data, you could set up a simple table that showed the start and end amounts, and the change in each revenue stream. These are just links to the original table, that had monthly revenue and the change amounts.
When you install the Chart utility, it adds a Waterfall command to the Ribbon. Select a cell in the table, then click the Waterfall Chart command, to open the dialog box.
You can change the sort order, and the option to use values as data labels, or go with the default settings. Then click OK, and the chart is created and formatted instantly. Much quicker and easier than doing everything manually!
So, to save yourself some time and headaches, take a look at Jon Peltier’s Excel Chart Utility. Use it to create waterfall charts, and other custom charts. It’s very reasonably priced, and will quickly pay for itself, in time saved.
Last week, John Walkenbach posted a list of basic skills for men, then basic skills for women, and finally, basic skills for dogs. That got me thinking – what’s on the list of basic skills for Excel users? If you use Excel at work, or list it on your résumé, what tasks should you be able to do?
I’m sure your list is different, but here’s mine. Would any of these make your list?
Do you have two or more web pages that you use frequently? I like Google as a home page, and use it many times during the day. I also refer to my own website several times, to make sure everything is working okay, or look up one of the Excel tips that are stored there.
I had Google set as my home page in Firefox, then opened Contextures.com later.
It’s not a terribly painful process, but I’ve found a way to set multiple home pages, instead of one.
Here’s how I set up the multiple pages:
The next time you open Firefox, both home pages will automatically open.
In the Google Reader, you can see a list of your RSS feeds, and group them into folders. Usually I assign a feed to a folder as soon as I subscribe to it. If I forget, then I can assign it later.
One way is to click the Manage Subscriptions link, at the bottom of the feeds list. Then, click the feed’s Change folders button, and select a folder. This method works well if you have a few feeds to change.
You can also click on a feed name in the feed list, then, click the Feed Settings button.
A quick way to assign a folder is to drag the blog name in the feed list. In the screen shot below, I’m moving 10x Software Development from Software to Technology.
In Excel 2003, you can add a couple of buttons to the toolbar to make it easy to filter a table. For example, in the table below, the East region is selected. With one click of a button, and no programming, you can add an AutoFilter and filter the table to show only the East region orders. Thanks to Roger Govier for sharing this tip.
[Update: For Excel 2007 instructions, see Easy Filtering in Excel 2007]
You can add two buttons to the toolbar, to make filtering easy. One button will filter the table for the selected item, and the other button will show all the records.
To add the buttons:
Now you can use the new buttons to filter the table, or to show all the records. The table doesn’t need to have an AutoFilter currently applied.
The table is filtered, and shows only the East region records.
To remove the filter, and show all the records again:
The filter is removed, but the AutoFilter feature is still turned on, and all the records are visible.
For more information on Excel AutoFilters, visit the pages on Excel AutoFilter Basics and Excel AutoFilter Tips on my website.
To see the steps, you can watch the short video below.
I’m setting up my new laptop, and plan to copy my bookmarks from the old laptop. Before I copy them, I’ll clear out the dead links, to tidy things up a bit. There’s no point in copying useless data to the new machine.
If you’d like to do this on your machine, you can use the free utility AM-Deadlink. It finds dead links and duplicate links in your list of browser bookmarks.
It’s easy to install and use, and takes only a couple of minutes to check my bookmarks (about 1700). Then I sort the list by error number, and delete any 404 pages.
You can also use AM-Deadlink to create backup files for the bookmarks.
In Excel, if you divide 2 by 8, the result is 0.25. if you format the cell as a fraction, the cell might show 1/4 as the result.
What if you want to show the result as a ratio? How can you get the cell to show 1:4 instead of 1/4? There may be other ways, but here’s the formula that I used.
=B2/GCD(B2,C2) & ":" & C2/GCD(B2,C2)
This formula requires that the Analysis ToolPak be installed, in Excel 2003 and earlier versions. It divides each cell by the greatest common divisor (GCD), and puts a colon between the two numbers. Would you use a different formula? __________________
Well, it’s Christmas Eve, and you’re probably too busy to read a long blog post today. So, here’s a quick tip that I discovered this week.
I use Firefox as my browser, and when I need a new tab, I right-click in the tab area, then click on New Tab.
Now, I realize that isn’t a terrible hardship, but it’s much easier to create a new tab in other browsers, that shall remain nameless.
I know there’s a New Tab button, and I even added that to my toolbar, but never remember to use it.
This week, I accidentally double-clicked in the tab area, to the right of the last tab (or in the sliver of space above the tabs, if your aim is good), and presto! A new tab was instantly created.
It’s still two clicks, but for some reason I find this quicker and easier. It’s the small things. 😉
Recently I’ve been skimming through Defensive Design for the Web by 37signals and have found a few good tips for creating web forms, help and error messages. It’s not the best book that I’ve seen on web design, but it’s easy to read, and illustrates its common sense guidelines with good and bad examples from popular sites.
At the end of the book there’s a design test that you can use on your own web site, to see how it rates.
As a customer, my worst web site experience was with the Westin Hotel, while booking accommodations for a conference. I selected my arrival and departure dates, then clicked a button to view room information, so I could decide which type I wanted. I closed the room info window, completed the booking, and got my confirmation, which I quickly read, then filed.
About a week before the conference, I checked the confirmation, and realized that it was for only one night, instead of the four that I needed. A few frantic phone calls later, I had the four nights booked, although one night was a a different hotel, a few doors down from the Westin.
What went wrong? I went back to the site and tested. While booking, when I opened that room info window, it wiped out my departure date! Now that’s bad design. One of the book’s 40 guidelines suggests saving customer data in incomplete forms.
Some of the book’s ideas could also be applied to forms in Excel and Access. For example: