|
|
Slasher movies are a scary Halloween tradition, and you can fight back against these horror films, by using Excel Slicers. The Guardian recently posted a list of Greatest Films of All Time, and you can download their data in this Greatest Films spreadsheet. I've avoided seeing the Halloween movie, but long ago I saw another frightening holiday film -- Black Christmas. Don't take the kids to see that one! But it didn't even make the Guardian's list of top horror films (anti-Canadian bias?). What are Excel Slicers? Slicers are a new feature in Excel 2010, and while they aren't as frightful as a slasher movie, they are very powerful, and help you rip into your Excel data. In this example, I created a pivot table from the greatest films data, and put Genre and Actor into the Report Filters area. When I selected Horror from the Genre drop down, the
Continue reading Excel Slicers for Halloween Horror Films
To make it easier to people to enter data in your Excel workbook, you can create drop down lists in the cells, by using Excel data validation. A couple of years ago, one of the first videos that I uploaded to YouTube showed how to create data validation drop down lists in Excel 2003. I've just finished a new version, for Excel 2007 and Excel 2010, and you can see it at the end of this blog post. The new video includes a couple of shortcuts that will make it even easier to create those drop down lists. Name the Item List When creating drop down lists, you can use a list of items from a different worksheet, as long as you give a name to that list of items first. Here's the quickest way to name the list of items: Select the items Click in the Name Box
Continue reading Shortcuts to Create Drop Down List in Excel
Did you ever get an Excel file from someone else, and try to sort out their Excel VBA code? Or, even worse, open an Excel file that you wrote long ago, and try to remember what all those variables mean?
I spend lots of time staring at Excel code, but apparently I don't use the right-click menu too often, because I hadn't noticed a couple of handy commands until recently. These commands can help you decipher that mysterious code, and unravel the complicated sections.
Excel VBA Quick Info
The first handy command is Quick Info. I have Auto Quick Info turned on in the VBE Editor options, and it helps me remember the syntax as I type the code.
What I didn't realize was that you can right-click on a variable, function, statement, method, or procedure in the code, and click Quick Info.
A tooltip appears, with details
Continue reading Get Details in Excel VBA Code
One of the best features of a pivot pivot table is filtering, which allow you to see specific results. You can: Add Report Filters at the top of the pivot table, to limit what's summarized in the pivot table data. Use Label, Value, and Manual filters on the pivot fields, to narrow the focus. The pivot field filters are easy to use, and you can quickly change the pivot table report for different needs. However, if you apply a Value filter on a pivot field, then try to add a Label filter, the first filter is removed. It's possible to apply multiple pivot field filters at the same time, so keep reading to learn how. Apply a Label Filter In this example, the pivot table has data from January 2008 to June 2010. To compare the first six months of each year, you can use a Label Filter
Continue reading More Than One Filter on Pivot Table Field
We have a very famous waterfall here in Canada, and it creates gorgeous photos, like this one from my fall 2008 vacation. You can create waterfalls in Excel too -- Waterfall Charts. They might not be as spectacular as Niagara Falls, but can be useful for showing how values change. There are details here for creating a simple waterfall chart, and a video that shows the steps. Net Cash Flow For example, in a small business, the net cash flow might be a positive number one month, and a negative number the next. In the Waterfall Chart shown below, the red columns represent a negative number, that brings the cumulative cash total down. Green columns are shown for months with a positive cash flow. The starting value for each red column (negative) is at its top, and the cumulative value for that month is the amount at the
Continue reading Create a Waterfall Chart in Excel
To fill blank cells, or delete rows with blanks cells, you can use Excel's Go To Special feature.
For example, in the worksheet shown below, you might want to fill in all the blanks in column B, by copying the value from the row above.
There are instructions on the Contextures website to fill blank cells, by using Go To Special to select the blanks. You can do this manually, and there's sample code to make the job easier.
Selection Is Too Large Error
This technique works very well, unless you're trying to fill blank cells in a long list. In that case, you might see the error message, "Selection is too large."
This happens in Excel 2007, and earlier versions, because there is a limit of 8192 separate areas that the special cells feature can handle. (This problem has been fixed in Excel 2010.) There are
Continue reading Excel Error – Selection Is Too Large
Remember, Sunday October 17th is Spreadsheet Day, so you'd better start planning your celebrations. You could start the day with a big bowl of Chex cereal -- each bite looks like a little spreadsheet. For dessert at the end of the day, have some pie, or bars, while you dream about charts.
As my contribution to Spreadsheet Day 2010, I'll list the top 5 Excel tips that I read on Excel blogs over the past year. These were Excel articles that solved problems I'd encountered, or got me thinking about new techniques to try. You can read these articles on Sunday, while you're celebrating Spreadsheet Day. Maybe your kids would enjoy them as a bedtime story!
There were many other Excel articles too, but I only had room (and energy) to list five. You can help out by listing your favourite in the comments.
Copy PivotTable Style Formatting
When PivotTable
Continue reading Spreadsheet Day 2010 — Top 5 Excel Tips
Are you ready for Spreadsheet Day on October 17th? Maybe you can add a Spreadsheet Day message to all your workbooks, using the technique described in this blog post. I'm sure your co-workers would enjoy that!
The Mission: Remind Users to Fill in Customs Form
In this example, the workbook has an order form, with a data validation drop down list, where you can select a customer name.
There are VLOOKUP formulas that pull the address information for the selected customer, to fill in the top of the Order Form sheet. If the customer is located in Canada, you'd like to remind the user to fill in a customs form.
Create a Worksheet Change Macro
By using Event code in Excel VBA, you can make a macro run automatically if something happens on the worksheet. In this example, you want the macro to run if there is a change
Continue reading Excel VBA – Macro Runs When Worksheet Changed
Happy Canadian Thanksgiving! You probably have your own spreadsheet to organize the meal, but you can download my Excel Holiday Dinner Planner, if you don't have one of your own. The dinner planner has data validation cells, where you can select a start time, or type of food, or cooking location, from a drop down list. Data Validation Drop Down Doug Glancy sent me a sample data validation file that he created, that uses a variation on dependent data validation. I changed his file a bit, to "Thanksgiving" it up. The file has a list of Thanksgiving dinner guests, whether they'd like turkey, and if so, the number of slices. In column B there's a simple data validation drop down list, where you can select TRUE or FALSE. Conditional Data Validation In column C, there is a conditional data validation drop down list, based on
Continue reading Excel Conditional Data Validation
Earlier this week, you read about the Top 100 Canadian Singles, and saw the pivot table that summarized the top songs by decade. In the comments, Martin mentioned the FLOOR function, that I used to calculate each song's decade, based on its release year. Martin also pointed out that the files weren't downloading, and I finally managed to fix that -- sorry about the inconvenience. Take my advice, and don't work on your blog while travelling, if you can avoid it! Things that work perfectly at home, refuse to cooperate when you're on the road. FLOOR It The Excel FLOOR function rounds numbers down, toward zero, based on the multiple of significance that you specify. In the Canadian Music file, the decade is being calculated, so 10 is used as the multiple. =FLOOR(A2,10) In column B, you can see the result of the FLOOR function,
Continue reading Fix the FLOOR to Round Down in Excel
|
|
Recent Comments