My first thank you messages were directed to people who helped me learn Excel. This, my fifth Thank You, goes out to the organisation that has helped me share what I had learned with others. Thank You CPA Australia for publishing my first Excel article, way back in May 2002. Thank you for agreeing to do a regular Excel article in late 2002. The Excel Yourself series started out with a Q & A format. Initially the questions were based…
Over the years I have had many requests to help people insert blank rows between entries is a list. Apparently it is for an input routine that requires blanks. My normal solution is a macro because it automates the process, but there is a manual technique that is quick and easy. (more…)
Recently I learned about the passing of Chip Pearson an Excel legend. He was so generous with his content and I had thanked him for his contributions years back but it got me thinking about thanking others who had helped me along the way. The fourth on my Thank You list is Bill Jelen (aka Mr Excel). Through the Mr Excel website Bill has directly and indirectly helped more people than probably anyone else in Excel. The site I have…
The Step chart is not a standard Excel chart but it is a useful way to display values over time. You have probably seen a step chart but you may not have known what it was called. It sort of looks like the city skyline or something you would create on an Etch-a-Sketch. (more…)
My free Excel webinar for May 2018 covered Text functions. Download the materials using the button below and watch the video. You know how well Excel handles numbers, but not everyone knows that Excel has built-in functions and features to work with text as well. This session covers Excel’s text functions and features, in it you will learn the different techniques to split text techniques to extract text from text how to easily join text techniques for tweaking text for…
Recently I learned about the passing of Chip Pearson an Excel legend. He was so generous with his content and I had thanked him for his contributions years back, but it got me thinking about thanking others who had helped me along the way. My third thank you goes out to Matthew Harris. Here is a link to his website Back in the 90's I taught myself VBA using his book - Teach Yourself Visual Basic For Applications in 21…
Cell comments are useful for instructions and documentation. If you want to make all the comments on a sheet visible, use Alt v c pressed in sequence, not held down. Once visible this shortcut also hides all the comments in one go. This is an old Excel 2003 shortcut that still works.
Let’s assume you have a large table that you are filtering. Based on the current filter you want to work out the earliest date and the latest date. You may be surprised to learn the SUBTOTAL function can help you. (more…)
Recently I learned about the passing of Chip Pearson an Excel legend. He was so generous with his content and I had thanked him for his contributions years back but it got me thinking about thanking others who had helped me along the way. My second thank you goes out to Jeff Robson from Access Analytic. Jeff hired me as an Excel consultant way back in 2006. He asked me if I knew someone who could do the job and…
Pie charts have a lot of drawbacks and limitations. One major limitation is they can’t handle negatives. One of Excel’s new charts can help out. (more…)
Last week I learned about the passing of Chip Pearson an Excel legend. He was so generous with his content and I had thanked him for his contributions years back but it got me thinking about thanking others who had helped me along the way. The first is John Walkenbach. John's books got me started in Excel. He has written about most versions and most topics on Excel. I sent him an email - an extract is shown below.
How do you know if a cell is locked in Excel? There is an easy way to find out. And it also makes it easier to lock or unlock the cell. (more…)