Category: Excel 2013 BI

Sometimes it’s hard to envision the finished product of your labors when you’re in the middle of the construction mess.  And it’s hard not to become frustrated when you can’t instantly learn all the great technical stuff out there.  Well, you’re heard that Rome wasn’t built in a day, and neither will your technical studies.  Building a solid technical foundation takes time and a ‘no quit’ attitude.  If there was another way to do it, I would have found it by now!  So let’s continue our journey as we explore Excel, an essential part of a solid foundation.

Excel 2013 Focus with Lynda.Com

I hope you have completed some of the courses we discussed in Lynda.Com In-depth: Part 1; if you have not read the post, click here.  For those of you who have completed the courses suggested in Part 1, you’re now ready for focused courses on Excel 2013.  But why Excel, when we’ve been studying SQL Server?  Because Excel is the preferred ‘go to’ for business analysis by the vast majority of companies.  Managers and owners still like the ‘slice and dice’ of Excel, so you must know Excel as well.  It will also set a solid foundation when you move into Power Pivot!

Building a Solid Foundation

Lynda.Com has a plethora of Excel courses, so we need to limit the scope.  Today’s post focuses on five courses; they teach essential and advanced concepts, so let’s take them in the best order for learning.  The great place to build a strong foundation is at the bottom, so Excel 2013 Essential Training is where you should begin.  Even if you have a good understanding of Excel, this course will serve as a refresher while introducing you to additional features found in the 2013 edition.

Excel 2013 Essential Training is 6 ½ hours long; most of the ‘Essential’ course topics taught on Lynda.Com are lengthy but worth the time.  After completing the Excel 2013 Essentials you will be able to enter and organize data and build workbooks.  You will be introduced to basic functions, formatting, creating simple formulas, sorting data and more; in addition to an introduction to pivot tables.  Now keep in mind, a pivot table is not the same as a Power Pivot table; the business intelligence aspect of Excel 2013 will be discussed in the third part of Lynda.Com.

Next Level Up

Once you’re waded your way through the Essentials course, you can move on to Excel 2013: Tips and Tricks course.  This course is 4 ½ hours and shows you how to increase your productivity with ‘power user’ tips; teaching you how to create formulas quickly, split data into columns, apply formatting, build charts, and create split screens quickly.  There is also a chapter on ‘Top Ten Shortcuts’ and ‘Ten Tiny Tips’, if you don’t have time for the full course.  But before you move on, watch the section on Database Techniques with ‘Flash Fill’ feature.  Now you can move on to Excel 2013: Power Shortcuts. 

Excel 2013 Power Shortcuts is a mere 3 hour long course showing you over 65 tips, tricks, and shortcuts.  Some of the material mirrors the previous course, so compare and contrast to see which one you’d like to watch.  The Power Shortcuts topics cover creating hyperlinks, setting print areas, removing duplicates, analyzing a formula for errors, and summarizing data with charts and tables.

Higher Level

The last two courses deal with more complex subjects: Data Validation and Time/Data; both are considered Intermediate level courses.   Excel 2013: Data Validation in Depth, only one hour in length, shows how to control data input into workbooks by controlling dates, length of text, types of data allowed in a workbook, using an error alert tab, and more.  The focus of this course is to ensure your data is accurate and valid.

Our last course for review is Excel 2013: Working with Data and Time.  This one hour course explains how Excel stores and uses dates and times and how Excel stores dates and time.  You’ll learn useful date/time functions to customize your dates, format dates over a 24 hour period, and calculate differences across dates and times.  A solid understanding of dates and times will greatly enhance your skills when you work inside a Power Pivot model as well.

A Few Final Thoughts

Next time, we’ll conclude the Lynda.Com In-depth with a look at the Power BI courses for Excel, with emphasis on Power Pivot, tables, and charts.  This is where the fun really begins!  You will not want to miss it.

I’m reminded of a saying my sailing captain would often say:  “When you get to the dock, you’ve already arrived at your destination.”  Captain Kevin wanted to convey that the trip was not about getting to a location, but enjoying the journey along the way.  So thank you for taking this technical journey with me as we continue becoming ‘technical’ together.  Until next time!

Susan Schneider lives in Jacksonville, Florida with her wonderful husband Steve. She enjoys sailing and is a ‘wanna be fisherman’, and loves all things BI.  See more information under the ‘About Me’ section.  Remember to sign up for new blog notifications:  Go to Subscribe2 on the sidebar and sign up!

Deep Dive: Pragmatic Works Training

I’ve blogged about the great training at Pragmatic Works before, but today, we deep-dive into the various training available through Pragmatic Works; let the lovefest begin with Training on the T’s!

Before my journey on the technical side, I was a teacher; so it’s quite easy for me to recognize great teaching versus good teaching. So, if you have a limited number of hours to dedicate to training, but you want to get the best possible learning experience; then you need to watch Devin Knight’s training sessions. It doesn’t matter what Devin is teaching on, the subject is going to be excellent! Devin is a wonderful communicator and teacher, and he presents the subject matter in a logical progression; making it easy to learn.

