A Community at the Heart of Microsoft Excel Technology. Join over 4,000 Analysts in community that follow us through Excel newsletter and get the latest Excel tips from leaders in the analyst community.
Friday Fire is back with an all new episode on how to speed up things in Excel. I present two ways to make things faster:
Replace repetitive volatile formulas (like NOW, RAND, etc) with a named range where only one volatile formula resides.
Root out spreadsheet errors and handle them with formulas like IFERROR.
Do you have ideas on how to speed up Excel? I’d love to hear them.
As you can see from my background, I’ve moved to Brooklyn! I love it here, but because my life is in flux, Friday Fire will be a bit sporadic for a few weeks. Don’t worry! Rick and I working on plan to have it come out regularly.
Hi everyone! After some hiatus, Excel.TV is back and ready to begin sharing to our community like never before. We’ll be doing that with all my new series, Friday Fire. It’s designed to help you end the week on a high note, with tips, tricks, comedy, interviews and more. But the most important part of Friday Fire is you!
Tell us what you’re looking for and we’ll try to make it happen. Have a great Excel tip you want to share with the world? Let us know in the comments. We might ask if we can share!
This is our second year doing our reader survey. Late in 2017 we asked you about what you liked and didn’t like. In short, we wanted to understand the demographics of our audience so that we create content and products that are aligned with what you expect when you come to a website named “Excel TV”.
Now that we have 2 years worth of data, we can start to see if our demographics are shifting. Where appropriate, I have compared the results for the past 2 years.
Percent Male %
Between Ages 45-54
Between Ages 55-64
Level: Intermediate Manager
Country: United States
Country: United Kingdom
Similar to last year, the demographic looks a lot like me. A middle-aged male, with a numbers crunching background in a Western, English speaking country. Not too many surprises there. There are some interesting movements in the demographic from last year, though. So let's dissect it a bit (the analyst in me just can't help it).
Compared to Last Year
We can't dissect this too much due to the sample size. 58 vs 123 respondents isn't exactly a huge sample size. But some of the movements make sense based on what happened in our business last year. So here goes...
Percent Male dropped and visitors are slightly older than last year. No idea why there are fewer males. There is a possibility that one of our larger female affiliates in Australia (thanks Danielle) helped to influence this. Respondents being older may be because we started selling products last year. Our courses are premium Advanced Excel courses and are not meant for the frugal buyer. This may impact the age of our visitors. But all in all... I think that the age & gender of our respondents is more likely due to our smaller sample size last year. We shouldn't read to much into it.
Less Finance and more Accounting visitors. One of our affiliates that supported our courses in 2017 provides CPE credits to Accountants. This influx of traffic (predominately USA traffic) likely influenced the results.
More USA, UK and Australia visitors. If I had added an additional row to the table, Canada would have been fourth. This is likely driven heavily by our American accents and our connections with other partners/affiliates in Australia, Canada and the USA. Last year we released an Excel PivotTable Course by Ken Puls (a Canadian). These countries tend to be considered "buyer" countries in the online market. So I'm not frustrated at all by the results. But we will eventually need to appeal to a more geographically diverse audience.
Compared to Facebook Audience
Percent Male %
So our social media following is much different than our website following. This is likely because an early follower of ours on Facebook was Rahim at Excel Basement. Here is his Pakistan based Facebook Page. He suggested others follow us, which heavily slanted our early social media following towards the Middle East. Not much to learn here other than our early success on Facebook hasn't necessarily been what's fueled our growth in 2017. Check out the Excel TV Facebook page or our popular Excel TV Facebook Group
About The Reader
Things he gets asked about most in Excel: Pivot Tables, Power Query, VLookup, Data Cleansing, Formulas, VBA/Macros/Automation, and Database Connections
What hasn’t worked for him: Excel Books, Google Searches, Most Youtube Channels and Udemy
Sample of things that keep him up at night include: VBA, Automation, Data Cleansing & Presentation
He is most interested to learn about Power BI, Power Query, SQL & Sharepoint
His Views of Excel TV
He doesn’t need to know much more about the Excel TV hosts. One person was curious about how Jordan keeps his curls. And another expressed that they liked the old Youtube "show" format and that in its current form the "website" isn't particularly unique in the Excel community. Spoiler Alert: Season 4 of the show will be back this summer.
Regarding purchasing a future Excel TV product… he already feels confident in the Excel TV Hosts but would need to see a product that fit his needs at a price point that made sense to him. In short… price, time, quality, and need.
Interested in topics like Power BI, Visualization and Predictive Analytics
If you are a survey geek, then I will share the full results with you by following this link
Audience - Our average reader/viewer is a male (that's why I keep saying "him/he") that is in Finance/Accounting and is getting older. A decade older than last year. Advancing in his career and has crunched numbers for a while. He just wants to get the damned report out. Automate it if he can. And hopefully at a price that his employer will pay for.
Topics - The average reader is quickly moving into advanced topics like PowerBI, PowerQuery, Sharepoint, and Predictive Analytics. While the current form of the website is set up as more of an Excel and Data Visualization hub. Excel TV is in the finishing stages of a Data Science Course (Power BI & R). But there is scant free content on the site around these topics. We need to consider this in our product and content roadmap. In short, our readership is maturing. And as such, they desire more advanced topics that help them get through their day at work. Perhaps shorter courses/tutorials that hit more precise problem areas would be useful.
Products - Average reader isn't opposed to our courses and products but needs them to hit a price point for purchase. Excel TV will need to look for ways to meet the value that the customer expects. The courses are actually underpriced, as all courses are facilitated by Excel MVPs, Authors, that are experts in the field they are teaching (this is unique in the Excel blogger world). But we must find ways of either communicating that value, or we will have a difficult time competing in the market longer term. This may include lesson giveaways, etc to let users appreciate the value
Content - Admittedly, our FREE Excel content has become stale. We last recorded a show almost 2 years ago. By no means will we likely ever return to the 20 shows we did in our first season. But there needs to be fresh content coming out of Excel TV on a consistent basis. This became difficult for us 2 years ago because we needed a way to show our loved ones that the effort we put into this 'labour of love' was worth all the time it took away from our families. So we spent some time creating a business model that works for everyone. We are now ready to ramp up the content again. The Excel TV show is scheduled to return in the summer of this year. Additionally, our resident Excel MVP Jordan Goldmeier will be unveiling fresh weekly content very soon. Stay tuned.
Are we on the right path? Did you see something different in the survey results? Want to know how Jordan keeps those curls kickin'? Hit us up in the comments section.
Oz has dedicated this episode to explaining the importance of ‘understanding data’. It is different from ‘doing data’ or ‘following instructions’.
Let’s get to it.
1 – What’s What
Following instructions is a mindless task. And often, in face of exceptions, it is improbable the set of instructions would cover them.
Doing data is about truly being engaged with data and with the tools that help with cleanse and analyze it. This involves understanding data generation processes, e.g. knowing where are the weak spots in them and how the analysis would need to be adjusted.
Understanding data falls in the middle. It revolves around an understanding of the business and its processes. People who understand data may not know about pivot tables or database queries, but they would know about the reports available. They would know about the business rules and how everything fits together. Most importantly, they would know what report can be trusted for what data.
2 – Why You Need Someone Who Understands Data
When Oz works for an organization, he goes as an outsider to develop solutions. Partnership with someone inside the organization who understands data helps get work done much more efficiently. Someone who understands data can anticipate the questions Oz might ask and would be able to warn about the credibility of certain sources.
This means that Oz would not have to analyze the processes end-to-end himself to understand data quality. This also means the solutions developed need not be complex.
3 – Why the Company Needs Someone Who Understands Data
Even if a company subscribes to a third party to provide some reports, these reports tend of focus on very specific aspects of the business. Then, when the company want something else, these third parties either can’t do it or will charge an exorbitant sum for it. But, if the company has people who understand data, then all they need is some data analysts to turn these already existing reports into a solution.
4 – Final Advice
Today, everything is data: sales, website visits, inventory levels, staffing. And playing with data is like playing with fire. Someone who understands data can help control this fire.
Share write your thoughts with us in the comments section below. And keep that data clean!
In this episode, Oz discusses the dos and don’ts of a sticky situation: when one’s data analysis is wrong! If your work involves a lot of number crunching, do read it.
Let’s get started.
1 – The Situation
Oz was processing commissions for some month and the numbers just looked plain wrong. He checked the previous month’s file and found out a blunder in his analysis: a person got paid $5,000 than they should’ve gotten last month. Although he feared looking stupid and getting fired, he still informed his superiors about the error.
2 – What Not to Do
Not taking ownership of one’s mistake and trying to hide it is a serious problem. Once caught, it can lead to dire consequences: from losing one’s job to landing in jail.
3 – What to Do
In each organization, there are processes and review mechanisms in place to catch such errors. But even than some errors might just slip away. The important thing is that the error was eventually caught and Oz took complete ownership of it. Also, the error highlighted a weakness in the processes governing disbursement of commissions.
4 – What’s the Take-away
Firstly, the blame does not rest with the tools an analyst uses. The analyst needs to own up to it.
Secondly, playing with data is like playing with fire; sometimes one will get burnt. It is important to accept and learn from one’s mistakes.
Share your experiences and advice with us in the comments section below. And do not forget to forward this video to your friends and colleagues.
Rick: Next up is our special guest Jon Acampora. You may remember Zack Barresse mentioning Excel MVP John Acampora of ExcelCampus.com. Jon, say hello and tell us what ExcelCampus is about?
Jon: Hello and thank you for having me. ExcelCampus is a blog that is about learning how to use Excel and saving time with your everyday tasks. I have a bunch of blog posts with in depth articles on there. I do videos and video tutorials. I have developed quite a few add-ins that you can download. The majority are free. It’s growing every day. It is a fun place to hang out and learn about Excel.
Oz: How did you start? Did you grow up saying I want to build Excel add-ins?
Jon: Who says that? I want to meet that person. It started in my first job after college. They were using Excel for everything; inventory, planning, accounting. I think most small businesses use Excel for a lot of things. I was immersed right away and fell in love. It’s hard to get away from it if you are in the finance or accounting world. I think it’s cool. It’s been a journey ever since. I started developing add-ins. Every company I’ve worked for has had problems with processes. There is a lot of room for automation. So that is how I got into add-ins; seeing the need to do things quicker and efficiently. I hate doing repetitive tasks. I get bored pretty quick. With Excel, you can program it. It is an amazing tool. There are endless opportunities.
Jon: Yeah, part of me does. I’m very open to learning other languages. But there are companies still using Excel 2003. I don’t see it going anywhere. So it doesn’t scare me. I’m fine with that.
Jordan: You should not be fine with that!
Oz: What do you do day to day? Do you do consulting? Do you meet with small businesses?
Jon: I still have a day job. I’m a finance manager for a software company in Southern California. I just started doing the blog on the side four years ago. I really didn’t start doing much until the last year when I started writing more articles, posts and different VBA utilities. I’m still doing the day job.
Rick: You talked a little bit about Tab Hound and some of the software you are putting out there. Will you talk about how you went about developing an add-in? What is the process like?
Jon: It is not easy. I think my first add-in was a tool bar I developed internally for a company I was working for that was using an Oracle product for forecasting, planning and that sort of thing and then a spreadsheet interface called S Base. I developed a tool bar in the ribbon that automated a lot of tasks because Oracle sunsetted that product. I have done smaller scale automation before using the ribbon. I used Ken Puls book to learn about the ribbon. From there it was, “Wow, there is so much potential to do other things to streamline processes and automate things that you might spend time doing.” I’m still working on a lot of different add-ins. I have a lot of ideas.
Oz: I’m curious about when you go to develop an add-in. Where does that happen? People don’t know about going to developer tab and pulling up tabs. At what point are you in add-in territory versus formulas or VBA? Where does the add-in happen?
Jon: Alt+ F11 is the short answer. You can start writing macros there and you can write modules in your workbook. Everyone learns usually by recording macros using the button on the left-hand side. You can record macros and then you will be able to figure out what you did. The code is easy to read. It is called Basic for a reason. It’s very powerful at the same time. You can save a lot of time if you’re doing repetitive task all day.
Oz: An add-in is written in VBA code?
Jon: Yes. Basically, an add-in is installed in Excel and it opens up every time. So you have that code there every time you open up Excel. A macro is just limited to a workbook whereas an add-in takes it a step further and is in Excel no matter what workbook you are in it operates.
Rick: On the live show blog, Ryan Wilson is talking about how he used it. I want to encourage everyone to answer questions on John Acampora’s blog. Jon, give us an idea of an Excel add-in. What are you talking about when you talk about an Excel add-in?
Jon: On my downloads page of my website, I have a list of add-ins I have developed, such as Tab Hound or Quarter Sum Formulas; tasks we do quite often. This add-in sits in the toolbar. Just click on it and it will automatically create the formulas you need. Microsoft itself is developing a lot of add-ins like Power Pivot and Power Query to make life easier with sets of data.
Rick: Can you let everyone see your page?
Jon: This is just some of how my add-in works. It downloads in a zip file. I have articles on how to install it to step you through installing an add-in in various Excel versions. The add-in tab will be there every time you open Excel. On this page, there are details on how it works.
Jordan: Two things. First, we can tell how cool you are because I see your bookmarks are surf and snow. So, you’re a cool guy. Bookmarks are like your radio presets.
Jon: I live in Southern California and my wife and I surf quite a bit. It’s a lifestyle we strive for.
Jordan: Second, can you tell us about the name ExcelCampus? How did you come up with that?
Jon: That’s a great question. When I first started doing this, I read a book on blogs and content marketing. I bought a few different domain names with the word ‘Excel’ in it. I didn’t like any of them like SpeedyExcel. I wanted to talk about doing Excel things faster. Then one day, I was driving home and I drove by a road named Campus by a college campus. That was it. It kind of stuck. It was all a road sign.
Rick: That has to be another whole episode; available Excel URLs.
Jon: Yes. That’s true. I bought quite a few.
Rick: Even the bad ones are taken. You think you have a good one, but there is no way it is available because it’s bad. Excel on a stick, or something, gone! Not only .com, excelonastick.ca,.net, .info.
Nick Green of EchoXL.com joins Excel TV host Rick Grantham and cohost Microsoft Excel MVP Jordan Goldmeier and author and Excel TV Host Oz du Soleil on Excel TV discussing the development and release of his Excel forecasting tool at EchoXL.com.
Rick: Our special guest is Nick Green from Columbia by way of Australia. Welcome to the show, Nick. Will you start off by telling everybody about your Excel background and your background in general?
Nick: I was introduced to Excel in supply chain industry after university. I didn’t learn much Excel studying business in University, which is funny because I use it in 50% of my work. Supply chain is quite a chaotic industry and chaos tends to mean people use Excel a lot. So I was forced to self learn. I wish I had studied VBA more formally because it would have saved me a lot of headaches. If you don’t have the structure for it now you pay for it later.
Rick: Can you explain about the EchoXL name? What is your site and where did the name come from?
Nick: EchoXL is a forecasting tool. When I think of forecasting, I think of echoes because the further away you look the softer your signal is much like an echo. And, echo is short. EchoXL.com is a forecasting tool. I’ve used a lot of forecasting software as a demand planner on my job and I thought those tools were built by statisticians for statisticians. There were so many people who had to do forecasting that were intimidated by these tools so I made something with simplicity to get them 98% of the way while automating and simplifying the process. That is what EchoXL tries to do.
Oz: How do you get from chaotic to meaning people use Excel a lot?
Nick: Because a lot of these companies have multimillion-dollar, streamlined software but Excel is popular among users because people underestimate the need for flexibility. All these businesses have quirks in their softwares that need to be adapted. Excel becomes more relevant, especially in supply chain.
Oz: What is an example of how things change fast in supply chain?
Nick: For example I worked for a cosmetics retail company where I had to forecast sales and make sure their inventory and stock was in the right location across the world. Each location had quirks. Inventory would have to meet special events or did not meet [historical] sales. Their software was rigid and wouldn’t cater to that, so we had to build similar software with Excel. There isn’t much out there with Excel forecasting tools in general. I’m trying to fill that void.
Jordan: When you built your tool, you had a version with no VBA. What advantage is there to taking VBA out and why did you want to?
Nick: The first version I sent to you was a beta version with VBA. I started with VBA and tried to make it work like an application. The ribbons were automatic, but I found when I sent it to people they would have problems setting it up. It wouldn’t get through the firewall or you couldn’t use it. If you’re sending it to friends, that’s OK. But if you’re sending that to customers, they get impatient and give up. I wanted to make something completely stable. If you work with constraints and a little VBA, what is embedded in Excel won’t work. Echo light has no VBA. I will be making more advanced versions with VBA that will be more heavy duty for organizations.
Jordan: I still love the VBA, but over time, as I learn more formula tricks, I came to feel like it’s overused. In a certain sense, less is more. You can do a lot of sweet things and efficient things with formulas and you didn’t have to use a run button. [Jordan hates run buttons!]. Formulas may be difficult, and VBA is easy, but I hope more developers will do more with Excel formulas because you can’t break them and it makes a lighter and tighter Excel application.
Oz: I would add, from bloggers that pointed out in the past, that if you have some big complicated formula, you are more likely to find somebody who can help you figure it out. But if you do VBA, you’re going to have a tougher time with somebody figuring it out. And when you get into writing code, a person may be more likely to say, “I don’t want to figure it out, let’s start from scratch.” So formulas are good for those reasons. I try to stick with formulas.
Rick: I see some irony in this, Jordan. This will probably be Bill Jelen’s favorite day. I’m a guy who owns Option Explicit VBA Blog (and author of Dashboards for Excel) thinks VBA is difficult!
Jordan: I know I have evolved. I can’t argue.
Oz: We have to have room to evolve or we have politicians that are 60 years old answering for things they did when they were 19. [Insert Chappaquiddick or other relevant pun here]
Rick: Nick, you use quite a few forecasting methods, can you discuss those and how you integrated those into Excel?
Nick: There are four forecasting formulas, but the most important one is called Winters Forecasting. Jordan is familiar. It is triple exponential smoothing. You need to run formulas in the background and hide them from the end-user because it’s complex. You are basically separating your trending and seasonality from your forecasting. You have variables that will determine how far back the forecast will look, for example several months or several years. I did everything in a processing sheet. You can go to VBA if you’re not enabling macros and you can go to Properties Section to hide the sheet. I think they are less breakable if you hide the sheet. It took a lot of trial and error and headaches, but it integrated them.
Rick: We talked about part of the development process. I did things in VBA and then I realized that was a problem. John Peltier talked about when you build for yourself, and then you build for your friends, there is growth in that. Then when you send it out to other people, you go to a further level of development. Can you talk about what led up to that? What did that development process look to you, for the benefit of our viewers?
Nick: Like most of your viewers, I built applications for myself and close colleagues. But when you have to build it at scale for people you don’t know, you have to hold their hands through using the template. The difference is you have to think about that user interface design or user experience. You have to imagine what the lazy impatient user is going to think almost like you’re writing an article. People using things for the first time want it to do what it’s supposed to do and not be annoying. Simplify. I added many features and I asked myself if the benefit of that feature was worth the extra weight and the extra complexity it added to the interface. I had to trash so many ideas and features I had spent days building.
Oz: Can you give us an example of what is a good idea that might not be worth having?
Nick: Initially, I had more tabs that would allow the user to control everything such as custom seasonality or weight given to Christmas sales, but the benefit was so small versus the learning curve and confusion introduced. So even though I put my sweat into it, I had to be honest and scrapped it.
Jordan: for our viewers, Nick, when we’re talking forecasting, for less statistically minded, we’re not just talking a weather forecast. Can you give information for forecasting in your world?
Nick: There are two kinds of forecasts in business and demand forecasting. There are finance people and engineers who have to forecast and predict sales of a product, say. The main one I’m referring to is historical forecasting where you’re looking backwards at historical trends and seasonal flows for a typical consumer product, for example, products with sales popular at Christmas, but diving in January. You use statistics to rationally predict and extrapolate the data into the future, basically relying on guest work.
Jordan: Using historical data to make guesses about the future. The different methods you have in your tool account for trends or cycles and looking at the past to get a pretty good guess about the future.