DESCRIPTION: Welcome to the expert level course. By now you would have completed both the beginners bundle and the intermediate bundle courses. From here we’re going to push your skills even further! Part of being an expert in Microsoft Excel is about being able to combine various techniques together. These include combining Pivot Tables with filters, or conditional formatting with data visualization techniques, or data validation with dashboard design. In this course I’ll be teaching each one of these elements and you’ll see how knowledge from one function or method can translate to another.
We’ll begin with data validation. This technique helps control the data that goes into a cell and limits what users can place inside. This is great for creating forms that others will use as well as creating dashboards with controls on them. Next we’ll cover charts and graphs and expand more on customization, styling and more importantly all the available chart and graph options available in Excel. Next comes Conditional Formatting where we’ll be expanding on the intermediate level course and learning more features. Next comes Filters and Pivot Tables where we’ll learn how to deal with larger data sets and more complex operations. With Pivot Tables I’ll show you more options and techniques which will give you deeper insight into your data. Next comes text editing which helps with data cleansing. Information can sometimes come to us in dirty versions where data is inconsistent or not laid out correctly. Text editing helps with cleaning up this data. A must for all Data Analysts. Next comes Dashboard Development. This builds on some knowledge learned from other short courses and allows you to create an interface for other people to use. Following on are Lookup functions which every expert should know. Lookups allow us to extract information quickly and from large data sets without having to manually do it ourselves. Next comes keyboard shortcuts which will increase your efficiency and lastly the Text-To-Columns function which allwos us to split data from one cell into multiple cells.
- Data Validation
- Charts and Graphs
- Conditional Formatting
- Pivot Tables
- Text Editting Functions
- Error handling
- Dashboard Development
- LOOKUP Functions: Index, Match, Vlookups and Hlookups
- Keyboard Shortcuts
Section 1: Data Validation for Experts
DESCRIPTION: This course continues on from the intermediate level data validation course so I recommend you take that first if you haven’t done so already. Data validation is a great way to ensure that data entered into your spreadsheet is correct and within the limits you set. You don’t want users typing text where numbers should be, writing dates incorrectly, setting numbers too high or even typing in names wrong. You can limit their answers using data validation very easily and give them information on what information is allowed. In addition, you can also provide helpful hints if the data they entered is incorrect. In this level of the course we’ll cover validation circles which will allow you to visualize and violations in your data. This occurs after data has been entered and the validation is applied afterwards. Next we’ll learn about creating a dynamic list. Earlier we could only create a fixed list. This time we’ll link it to a table that changes depending on user values. Next we’ll combine the table function with data validation which allows you to create tables very quickly with automatic validation applied. Lastly, we’ll cover custom data types which are more advanced rules for data entry.
Section 2: Charts and Graphs for Experts
DESCRIPTION: This course continues on from the intermediate charts and graphs course. In the intermediate course we covered only the basic chart types. At this level we’ll cover all the other types of charts, and there’s a lot of them!! First we’ll use stacked charts which combine multiple series into a single column giving you more insight into your data. Another version of this is the 100% stacked chart which converts your data to a percentage for you. This is especially useful for people who hate calculating percentage! Next we cover stock charts and error bars. Great for scientists, statisticians, mathematicians, researchers, engineers and stock market lovers. These charts allow you to visualize the range in which your data exists. Next is radar plots which look amazing. Think of them as a line chart that’s been bent into itself. These charts are great for data that needs to be mapped radially or in a circle. Surface plots come next and these look like weather maps. Excel determines the values between your data and creates the map automatically for you. Scatter plots are charts we covered earlier but in this course we’ll cover an extension of them which are bubble charts. Bubble charts give you another dimension of vision into your data. Common with scatter plots are trendlines. The great thing about trendlines is that you can forecast data before or after your data range very easily.
Section 3: Conditional Formatting for Experts
DESCRIPTION: Conditional formatting is one of my favourite and most used tools. This course continues on from the intermediate conditional formatting course so be sure to check that out first. Conditional formatting is great for everyone because it gives us a visual representation of data. Humans are designed and have evolved to recognize colours more than reading numbers so we should use that to our advantage. In this expert level course we’ll be covering some more advanced techniques such as combining conditional formats with filters. I’ll also show you how to customize the appearance of the conditional format beyond the pre-set red, yellow and green options. Next I’ll give you some tips on managing multiple conditional formats as some conditions will overlap. You’ll need to understand how Excel chooses which one to apply. Next comes the customization which includes setting the numerical limits for icons sets, highlight blanks or non-blanks, duplicates and uniques and finally detecting errors.
Section 4: Filters for Experts
DESCRIPTION: This continues on from the intermediate level filters course so be sure to check that out before you take this course. Filters are one of the most powerful tools in Excel. It allows us to reorganise data alphabetically or numerically, trim down rows based on a single columns values or do it multiple times using multiple columns, we can use colours to sort, set limits by values and even manually set unique values and handle blanks. That’s just the start. Years of experience has shown me the true power comes when filters are combined with other techniques. In this course we’ll cover some more advanced techniques such as formatting filtered data, advanced filtering which uses another table as the filtering criteria, multiple filters and finally keyboard shortcuts.
Section 5: Pivot Tables for Experts
DESCRIPTION: This is a continuation from the intermediate course on pivot tables so be sure to check that out before you take this course. Pivot tables are a great way to summarize information especially large sets of information. The great thing about them is that they’re dynamic; you can constantly change the layout, you can add new fields, you can update the date source, you can create formulas on the fly and so much more. In this course we’ll learn the more advanced parts of the pivot such as how to repeat item labels which is great for generating new information and tables, custom grouping, calculation fields, show value options, pivot styling and finally chart creation which links your pivots to charts.
Section 6: Text Editing Functions
DESCRIPTION: This course is perfect data analysts. Inconsistent data or more commonly known as dirty or unstructured data, needs to be cleaned if it’s to go into databases correctly. This involves a certain amount of data cleansing. The techniques you’ll learn here are great because they allow you to automate in sense the clean up procedure compared to doing it manually which could take a very long time. We’ll begin by learning the text trimming functions which allow us to extract text or number from part of a cell, following we’ll l learn the Len function which counts the number of characters in a cell which is useful for clean up purposes and data checking. Next we’ll cover the Lower, Upper and Proper procedures which change the capitalization of text. The substitute function comes next which replaces specific content in a cell. This is great for situations where the same type of error appears and needs to be replaced.
Section 7: Error Handling
DESCRIPTION: There are cases where formulas will result in an error not because the formula itself is wrong, but simple because the design of the spreadsheet results in errors. For example, you have a 2 lists of numbers where one needs to be divided by another. In some cases you’ll numbers that divide by zero. Instead of showing an error, you can replace the error with some sort of descriptive text. Other cases include using a lookup function such as Vlookup or index; if the result you’re after isn’t in the list it will result in an error. Instead, you can replace that with text, a number or even a formula. This course is all about handling errors so that you can numerically continue in your analysis. We’ll cover the error types and what each one means, checking for blanks, error tracing, precedents and dependents, handling the #N/A error (there are 3 ways!), the Iferror function which replaces an error with something else and finally the error type check which gives the particular error a code that you can use.
Section 8: Dashboard Development for Experts
DESCRIPTION: This is a continuation from the intermediate Dashboard Development course. We’ll learn even more controls you can use to create your dashboard. This includes the use of Radio buttons and tick boxes. This is a great option for others who use your dashboard because they no longer have to type in information or select from a drop down. Instead, they just select the option they want. Now Radio buttons and Tick boxes need to be contained within a group in order to relate them. You can go further and hide these groups from your users if you wish with a few simple lines of VBA code. Next we’ll cover list boxes which work similarly to Data Validation list boxes except that they are floating. Next we’ll cover sparklines and icon sets which are added visualization you can use to make reading your dashboards even easier.
Section 9: Lookup and Index Functions
DESCRIPTION: This is one of the most powerful techniques you’ll learn Excel. It replaces a lot of manual searching which saves you a lot of time. Lookups work by searching for a value in a table and extracting it for you into another cell. This is useful for when you need just a few values from a cell or you need to distribute a unique value to a list of similar values. The possibilities and applications are endless. We’ll begin by learning the Vlookup and Hlookup functions which stand for vertical and horizontal lookup. Next we’ll cover the match and index function which when used in combination with each other makes for a powerful search function.
Section 10: Keyboard Shortcuts
DESCRIPTION: Keyboard shortcuts can greatly increase your efficiency. Instead of searching for the icons you need with the mouse, you can trim off a few seconds using the keyboard shortcut. It doesn’t sound like much at first, but if you add it up over you Excel life, it’s a lot of time. The shortcuts I’ve compiled here by no means covers all the available shortcuts. However, these are the ones I use the most. I’ve categorized them based on their use which includes the general shortcuts such as cutting, copying, pasting and formatting. Followed by cell control such as table navigation and row/column control. Then we’ll cover window control which includes anything that controls Excel itself such as switching between works, sheets and accessing the quick access toolbar. Next comes data types and formulas which you’ll be using a lot, then a few extras (I had to call this extra cause I had no idea how to categorize them haha) and lastly the F-buttons which are at the top of your keyboard.
Section 11: Text To Column
DESCRIPTION: A very powerful technique to say the least. This function allows you to separate data that has been stored in a single cell, essentially splitting it into multiple parts. For example, you have an address such as “55 Excel Avenue” and it’s been typed into a single cell. When you apply text to column, it will split it into 3 separate cells. It does this by locating a specific number, letter or symbol, in our case a space. It finds the spaces and breaks up the data into as many cells as required. The applications for this is endless and many times I hear professionals seeing their interns separating data manually. In this course I’ll teach you the two types of text to column which are delimited and fixed width, each having their own application. Next I’ll show you a few combinations that I use and finally the concatenate function which recombines data that has been split. This is useful where when only a certain part of your data needs to be cleaned and so you split it up, clean it, and then recombine.