Excel AI - data analysis made easy
Excel AI - data analysis made easy
In this step-by-step tutorial, learn how to use Artificial Intelligence or A.I. in Microsoft Excel to easily and to quickly analyze your data.
📚 RESOURCES
- Excel for Beginners Course - Go from Excel Novice to Data Analysis Ninja in just two hours: https://kevinstratvert.thinkific.com/
- Sample Excel workbook to follow along: https://1drv.ms/x/s!AmxrofZZlZ-whYI8i…
- Article that walks through AI in Excel: https://kevinstratvert.com/2023/01/07…
⌚ TIMESTAMPS
0:00 Introduction
1:03 Analyze Data to use AI
1:53 Ask questions about data
3:35 Get insights and see trends
4:52 Refine questions
5:39 Shortcomings
6:32 Automatic insights
7:17 Best practices
7:33 Wrap up
📺 RELATED VIDEOS
- Playlist with all my videos on Excel: • ❎ How to use Excel
📩 NEWSLETTER
- Get the latest high-quality tutorial and tips and tricks videos emailed to your inbox each week: https://kevinstratvert.com/newsletter/
🔽 CONNECT WITH ME
- Official web site: http://www.kevinstratvert.com
- LinkedIn: https://www.linkedin.com/in/kevinstra…
- Discord: https://bit.ly/KevinStratvertDiscord
- Twitter: https://twitter.com/kevstrat
- Facebook: https://www.facebook.com/Kevin-Stratv…
- TikTok: https://www.tiktok.com/@kevinstratvert
- Instagram: https://www.instagram.com/kevinstratv…
🎒 MY COURSES
- Go from Excel novice to data analysis ninja in just 2 hours: https://kevinstratvert.thinkific.com/
🙏 REQUEST VIDEOS
https://forms.gle/BDrTNUoxheEoMLGt5
🔔 SUBSCRIBE ON YOUTUBE
https://www.youtube.com/user/kevlers?…
🙌 SUPPORT THE CHANNEL
- Hit the THANKS button in any video!
- Amazon affiliate link: https://amzn.to/3kCP2yz (Purchasing through this link gives me a small commission to support videos on this channel — the price to you is the same)
#stratvert #excel #ai
Content
0 -> Microsoft Excel is one of the most popular and powerful spreadsheet applications in existence.
7.36 -> That's why over the past few decades, millions of businesses,
11.44 -> organizations, and individuals have relied on Excel to get insights about their data.
17.84 -> Of course, to be able to get insights from your data, the barrier to entry is fairly high.
23.2 -> To use Excel effectively, you need to know about formulas and functions, pivot tables,
28.16 -> conditional formatting, and the list goes on and on. Of course, if you want to learn about those,
34 -> you can check out my course right up above. Microsoft Excel is pushing the boundaries to
40.08 -> make it even easier and quicker to use by introducing artificial intelligence or AI.
46.8 -> In this video, we're going to look at how you can use AI in Excel to get some incredible insights
53.28 -> about your data. And best of all, you only need to know how to click on one button or
59.36 -> how to ask the right question. Let's check this out.
63.12 -> Here I am in Excel and I have confidential sales data for the Kevin Cookie Company.
69.36 -> If you'd like to follow along, I've included the data down below in the description.
75.12 -> To analyze the data here, I could use formulas and functions. I could insert a pivot table.
80.16 -> I could also apply conditional formatting, but all of that requires some knowledge of how to use
85.36 -> Excel. Instead, let's go to the top tabs and make sure the one titled home is selected,
92.16 -> and all the way over on the right-hand side, it's somewhat tucked away. Let's click on the button
97.44 -> that says analyze data, and this opens up the analyze data pane. To expand it, you could click
103.92 -> over here and you could drag it a little bigger. This way you could see it better.
106.96 -> And this pane has so much power as long as you know how to ask the right question.
112.72 -> Let's actually test this out. I'm interested in knowing how many units we sold per day
118 -> across all of these different products. Using formulas and functions would take a little bit
122.16 -> of time, but here I simply need to phrase my question. Here I'll say how many units sold
127.2 -> per day, and I want to see it as a line chart. Here I'll press enter and look at that. Here
133.68 -> Excel automatically generates a chart for me that shows me the sales across all these different
139.36 -> products, and all I had to do was type in this one question. If I want to keep this view, here
145.84 -> I'll simply double click on this chart and that inserts a new pivot table and a chart showing me
151.44 -> the answer to my question. As a pivot table and with a chart here, I can now modify these pivot
157.28 -> table fields to get the view exactly how I want it. And if you want to learn more about how to
162.4 -> use pivot tables, be sure to check out the video right up above. Instead of modifying the pivot
167.36 -> table, I can also just go back to the original analyze data view and I can modify my question.
174.4 -> So let's say that maybe instead of seeing it per day, maybe I want to see it aggregated by month.
179.28 -> Here I'll simply change the word to month and then I'll hit enter. And here I get a chart
184.32 -> automatically generated that now aggregates it down to the month. Now, so far we just see a line
189.2 -> chart, but what if I want to see it as a, let's say a column chart. Here, I'll remove line and
194.4 -> type in column, hit enter, and it automatically generates a column chart showing me the answer
199.92 -> to my question. I could even get it back as a table. So instead of a column chart, here
204.88 -> I'll change this to say table and hit enter, and here I see it by month in a table format.
210.72 -> So this makes it so easy to analyze data as long as I have the right question in mind.
215.36 -> Up to now, we've had to ask all of the questions, but I want to see if maybe Excel could come up
221.52 -> with some insights for me, and specifically, I want to know if during all these months of the
227.04 -> year, are there any outliers or maybe trends in the months? Right here, I'll click on the X to
232.32 -> remove my previous question, and here I'll type in, are there any date trends? And I'll press
237.68 -> enter. It's now analyzed all of my data. And look at this, it looks like unit sold has outliers
244 -> on December 3rd and also December 12th. So here I can see that December tends to be a higher month
249.44 -> than all of the other months of the year, and also there are these two dates that really stand out.
254 -> This really helped me analyze all the data very quickly so I know that December is an extremely
259.04 -> important month for the cookie business. It's good to know that December is an important month,
263.04 -> but let's see if there are any other insights. Here, I'll go back up to the top. Let's remove
267.12 -> this question and let's see if maybe there are any product insights. Here, I'll type in the
272.16 -> question, are there any product insights? And then click on the go icon. And right here,
277.28 -> I see an answer. It says that the product chocolate chip has noticeably higher units
281.84 -> sold compared to all of the other cookie types that we sell here at the Kevin Cookie Company.
286 -> It looks like chocolate chip is definitely the big seller here, so we need to make sure that
290.16 -> we maintain the quality of that recipe. Another thing that I want to know is which customers
294.64 -> should we wine and dine the most? So which ones bring us the most revenue? I'll go back to the
299.36 -> top and here I'll remove that question. Instead of typing in a full question, I could also just type
304.48 -> in revenue by customer and hit enter, and here I see the revenue by all of our different customers.
310.4 -> So it looks like Stop and Snack brings us the most revenue. Now, I actually meant to ask for
315.6 -> the average revenue by customer ID, and here if I look down below, Excel also helps me refine my
321.84 -> question. Here I see average revenue by customer ID. I'll select this, and here I see that view.
327.84 -> So when we look at the average revenue, it actually turns out that Wholesome Foods is
332.64 -> our best customer. So, maybe we should wine and dine with them and see if we can get them to order
336.88 -> a little bit more since the average revenue is so good. Now, before you think you're out of a job
341.92 -> and Excel could analyze just about everything, there are some shortcomings. Here I'll zoom out
347.2 -> just a little bit and let's say that I want to know, well, what was the total profit?
351.44 -> The total profit is all of the revenue minus all of the cost. Right up here, I'll ask another
357.2 -> question and I'll say, what is the total profit, and then I'll hit enter. And here it simply
363.36 -> returns the sum of the revenue. That's the sum of this column. So it doesn't sum up the cost
368.64 -> and then subtract it from the revenue. Let's try one more thing. Over here, I'll type in total
374.32 -> revenue minus total cost, and then I'll press enter. And here it returns me the sum of revenue
381.68 -> and the sum of cost, but now I need to subtract them. So it gets me close, but it doesn't quite
387.44 -> get me all the way there. Maybe in the next iteration, you'll be able to ask questions like
391.76 -> that. A lot of what we've looked at so far requires that you have a good question in mind, but
397.44 -> sometimes you might not know what to ask. Here I'll click on the back button and here we see some
403.28 -> sample questions that you could click on. So maybe one of these questions gives you what you need.
407.52 -> And here, if we scroll down a little bit here, you can see some example answers or insights about
412.48 -> your data. So here I see the revenue by customer ID. Here I see that insight, that unit sold in
418 -> December is higher than all the other months. And here I see that insight about chocolate chip is
423.04 -> higher than all the other cookie types. Down at the very bottom, I could also click on show more,
427.04 -> and here I see many other insights about my data. So, even if I don't have inspiration for a question,
432.48 -> I could just look through these and see maybe if there's an interesting view of my data
436.08 -> that I hadn't thought of. In general, to get the best possible results, it helps to have
441.52 -> a single header up on top. Also, when you phrase your question, right up at the top,
446.72 -> you want to make sure that it's as specific as possible. That way you'll get the results
451.36 -> that match exactly what you're looking for. All right, well, that's how you can start leveraging
456 -> AI in Excel, and I think you'll agree with me that AI makes Excel so much easier to use.
462.64 -> Instead of worrying about how to use the tool, you can instead focus on asking the right questions.
467.92 -> To watch more videos like this one, please consider subscribing, and I'll see you in the next video.
Source: https://www.youtube.com/watch?v=b-cFv2DvEqE