Excel Weekly News from Contextures Apr 08, 2014
Clean up a list + more Excel tips
In this week's Excel news, you'll see how to clean up an imported list, and other tips. Thank you for reading the Excel news!
- Debra firstname.lastname@example.org
- Clean Up an Imported List
- Protect Pivot Table Source Data
- Headers on Combo Box List
- More Excel Tips
- Video: Highlight Numbers
- Weekly Photo: Snakes and Alligators
- Excel Tools
Clean Up an Imported List
Sometimes you can import data into Excel, and eveything goes smoothly. Other times, you might need to do some work, to clean up the list. In this example, I pasted a two column table into a worksheet, but it all ended up in a single column.
Click here to see how I fixed the data, and read the comments, to see other solutions: Change Single Column List to 2 Column Table
Protect Pivot Table Source Data
If you have data for several departments or regions in a pivot table, you might want to send out individual results to each manager. If you lock down the pivot table, can you prevent people from seeing other regions' data?
Unfortunately, there's no easy way to do this, and you can read the details here: Protect Pivot Table Source Data
Headers on Combo Box List
If you create a combo box with multiple columns, you can add headers, so the list is easier to understand. Also, make sure to adjust the column widths, so everything lines up nicely.
Click here to see the details: Headers on Combo Box List
More Excel Tips
Here are a few of the Excel articles that I read recently, that you might find useful:
Other Excel Articles
Here are a few of the Excel articles that I read last week, that you might find useful:
- I don't want to be overly optimistic, but it looks like winter is finally over. At Neoformix, Jeff Clark used the weather data to show that winter in the Toronto area was the coldest in 20 years. You could download data for your area, and make a similar report in Excel.
- You can't use ActiveX controls on a Macintosh, so Prof. Lee Townsend shows how to create dependent drop downs using Form control combo boxes.
- How much money will you need to retire? The Blunt Bean Counter shares a retirement planning spreadsheet, and there a couple of other links in the comments.
- Doug Glancy found that the SheetActivate event isn't triggered if a workbook has multiple windows, so he added a workaround.
- If you work with dashboards, take a look at the new books from Mike Alexander (Excel Dashboards and Reports for Dummies) and Kasper de Jonge (Dashboarding and Reporting with Power Pivot and Excel).
- Finally, for a humorous peek at what other people are saying about Excel, read this week's collection of Excel tweets, on my Excel Theatre blog.
Video: Highlight Numbers From a List
If you have lottry tickets, 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. For example, create a list of obsolete products, and highlight those on an Excel worksheet with product order requests.
Snakes and Alligators
On the weekend, we had fun playing a game of Snakes and Ladders. Instead of the old-fashioned flat board, we used a fancy 3D version, with slanted ladders. Then, at the end of the game, a herd of alligators stormed the board, and turned it into Snakes and Alligators. Watch your fingers -- those teeth look sharp!
Recommended Excel Tools
In addition to all the free Excel tips and tutorials, there are other Excel tools that you can invest in. To learn more about the products listed below, click on the links to take a look at their features, and decide if they're right for you.
- Contextures Excel Tools Add-in
- Contextures PivotPower Premium Add-in
- Contextures 30 Excel Functions in 30 Days
- Excel Online Course
- Excel Charting Tools
- Excel Dashboard Kits
- Excel Project Management Templates
- Excel VBA School
Note: I am an affiliate for some of the products mentioned in this newsletter, and earn a commission on the sales.
Contextures Inc., Copyright ©2017
All rights reserved.