Excel tips and tutorials
After you record or write a macro in Excel, you can run the macro, or go slowly through it, line by line, to see if it is working correctly. I use the F8 key, in the Visual Basic Editor, if I’m troubleshooting a macro, to see where something is going wrong. You can use also use the Step Into command, in the VBE’s Debug menu.
However, for the past few months, something was going wrong with the “Step Into” command in Excel 2010, whether I used the menu, or the F8 key.
There is a free app available for Excel (2013 or Online version), that lets you create 8 different chart types, including some that aren’t standard Excel charts, such as box plots. The app is called XLMiner Data Visualization, and it’s created by Frontline Systems, which also makes the Solver add-in.
If you log in to your Microsoft account, you can download and install the app from the Microsoft Store. Then select data, and the chart appears in the app’s window. In the screen shot below, you can see the box plot chart that I created, in just a few seconds.
The free version of XLMiner lets you do the basics, and if you want more features, you can upgrade to the Pro version. It costs $995, about the same as Tableau Desktop. And if you want quick, fancy charts, at a much lower cost, take a look at Jon Peltier’s charting utility.
Last week, I was creating an Excel file with sample data, to use for a few experiments. But don’t worry, they weren’t mad-scientist-type experiments – I was doing Power Pivot experiments, and needed some data to play with.
I needed 2 types of data:
Does Ed Bott include your favorites in his list of 6 Excel power tips?
He included one of my favourites – Paste As Values. However, I usually Paste Values by dragging with the right-mouse button pressed, instead of using a keyboard shortcut. You can see that technique in the very short video below. Do you use the mouse or keyboard for Paste As Values?
Or watch on YouTube: Change Excel Formulas to Values With Mouse
When you try to use the Top 10 filter, on a list that already has some filters applied, the results probably won’t be what you want. The Top 10 feature ignores the filters on other columns, and just returns values that are in the overall Top 10.
Recently, I showed a workaround for that problem in this blog post: Top Ten Values in Filtered Rows. In that example, I added a new column, and used the SUBTOTAL function to show the value, then filtered that new column. Hidden rows would have a value of zero, thanks to the SUBTOTAL function, so they wouldn’t be included in the ranking.
The Office 365 Garage Series has a video on what’s new in the Office Apps, and they demonstrate some cool new features. The Excel App is mentioned at the 7:12 mark, when they look at the new APIs for formatting.
However, at the 3:12 mark, they downplay the old development methods, including VBA and COM, and rave about the new methods — HTML and JavaScript, that are used for the online versions of Office.
Apparently it’s almost impossible to find people who are still working in the older development platforms. What do you think of the chart they use to “validate” the trend? Are they really using a sample size of 12 COM jobs, compared to a final number of 5 JavaScript jobs? I’m not a statistician, but that doesn’t qualify as “dramatic decline” versus a “huge insurgence” in my opinion!
I did like the way that he drew on the chart though, to change it from a column to a line chart. You can see that at the 4:20 mark in the video below.
Or watch on YouTube: What’s new in the world of apps for Office?
Thanks to AlexJ for suggesting a great use for the REPT function – setting a minimum row height. He uses this technique to add a bit of spacing in his tables, so they’re easier to read.
You can watch the steps in this video (or watch it on YouTube), and the step-by-step instructions are below the video.
For example, here is my To Do list, with a few items to work on, around the house. Most of the Task Descriptions are short, and fit in a single line.
Here’s this week’s Excel roundup, with news, tips, and announcements.
Here’s what I posted recently:
Here are a few of the Excel articles that I read recently, that you might find useful:
Here are some upcoming events, courses, recently published books, and other new items, related to Excel.
| Excel Tables: A Complete Guide (pdf version) by Zack Barresse and Kevin Jones ”The book begins by explaining what tables are, how to create them, and how they can be used in reporting before moving on to slightly more advanced topics, including slicers and filtering, working with VBA macros, and using tables in the Excel web app. Novice Excel users and experts alike will find relevant, useful, and authoritative information in this one-of-a-kind resource. “ |
If you read or wrote any other interesting Excel articles recently, or have upcoming Excel events, please share a link in the comments below, with a brief description. Thanks!
_____________________
____________________
There are a few sample files on my web site that let you automatically add new items to a drop down list. The most recent version works in newer versions of Excel, and uses named tables to store the lists.
When the code detects a new item, it asks if you want to add it to the drop down. If you click Yes, the item is added, and the lookup list is sorted in alphabetical order.