Another favorite presenter is Brian Knight.   I remember sitting in a seminar at a SQL Saturday, many years ago, listening to Brian talk about Data Mining.  Brian’s knowledge of the subject, along with his boundless enthusiasm, made you want to learn Data Mining. He says data is sexy, and he’s correct!

I’ll also introduce you to a few more of my favorite webinar presenters: Kathi Kellenberger, Bradly Schacht, Mike Davis, and Angel Abundez. There are other great presentors like Adam Jorgensen, Shawn Harrison, and Jason Strate; but there’s not enough time in the blog post to cover everything!

Building a Strong Foundation

For the next few minutes, I want to list specific Training on the T’s webinar titles and presenters that will help you build a strong foundation in your technical knowledge. I’ve selected the following order of webinars to save you time and help you lay a solid foundation for your learning. So let’s get started!

First off, watch Devin Knight’s webinars, with my comments listed for a few of them:
-Understanding Microsoft Self-Service Business Intelligence
-Introduction to Power BI (**great overview of Power BI Excel 2013)
-Introduction to Power Pivot (**the cornerstone to the Power BI tool suite in Excel 2013)
-Introduction to Common DAX Expressions (**the language that make Power Pivot rock)
-Creating Real World Power Pivot Models (**you’re only as good a your database)
-Introduction to Power Query (**talk about an easy way to ETL: ‘extract, transform, and load’)
-Creating an End to End Power View Reporting Solution (**deliver the ‘wow’ in report presentation)
-Touring your Data with Power Map
-Choosing a Microsoft Reporting Technology

Now, there are many more topics that Devin has available, but I’m giving you a logical course of training to give you the building blocks you need for a solid foundation in the MS BI Stack. After you view these webinars, let’s move on to a few webinars from Brian Knight.

One of the best ‘soup to nuts’ overview on planning a Data Warehouse was recently presented by Brian (with PowerPoint by Devin). The webinar presents a clear cut look at the planning stages of a DW. With that said, it’s also beneficial for beginners as well, as you learn the terminology and concepts; you’ll not want to miss this one.

Brian Knight:
-Tips and Tricks for Planning a Data Warehouse (**great presentation ‘soup to nuts’ overview)
-Quick Start to Power Pivot
-How to use Power Query as your Self-Service BI ETL tool

Topics for a Future Day

When you have some more training under your belt, you’ll want to move on to webinars by Mike Davis, Kathi Kellenberger, Bradley Schacht, and Angel Abundez. When you feel comfortable with T-SQL, learn about Window Functions; Kathi gives two excellent webinars on this subject. Mike Davis gives the best presentation on how to prepare for a technical interview. His presentation on the subject at the 2014 SQL Saturday was excellent, but his webinar ‘Mastering the Technology Interview’ is even better! These folks are just a few of my favorites, but all the PW webinars are excellent. When you get a chance, check out some webinars by Adam Jorgensen, Shawn Harrison, and Jason Strate as well.

Kathi Kellenberger: T-SQL Window Functions
-T-SQL Window Function Performance
-Write Better Queries with Window Functions

Mike Davis
-Mastering the Technology Interview
-Complex DAX Expressions Power Pivot

Bradley Schacht
-Introduction to ETL Using SSIs
-SSIS ETL for Beginners
-Back to Basics: SSIS 2012 for Beginners

Angel Abundez
-Power BI Tips for Data Analyst
-Chart Anatomy 101

Virtual Training

Sometimes you just need to spend some money on training. So when you’re ready to make that move, PW offers Virtual Training courses in six areas: Analysis, Big Data, Business Analytics, Data Integration, Data Visualization, and Database Administration. The cost is $995.00 for 4 days (3 hours each day) of in-depth virtual training; the class is limited to around 24 participants. You’ll usually have labs to complete after each day’s training. For more information click on the Data Sheet (a one page outline of each day’s topics), Details (specifics for each day’s training), and Date/Time(when the class will be offered during the year). I’ve taken the SSRS course and I’m scheduled to take the Tabular and Power Pivot for Developers in October. I can hardly wait!

Workshops and Bootcamps

PW offers workshops around the country; they are in-person two day training courses on a variety of SQL Server topics. Then you have Bootcamp, a week-long intensive learning on either Business Intelligence or Performance Tuning.

Closing Thoughts

Pragmatic Works has lots of good stuff under their Resource section; things like Articles and Whitepapers, Cheat Sheets, and more. If you are limited on time, but want consistent, excellent training; spend your time on the Pragmatic Works website. Well, we’ve definitely had a Pragmatic Works lovefest today. Next time we’ll take a deep dive into Lynda.Com and the great technical training available there. Until then!

Susan Schneider lives in Jacksonville, Florida with her wonderful husband Steve. She enjoys sailing and is a ‘wanna be fisherman’, and loves all things BI. See more information under the ‘About Me’ section. Remember to sign up for new blog notifications: Go to Subscribe2 on the sidebar and sign up!