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.
Advanced Excel Tutorial: How To Increase VLOOKUP() Speed up Excel | Excel TV, Episode 54 - YouTube
Several people pushed back on the video I release this morning and I want to thank them (and in particular Wim Gielis). This caused me to open my laptop on the train while on the way to a client’s office (so I could see if I was wrong ... which I was!). When I realized I was wrong, I became so deep in thought I missed my stop. And that’s when my phone died.
So I have a major apology to make. A few months back I created a solution that shrank the size of arrays my client was doing SUMIF and COUNTIFs on. I used a method similar to the one presented in the video I released today. It made their spreadsheet much more manageable.
But I wanted to adapt it to make it easier to understand. And, in doing that, I really lost that VLOOKUP already has a faster way to operate. So, the video I presented, while interesting in its technical solution is not the BEST way. The best way would be to change the VLOOKUP parameter to false.
Anyway, I think it’s important to learn from mistakes. I don’t plan to take this video down because I don’t want to hide behind my error. But I will add a note at the beginning of the video later today when I am back home.
Thanks to everyone who watched it anyway. My desire for a complicated solution is sometimes so great that I miss what’s already in front of me.
Wim Gielis, wanted me to also add this note: "an approximate lookup can lead to a wrong result. All people looked up should be part of the database. Or test for it. Second caveat, database must be sorted on names."
In this all new episode of Excel.TV, I show how you can make your spreadsheets wicked fast with Excel using VLOOKUP.
Those who know me know I love the advanced stuff. And this tutorial could have come out of my book Advanced Excel Essentials.
It works like this. Most of the time, when we use lookup formulas, we grab the entire table range we're interested and perform the lookup on that.
The Problem with Lookups in Excel
Lookups in Excel essentially work like this: they start at the top of the cell range and check if that cell is a match. If it's not, it moves on to the next one. We would say then that in its worst case scenario, it would have to look through a total of n-items. That is to say, if the size of the first column has n items, then we can think of its performance bounded by the nth item, since it would have to go through all items at worst.
We can dramatically reduce that n by decreasing its search space. But how?
Enter the Lookup Table
I compare the lookup table to a phone book (remember those?). You don't search the entire phone book for the person or business you're looking for. Rather, the phone book is broken down to a section for each alphabetic letter. And all information is stored in alphabetic order. This dynamic essentially helps you avoid looking where you know the information isn't found. We can do the same in Excel by finding where each new letter in the list starts and then creating a dynamic range based on those boundaries.
It's all in the video!
Inasmuch as I would love to write a tutorial here too, this is one of those cases where you'll learn more just by watching. There are many moving parts and the video brings them all together.
Excel Tutorial Concat Function in Excel with TEXT JOIN - Excel.TV Episode: 53 - YouTube
And we're back...
We took a brief hiatus for the holiday season here in the United States (Passover, Easter, and 4/20) but we're back and better than ever.
Say "Hello," TEXTJOIN
This week, I wanted to introduce you to my next best friend, TEXTJOIN. TEXTJOIN was added to Excel a few years back, and came about - I kid you not - as a result of years of everyone complaining. Well, that's the way I think it happened.
What can I do with TEXTJOIN?
TEXTJOIN allows you to combine data from different cells into one. That might not seem so novel, but remember the old way of doing things: you would use the CONCATENATE function (or CONCAT for short) or ampersand ('&') to connect different cells into on cell in Excel. If you had 100 cells, you would have to click each cell 100 times (Chandoo had a trick to avoid this but it's no longer necessary under the TEXTJOIN regime). Hence, like I said, TEXTJOIN came as a result of our aggravation at the current way of doing things.
In the video...
In this video, I quickly show you the old way to bring multiple cells together into one cell and then show you how TEXTJOIN awesome improves upon it. So make sure you watch the video at the top of this blog post to get everything.
In the meantime, here's a quick break down of it how it works:
delimiter - Here you can specify if you want a delimiter to separate each text item when you combine them. In the example, I start with a space "" (literally, two quotation marks with nothing in them), but you can also do comma (",") or multiple spaces (" "). It's really up to you.
ignore_empty - This is a TRUE/FALSE option that lets you tell Excel if you're interested in ignoring empty cells.
[text ...] - Place all the cells you want to combine into this argument.
Hit ENTER and watch the magic happen!
Get the download files
Click on the button below to get the download files used in this video.
How to Use Sum in Microsoft Excel Power Query (Fun Tutorial) - YouTube
Let's say Hi to Szilvia!
This week Szilvia shows us some awesome Power Query tips in the context of something many of us do - bar trivia! Excel.TV's own high priestess reports from sunny, southern California, where spreadsheets are the brightest you've ever seen.
Excel Tips & My thoughts
As someone who is just really getting started with Power Query and Power BI, I was really looking forward to this video. One of the things Szilvia shows you how to do is to get data into Power Query with just a named range! - that's right, you don't need an Excel Table. I thought that was pretty cool.
She also shows you how to make that top row into a header row, unpivot columns (one of my favorite features) and how to regroup everything into summarized results.
I love how she uses this in the context of pub trivia. But if you think about it, you can use this example on your own dashboards. It does require that you press an update button. Those who've read my books know my strong opinions about run/update buttons. But still, it's a pretty cool trick! In fact, I kinda love it!
Free Webinar: Building an Awesome Data Model in 7 Easy Steps
We're excited to let you know, we'll be running Szilvia's webinar again very soon, which deals with building data models from scratch. The name of her webinar is Building an Awesome Data Model in 7 Easy Steps. Her webinar is by far one of our most popular, but we haven't run it in over a year! Last time we ran it, someone said it was the BEST webinar they'd ever been to (this is Jordan writing this, by the way, and they didn't say that about mine!).
In any case, we would live if you joined us for it. Click the link below. We're still working like busy beavers behind the scenes to make sure everything is up and running. If we're ready to go, the registration page will be up!
Microsoft Excel MVP Oz Du Soleil Supercut from Classic Excel TV episodes - Episode 50 - YouTube
Excel.TV's 50th Episode!
It's finally here! Roll out the red carpet, prep the T-shirt cannons, and turn on that fire house! BECAUSE. TODAY. EXCEL. TV. RELEASES. IT'S. 50TH. EPISODE.
It's Oz, like you've never seen him before!
We took some of our favorite clips from the last several years and cut them together into one of the greatest clip shows known to man and paperclip.
You'll laugh, you'll cry, you'll wonder what happened to Clippy! (And one point, Oz tells a person in a Clippy costume, "I thought you were dead!")
Where is Oz now?
Oz now has his own YouTube channel. He is known for putting together some of the most helpful and creative Excel videos out there. In fact, over this last year, I was asked by several people if I'd met Oz! pfffffffft!
He's taking the Excel and YouTube world by storm! Check him out:
There are probably more hilarious clip shows in the archives. Szilvia recently reached out to me and suggested we do a Where are they now? episode. We think that's a great idea! And, we're open to new ideas -- especially yours! -- as we get our video machine back up and running.
Budgets vs Actuals Target Chart in Microsoft Excel - Excel TV: Episode 50 - YouTube
In this week's episode of Excel.TV, we show you how to build a budget vs. actuals chart. Really, though, this is what I call in my book Dashboards for Excel a performance-against-context type chart. It basically says: We have what we did against some context (in this case, a target). Take a look below.
In this episode, I show you how to make the chart on your own. It's a really quick process:
Start with your data
Place the data onto the a 2d clustered column chart
Align the series to be on top of one another
Change the chart type of your target series to a line chart
Add markers and remove lines
And that's about it. The rest is formatting.
1. Start with your data
In our example file (you can download the file at the end of this post), we start with a list of accounts and their associated actual and budget amounts.
2. Place data into 2d clustered column chart.
We can insert a 2d clustered column chart by going to Insert > 2d clustered column chart from on the ribbon tab.
Once we've inserted a blank chart, we can highlight the data in the table above and press CTRL+C to copy. Next, we'll select the blank chart we've just inserted and press CTRL+V to automatically paste the data into the chart.
3. Align the series to be on top of one another
Next, we can right-click onto our budget series (the orange one in the figure) and select Format Data Series....
From the Format Data Series context pane, we'll set the series overlap to 100%.
4. Change the chart type of your target (or context) series to a line chart.
From here, we'll right-click our target series (the orange one in the example) and select Change Series Chart Type....
This will bring up the Change Chart Type dialog box. From here, we'll change the Budget series chart type to a line.
5. Add markers and remove lines
At this point, we're ready to add markers and remove the lines on the line chart. From here, you can right click the orange line and select Format Data Series....
From in the Format Data Series context pane, select the Paint Bucket icon and then the Marker sub menu. From within the Market Options field you can select built-in, type "-", and increase the size to something larger as I have in the image below.
You can then remove the line on the chart from the Line menu (click on the Line option next to Marker option - use the image above for reference. From there, select No Line.
And that's about it! The rest is just formatting!
If you wanted to know how to format the chart like I have, make sure to watch the whole video! And down't forget to snag the download file.
Well, another Microsoft MVP Summit is now one-and-done. It's my sixth year as a Microsoft MVP, and so much has changed since the first time I went back in the day. Excel 2013 was in development and many of us were still using Excel 2010! Crazy!
Reflections on being in this space, and a big thank you
When you start developing in Excel, you remember the blogs that really helped you out. Many MVPs, however, have segued their work into the entrepreneurial space. That education - how to run a business consulting and training with Excel - is something that many blogs don't teach. For me, talking to experts who've done it is invaluable.
And so, I would like to give shout out to four Excel MVPs in particular who've helped me in this journey. They aren't just Excel experts, they're also experts at really understanding what Excel users need and creating outstanding products (many of them free!). They're as much friends as they are mentors.
Four Simple Tips to Land A Job in Analytics and Data Science (and Make More $!) - Episode 49 - YouTube
This week, we take a departure from our normal Excel tips and focus on a more applied skill: getting a job with your Excel skills (and related data work). Most of us aren't just Excel-ers, we're much more: data visualizers, process improvers, etc. What we do with Excel we also apply to other code -- and even to other processes. I want to help you take your skills as an analyst and to land a perfect role that'll pay you well!
For the last ten years, I've heard that Data Scientist was the latest, sexiest job. We hard, among other things, that the demand for data scientists was far greater than the supply. And, of course, slightly before that, we said the same thing about computer science. As a result, there are now tons of resources on training people to work in data.
Look, there's no doubt there are new data jobs popping up left-and-right. And, having made a career in it, I can tell you that's very fun and rewarding.
But the emphasis on training, bootcamps, etc and the coming up with definitions for data scientists and business intelligence analyst and the like has lead to a very confusing and overwhelming landscape. Those who've begun working data simply because their job evolved into that process are finding a hard time taking that experience into more data-driven roles. And those with certificates, masters degrees, and bootcamps aren't much better off.
What the video!
So I want to help you clear the air. This video provides you will four tips to help you land that role. I provide advice - some of it unconventional - to help you realize your full potential.
I really break down the process to the following items:
Being bold and confident
Creating an awesome resume
Acing the Interview
Iterating on what you've learned.
Get my FREE Guide
To help you in your journey, I've created a FREE guide, 4 SIMPLE TIPS TO LAND YOUR NEXT JOB IN ANALYTICS AND DATA SCIENCE. Click the button below:
Build Your First Excel Power BI Dashboard Report - Quick Tutorial - Episode 48 - YouTube
Introduction & Updates
At Excel.TV, we’re trying something different. We want our videos to be more than just Excel tips. Instead, we want to help you excel your skills, your business and career.
That means, we’re going to focus on content that helps you do just that. So watch out, we’re going to start featuring content around:
Landing that perfect job! Today’s analysts sometimes didn’t plan to be analysts. Some of you started in IT. Some of you started out as creatives. Some of you graduated with degrees in finance, and with so many options and bootcamps (and the ever present exuberance around data science), how do you stand out from the crowd to land the role? We’re gonna help you.
And we’re moving into related technologies birthed (and inseparable) from Excel! We’re talking M language, DAX, and Power BI – and even R and Python.
And we’re ready to here from from you! We want your stories, tips, tricks and helplful notes.
My Power BI Journey
You all probably know that I’ve not been a Power BI person. Last Excel.TV episode I said it wasn’t fun. That I missed the challenge of Excel. But I got a big confession to make: I WAS WRONG IN EVERY SINGLE POSSIBLE WAY.
Seriously, Power BI is bomb. It’s baller. It’s like someone took my brain and the way I think and created a program out of it. Don’t get me wrong, I love my SUMPRODUCTS. But I can’t help but marvel at what Power BI is and what it’s capable of.
So Watch Me Make My First Dashboard
Well, it’s technically not my first! But, it is my first in a long time. In the video I review several dashboard components including:
The Map Visualization
And look how quickly I was able to develop my work? Pretty neat, huh?
Also, a tremendous thank you to Alex M Powers of ItsNotAboutTheCell.com Alex gave me an hour of his free time over the weekend to give me a crash course in working with Power BI.
Look, I’m Not Perfect
And my report ain’t perfect. I’ll be the first to admit it — Alex candidly told me so! But everyone has a learning journey, and I’m happy to share mine with you. If you’re new to Power BI, hopefully this video got you thinking about all the possibilities. And if you’re a veteran–please, tear mine apart! What did I do wrong? What can I learn? I’m grateful for any feedback you got.
Checkout These Resources!
Alex also pointed me to some awesome Power BI resources. I’ve already made my through the first of these: