Loading...

Follow Excel TV Blog on Feedspot

Continue with Google
Continue with Facebook
or

Valid

Using Power Query to Connect Tables for Reporting - Excel.TV | Episode 55 - YouTube
An Excel Joke to Start Off...

Did you hear about the database developer that walked into a bar? They saw two tables and tried to join them. 

And that's what we're doing this week! Last week, as you might recall, I posted an overly complicated solution to this problem based on formulas.  

This week, I've simplified it all using a left outer join. This is a really cool feature that lets you treat Excel like a database. You can a very similar use of joins in Szilvia's video, by clicking here.

Watch the entire video to see how I join two different tables using name as the primary key! 

Get the Download File

Get the file by clicking this link.

What do you think? How do you connect tables?

Let us know in the comments.

The post Using Power Query to Connect Tables for Reporting | Excel.TV, Episode 55 appeared first on Excel TV.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Advanced Excel Tutorial: How To Increase VLOOKUP() Speed up Excel | Excel TV, Episode 54 - YouTube
CORRECTION

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."

I'd still invite you to watch the video here. Don't judge me too harshly. 

Thanks again,
Jordan

Making Excel Faster

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.

Get the download file!

Want to try it yourself? Click here to get the download file!

Leave a comment!

What techniques do you have that could speed up Excel? Let us know in the comments. 

The post Advanced Excel Tutorial – VLOOKUP() Speed in Excel | Excel TV, Episode 54 appeared first on Excel TV.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

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: 

TEXTJOIN( delimiter, ignore_empty, text1, [ text2, ... text_n ] )

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. 

Let us know what you think in the comments!

Did you like this video? What type of data are you joining? Let us know what you think in the comments. 

The post Excel Tutorial Concat Function in Excel with TEXTJOIN – Excel.TV Episode: 53 appeared first on Excel TV.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
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! 

Get the Download File

Click the button below to get the download file!

Leave us Your Comments!

What do you think about using Power Query for bar trivia? Let us know what you think in the comments!

The post 52: How to Create Running Totals & Rankings in Microsoft Excel Power Query appeared first on Excel TV.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
Excel TV Blog by Jordan Goldmeier - 1M ago
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:

  • YouTube Channel - "Excel on fire" - Link
  • Website - Link
Want to see more videos like this?

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.

Let us know what you think in the comments!

The post Oz du Soleil…Supercut! appeared first on Excel TV.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 
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:

  1. Start with your data
  2. Place the data onto the a 2d clustered column chart
  3. Align the series to be on top of one another
  4. Change the chart type of your target series to a line chart
  5. 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. 

Download File

Click the button below to get the download file!

Leave a comment!

Could you see yourself using this type of chart? What other types of data could it measure? Let us know what you think in the comments! 

The post Budgets vs Actuals Target Chart in Microsoft Excel – Excel TV: Episode 50 appeared first on Excel TV.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

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.

In no particular order: 

(While not an MVP, I'd also be remiss if I didn't mention John Michaloudis - myexcelonline.com - for his help as well!)

Seeing everyone in the flesh

Even as this is my sixth year, it never really gets old meeting my heroes in the flesh. Here are some pics of the event. 

Name that MVP!

Me, Mynda, Oz, Chirs (TheSpreadsheetGuru), Jon 

Me, Leila, Mathieu (RubberDuck VBA), Oz

The venerable Bill "Mr. Excel" Jellen, Me

The big party on the last night!

Me happily holding a therapy bunny that was (surprisingly!) part of the party on the last night. There was also a great, chill dog to pet. 

What's coming up next?

While at the MVP Summit, I had a chance to interview both Leila and Jon outside on the Microsoft Excel campus. The videos are currently with Jon's editor. Until then, here's a sneak preview:

Leave a comment!

Want to know what a specific MVP is like in real life? Ask me in the comments. I promise to give you the unflattering, mostly-true version of them!

The post Dispatches from the Microsoft MVP Summit appeared first on Excel TV.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Four Simple Tips to Land A Job in Analytics and Data Science (and Make More $!) - Episode 49 - YouTube
Introduction

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!

The Problem

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:

  1. Being bold and confident 
  2. Creating an awesome resume
  3. Acing the Interview 
  4. 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: 

Extra ResourcesLeave a Comment (especially if you have your own job tips)

What job tips do you have? Let's share our knowledge that everyone can be successful!

The post 49: How to land a data-driven role (even with a creative or nontraditional background) appeared first on Excel TV.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

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
  • Slicers
  • Timelines
  • Bar Charts

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: 

Have you used Power BI? What tips would you offer newbies? Leave a comment and let us know. 

What’s your experience been with Power BI? Do you love it? Hate it? 

Any interesting tips to help newbies get their feet wet? Share your thoughts in the comments section. We might even feature it on a later episode! 

Try it for yourself

Click the button to get the download files. 

Get the download files!
More Delicious Excel Content

Sign up to our email list to get more content around Exceling your skills, career, and life. 

The post 48: Dashboard Demo: Building your first Power BI Report appeared first on Excel TV.

Read Full Article
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Stop by here at 8:00pm Eastern on Wednesday January 23rd for a LIVE episode with Alex Powers of ItsNotAboutTheCell.com

Be sure to hit the refresh button on your browser if the show doesn't automatically start for you.

Oh yeah... also leave some comments for us below

The post 47: Alex Powers of ItsNotAboutTheCell.com appeared first on Excel TV.

Read Full Article

Read for later

Articles marked as Favorite are saved for later viewing.
close
  • Show original
  • .
  • Share
  • .
  • Favorite
  • .
  • Email
  • .
  • Add Tags 

Separate tags by commas
To access this feature, please upgrade your account.
Start your free month
Free Preview