It looks like a simple task – filter a list, so you an see the items you’re interested in. Then, copy data from one column, and paste it into another. But you can have a serious problem pasting in Excel filtered list.
Hide Table Details With No Macros
Last week, I was preparing reports for a client, and needed to find a quick and easy way to show or hide details in a long list. After a bit of thought, and experimenting, I found a way to do this – without macros.
You could use this technique on a table or pivot table, to make it easier to explore the data. You could also use this technique on an Excel dashboard, where real estate is in high demand!
Continue reading “Hide Table Details With No Macros”
Excel Roundup 20140421
Last week, Microsoft’s Excel team announced several welcome improvements to Excel online. The new feature that I like best is that you can now edit files that contain VBA, without the code being removed or corrupted. Of course, it would be even better to use VBA online, but we’re making progress!
Other new features include:
- Hiding and unhiding rows and columns with a right-click
- Easier access to Excel Online – just go to https://office.com and click the type of document you want to create
- Add, remove and edit comments
- Navigate quickly with the Go To shortcut (Ctrl + G)
Adding Form Controls on a Worksheet
Do you spend your days, and maybe your nights, talking about Excel with your friends? It’s amazing how quickly the time passes, while you chat about all the awesome features and tricks that you know. You know, exciting things like adding form controls on a worksheet.
Make a Better Pizza Pie Chart
While flipping through a magazine on the weekend, I saw this pizza pie chart. Yes, the pizza looks delicious, but the toppings are split down the middle. That makes the vote appear to be 50-50, not 58-42. Fun idea, but bad pizza pie chart!
Excel Roundup 20140414
Mike Alexander is having a contest on his Bacon Bits blog – create an Excel meme, and you have a chance to win a couple of Mike’s books. The deadline is April 16th, and the rules are on Mike’s blog.
If you aren’t sure what a meme is, Wikipedia describes it as an idea or picture that is shared on the internet. Mike posted a couple of examples on his blog, and here is my contribution. I’m sure you’ve met someone like this guy!
I made this in Excel, using the Insert Online Pictures command, and the text in WordArt. What can you come up with?
Center Headings Without Merging Cells
You know that merged cells are evil, and should be avoided at any cost. Those merged cells can make it almost impossible to do simple tasks on a worksheet, such as sorting or filtering. Merged cells can even make it difficult to select a range of cells – and that’s annoying, as you probably know! Here’s how you can center headings without merging cells.
Change Characters to Superscript
Do you ever use the Subscript or Superscript fonts formats in Excel? Maybe you’re reporting on chemical usage, and you need to enter CO2 as one of the row headings. Here’s how to change characters to Superscript – you could use similar steps to change characters to Subscript.
Excel Roundup 20140407
Last Friday, Microsoft’s Excel team hosted an AMA on Reddit, where anyone could submit a question about Excel. You can read all the questions and replies at this link.
It looks like the event went very well, so they might host another one sometime in the future. What would you ask the Excel team, if you had the chance? Think of your questions now, so you’ll be ready for the next opportunity.
Here’s one of the questions, and an answer by program manager, Ben Rampson:
Q: Do you guys ever see a use for Excel that you didnt even think was possible. Crazy forumals, etc
A: I’ve seen lots of crazy uses of Excel that I never expected. Some of my favorites:
- A customer issue escalation from NASA complaining the space shuttle launch would be delayed by a few months if we didn’t address the problem they found.
- A customer escalation from military with a business justification that was basically “innocent people will die” because of shapes being shifted over a few pixels in a spreadsheet.
- A manufacturing company that used Excel to run robots responsible for testing the electronics they were building on a manufacturing line. The amount of code they had written around their spreadsheet shocked me.
Contextures Posts
Here’s what I posted last week:
- Here’s how I cleaned an imported list in Excel, and split one column of data into 2 columns. There are other suggestions in the comments too.
- Can you send out a pivot table and prevent people from seeing all the source data? It’s not an easy task, but there are a few options that you can try.
- You can add headers to a combo box drop down list, so the columns are easier to understand.
- 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.
Other Excel Articles
Here are a few of the Excel articles that I read last week, that you might find useful:
- 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.
- And speaking of the Mac, one of the earliest spreadsheets was Trapeze. Its creator, Andrew Wulf, has decided to build a simple version of Trapeze from scratch for the iPad.
- Doug Glancy found that the SheetActivate event isn’t triggered if a workbook has multiple windows, so he added a workaround.
- 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.
- Scott Lyerly discovered that you can use VBA with the Ribbon in Microsoft Project, so why can’t you do something similar in Excel?
- 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.
Excel Resources
Here are some upcoming events, courses and new books, related to Excel.
- Registration is open for the Amsterdam Excel Summit. The one-day event runs on May 14, 2014, and features sessions by several Excel MVPs, such as Bill Jelen (Mr. Excel), Ken Puls and Charles Williams. All the sessions are in English, and the limit is 100 participants, so sign up now, if you’re interested.
- The European Spreadsheet Risks Interest Group – EuSpRIG –has announced the preliminary program for Eursprig 2014. This event will be held in Delft (Netherlands) on July 3, 2104.
Excel Dashboards and Reports for Dummies, by Mike Alexander.
336 pages, published March 31, 2014
Learn to: Analyze data and report it in a way that makes sense; slice and dice data from different perspectives; create eye-catching and understandable reports, visualizations, and dashboards; automate redundant reporting
Dashboarding and Reporting with Power Pivot and Excel, by Kasper de Jonge.
195 pages, published April 1, 2014
This book, written by a member of Microsoft’s Power Pivot team, provides a practical step by step guide on creating a financial dashboard. The book covers in detail how to combine and shape the relevant data, build the dashboard in Excel, providing layout and design tips and tricks, prepare the model to work with fiscal dates, and show values used in many financial reports.
What Did You Read or Write?
If you read or wrote any other interesting Excel articles recently, that you’d like to share, please add a comment below, or send me an email. Please include a brief description, and a link to the article.
[Update] Thanks for suggesting the following articles:
- Randy Rhea advises us to use a delimiter that’s not in the data, for easier imports.
- To help organize your data, Randy suggests using a macro that enters the date and user’s initials in a task list.
- Barb Darrow doesn’t think that Excel on the iPad is much use, if it doesn’t have macros.
_______________________________
Headers on Excel Combo Boxes
You can use combo boxes to show a drop down list, either in a UserForm, or on a worksheet. Sometimes a list is easy to understand, like this one, which shows a part number and part name.
If the list has multiple columns, it might help if there is a heading for each column, like the one in the next screen shot.
You could probably figure it out without the headings, but it removes any potential confusion, if you show the labels at the top of the list.
Continue reading “Headers on Excel Combo Boxes”