|
|
Earlier this month, I had the pleasure of flying out of Chicago's O'Hare airport. I was checking in at the ungodly hour of 6 AM on a Sunday, and hoped that would be a quiet time at the airport. The streets near the downtown hotel were almost empty, so that was encouraging, but the closer the taxi got to the airport, the busier things got. Not a good sign! There was no line at the Air Canada counter (woohoo!), but there was no counter agent either. He showed up a couple of minutes later, and that part of the process went quickly. (The same guy showed up at the gate later, to take our tickets, and attached the jetway to the airplane when it arrived. I figured he'd fly the plane too, but he didn't!) Then, it was on to the dreaded security checkpoint, and the inevitable long wait.
Continue reading Airport Security Times in Excel Box Plot
On the Consumerist website last week, they posted Lauren's Excel budget template, so I downloaded it, to take a look. I'd call it an Expense Tracker, rather than a "Budgeter", because it's used to record income and expenses. (Do you know the origin of the word "budget"? I had to look it up.) Here's what it looks like, with part of the formula for the Total cell showing in the formula bar. The grey fill colour is added with conditional formatting. Shown below is the full formula for the Total. You can see that Lauren has named the date headings (_8_10d) and hidden total row (_8_10) for each month. Wow! It makes me tired just looking at that. Lauren created a lot of named ranges, to set up the file, and she'll need to do more work to add more months. Because there's a separate
Continue reading Help Improve This Excel Expense Tracker
Apparently you have to buy a ticket if you want to win the lottery, so I'm out of luck. However, if you're in an office lottery pool, or buy your own tickets for the lottery, Excel can let you know if you have a winning ticket. It just takes a bit of conditional formatting, and the COUNTIF function. You could use this technique to highlight other things too, based on a list of items to check. For example, you could create a list of bad accounts or obsolete products, and highlight those on an Excel worksheet with product order requests. Highlight Lottery Numbers In this example the ticket numbers are in cells B2:G4, and the winning numbers drawn for the current lottery are entered in cells B6:G6. Follow these steps to highlight the winning numbers in the list of tickets: Select the cells were the conditional formatting will be
Continue reading Excel Knows If You Won the Lottery
To keep your data details confidential, you might want to send someone a copy of a pivot table, without the link back to its source data. It's easy to copy a pivot table, and paste it as values, but if you try to paste the values and source formatting, you'll be disappointed by the results. The values are pasted, but not the PivotTable Style formatting. Fortunately, John Walkenbach discovered that you can paste from the Office Clipboard, instead of using the Paste Values command, and the PivotTable Style formatting is pasted too. The result is something that looks like the original pivot table, without the link to the source data. For more instructions on PivotTable Styles and pivot table formatting see the Contextures Pivot Table Format page. Watch the Pivot Table Format Copy Video To see the steps for pasting the pivot table formatting and values,
Continue reading Copy Pivot Table Format and Values
If you're hosting a golf tournament, with cash prizes for the top ten players, what happens if two or more players are tied in rank? Maybe if the top two players are tied, you'd have a playoff, but if two players are tied at 3rd, you wouldn't try to break the tie that way. In March, I posted an article about Excel's RANK function, and how you can break ties. In the comments, someone asked how to split the points if players were tied at the same rank. So, for the two golfers who are tied for 3rd place, you could add the 3rd and 4th place prize money (or points), and divide that amount equally between the tied players. Split the Prize Money When I checked Google to see how golf tournament prizes were split, I found this interesting article from Sports Illustrated in September 1962, describing how
Continue reading Split Golf Prize Money for Tied RANK with Excel
Last July, I posted sample Excel VBA code to navigate to the next or previous worksheet. If you're on the second sheet, you can click the Next button to go to the third sheet. Or, click the Back button to go to the first sheet. In the comments for that blog post, Ron de Bruin suggested modifying the two navigation macros, so they test if the target sheet is hidden, before selecting it. Finally, only 14 months later, the revised code is ready. Hey, quality work takes time! ;-) Excel Worksheet Navigation Code Here's the Excel VBA code for the two macros -- GoSheetBack and GoSheetNext. If the next sheet is hidden, the code keeps going until it finds the next visible sheet. If it reaches the end of the sheet tabs in either direction, it jumps to the other end, and continues from there. '==========================
Sub GoSheetNext() Dim
Continue reading Add Navigation Buttons to Excel Worksheet
There's an Excel holiday dinner planner on the Contextures website, and I'll need that in about a month, when Thanksgiving arrives. In the meantime though, there are lots of daily meals to plan. Now that the kids are back in school, and everyone is involved in fall activities, it can be tough to get the meals organized. To help you make the job easier, I created an Excel weekly meal planner. You can enter your favourite meals, their basic ingredients, and run a macro to create a shopping list for a week's meals. Select Meals for a Week There are a few sample meals in the Excel weekly meal planner, and you can add to the list, or replace the existing meals with your family favourites. Then, select a meal for each weekday from a data validation drop down list. Create a Shopping List There's a button on the
Continue reading Plan Weekly Meals in Excel
There are instructions on my Contextures website for using a combo box with data validation cells. Click on a cell that contains a data validation list, and the combo box appears. It takes a bit of programming, but has several advantages over a data validation list drop down: a combo box's font size can be set more than 8 rows can be displayed autocomplete can be enabled Exit the Combo Box In the first version of the data validation combo box, you had to double-click on a cell, to make the combo box appear. In the next version, you simply clicked on a cell with a data validation list, and the combo box appeared. Then, Ted Lanham suggested code that would allow you to exit the combo box by pressing the Enter key or the Tab key, so you didn't even have to touch the mouse to make the
Continue reading Data Validation Combo Box in Excel Table
Way back in April, I wrote about the Excel VBA code to automatically add new items to a Data Validation drop down. It's an easy way to update a list as you work, so the latest items are always available for users.
Last week, someone wrote and asked how to modify that code, so only a specific user could add new items. Everyone else should see a message that says they aren't permitted to add items.
This technique isn't foolproof, and anyone who's determined to circumvent it would be able to. But, it's a good way to remind people that they can't update the list without permission.
Identify the User
One way to find out who's trying to add a new item, is to check the user name that's entered in the Microsoft Office application. After you install Office, you can personalize it in Excel Options, in the Popular category,
Continue reading Allow Only Specific User to Change Excel List
Yesterday, I started out with the best of intentions, planning to get some work done, and find a couple of topics for upcoming blog posts. Then, while sipping my morning coffee and reading the RSS feeds, I clicked on an article about pivot tables. There were a few lines of sample code for creating a list of fields in the pivot table, which seemed like a good idea, but needed improvement. And there went the day! But, in the end, I had some useful code for removing calculated fields from the pivot table layout, so the day was productive, but not in the way that I anticipated. Strange Results for Orientation Property The journey started out with that simple list of pivot table field names. I wanted to see more information about each field, so I wrote some Excel VBA code to list the pivot field caption, source name, location
Continue reading Remove Pivot Table Calculated Field With Excel VBA
|
|
Recent Comments