Course Close

Quick reference

• 00:04 A huge congratulations everybody for making it all the way
• 00:07 through to the end of this Excel for business analysts course.
• 00:11 I hope you enjoyed working through this course as much
• 00:14 as I've enjoyed hosting it for you.
• 00:16 And if you think back throughout all of the different sections of this course,
• 00:20 we've covered so many different things.
• 00:22 If you remember all the way back in Section 1,
• 00:25 we discussed the role that Excel plays when it comes to business analysis.
• 00:30 And we also saw some of those really important Excel efficiency tips and
• 00:34 keyboard shortcuts to help us move around our spreadsheet a lot quicker.
• 00:39 In the second section, we really took a look at some of those golden rules when it
• 00:43 comes to designing our spreadsheets.
• 00:45 We talked about naming conventions, version control,
• 00:49 how to give instruction using summary sheets, versions,
• 00:53 how we can use cell color and cell styles to control where our inputs are going.
• 00:58 And we also saw some techniques that we can use in order to minimize the amount
• 01:02 of errors going into our spreadsheets, so things like protecting the cells and
• 01:07 data validation.
• 01:08 In Section 3,
• 01:09 we moved on to taking a look at some of the more advanced uses of lookups.
• 01:14 We did a recap of how to use VLOOKUP, and
• 01:16 I showed you how you can use VLOOKUP when you have duplicate LOOKUP values.
• 01:21 We also saw how we can perform more complex and
• 01:24 flexible lookups using index and match and data validation lists.
• 01:28 And we also had a look at how we can do two-way matches using
• 01:33 the newer XLOOKUP function.
• 01:35 In the next section, it was all about Excel lists.
• 01:38 I showed you numerous different ways that you can compare different lists together.
• 01:43 I showed you how you can highlight entries in a list using data validation and
• 01:47 conditional formatting.
• 01:48 We saw how to create multiple dependent lists and
• 01:52 how to do things like create dynamic checklists.
• 01:55 We finished out that section by showcasing one of Excel's best features,
• 02:00 in my humble opinion, and that is the superhero that is Flash Fill,
• 02:04 such a time saver when you're working in Excel.
• 02:08 The next section was all about importing data and preparing it for analysis.
• 02:13 I showed you a couple of different techniques when it comes to importing data
• 02:16 from the web.
• 02:17 We saw how we can clean up our data, so
• 02:20 joining things together from different cells using CONCAT and TEXTJOIN.
• 02:25 We saw how we can do the reverse of that and split up data across cells.
• 02:29 And I showed you some of my favorite data cleaning techniques.
• 02:33 We finished out this section by showcasing a couple of the brand new text and
• 02:38 array manipulation functions that have just been released in Excel.
• 02:42 So VSTACK, HSTACK, TEXTBEFORE, TEXTAFTER and TEXTSPLIT.
• 02:47 The next section was where we started to build out PivotTables.
• 02:51 We did a quick recap of how to build a basic PivotTable before moving on to
• 02:55 showcasing some of the more advanced features and
• 02:58 functionality that you may not have already known about.
• 03:01 And then finally, we took a look at how we can utilize Power Pivot and
• 03:05 Power Query to combine data that's contained in multiple different sources
• 03:10 into one and create ourselves one big old PivotTable.
• 03:14 The next section was all about visualization, using charts and diagrams.
• 03:18 We went through some tips when it comes to selecting the correct chart, and
• 03:22 then I showed you some of the trickier chats, so the map chart, for
• 03:26 example, which can be created directly from PivotTable data.
• 03:29 I showed you how to create a histogram chart to show distribution and
• 03:33 we saw some advanced chart formatting techniques to really take your
• 03:38 charts from looking kind of dull to super fancy.
• 03:41 We had a look at how we can add diagrams using SmartArt and also how we can
• 03:46 create in-cell charts using things like data bars and sparklines.
• 03:51 The next section, we were all about dashboards.
• 03:54 And we spent the entire section working our way towards building
• 03:58 a fully interactive dashboard.
• 04:01 So we explored what exactly a dashboard is and why they're useful.
• 04:05 We saw how we can build multiple PivotTables and
• 04:07 PivotCharts in the most efficient way possible.
• 04:10 How we can prepare a calculation sheet, and
• 04:13 I offered you some tips when it comes to inspiration and also dashboard design.
• 04:17 In the final lesson, we brought it all together.
• 04:20 We added some slicers and we ended up with a really attractive looking dashboard.
• 04:25 And hopefully, it's given you some inspiration when it comes to creating your
• 04:28 own dashboards using your own data.
• 04:30 The next section, we were all about predicting future values and
• 04:34 seen trends using forecasting.
• 04:36 I introduced you to some of the forecast functions that we can use depending on if
• 04:40 you're doing a linear or a seasonal forecast.
• 04:43 We spoke a bit about confidence levels and adding an upper confidence and
• 04:47 a lower confidence bound.
• 04:49 And I also showed you how you can do a very quick forecast using forecast sheets.
• 04:54 And in the final section, we took a look at Excel's What-If Analysis tools.
• 04:58 I showed you how you can use Goal Seek to start with a value and
• 05:03 work backwards to find what needs to change in order to achieve that value.
• 05:08 We looked at Scenario Manager,
• 05:09 where you can view your data based on different conditions, different scenarios.
• 05:14 And we also saw how we can utilize data tables in order to see what
• 05:18 our result would be based on one or two variables.
• 05:22 So when you think back through everything that we've covered,
• 05:26 it is such a lot of information.
• 05:27 So the only thing left for you to do now is simply practice, practice, practice.
• 05:32 Remember, you can always go back and rewatch any of these videos, and
• 05:36 you have all of the course files and exercise files to practice with.
• 05:40 So all that's left is for me to sign off now and say my goodbyes.
• 05:44 I had a complete blast putting this course together for you, and
• 05:48 I hope you really enjoyed it and found it useful.
• 05:51 Once again, my name's Deborah Ashby, and I hope to see you again on another course.

Lesson notes are only available for subscribers.