DESCRIPTION: Welcome to the intermediate bundle! In this bundle we have compiled all the intermediate courses you’ll need to continue from the beginner level training and all the way to expert level. By this point you would be familiar with how cells work, how formulas connect to each other, basic knowledge of creating charts as well as numerous other functions such as hyperlinks, sheets and wordart.
In the intermediate level course we’ll be expanding into new territory. We begin by enhancing your knowledge of formulas by learning about nested functions. These are formulas within formulas which will allow you to perform more complex calculations in less space thus simplifying your spreadsheets. We’ll then be covering data validation which limits the data type in cells, object grouping and ungrouping which will simiplify your use of objects, review functions to correct word usage, shortcut ribbon for productivity, pivot table slicers and timelines for basic dashboards, shapes, charts, conditional formatting which changes formatting in a cell depending on the contents in a cell, workbook presentation to make sure you’re presenting clear data to your audience, logic functions, sparklines, images, name manager for calculations, workbook protection, formula auditing for cases where you have heaps of formulas on screen, smartart graphics, filters, pivot tables and finally tables……phewwww….!!!!!
TARGET AUDIENCE: This is for intermediate users who have completed the beginners bundle course.
- Formulas – Nested Functions
- Data Validation
- Grouping and Ungrouping
- Review Functions (Spelling, Research, Thesaurus, Comments)
- Shortcut Ribbon
- Pivot Table Slicers and Timelines
- Charts and Graphs
- Conditional Formatting
- Workbook Presentation
- IF, AND, OR Functions and Logic Theory
- Using the Name Manager
- Workbook Protection and Change Tracking
- Formula Auditing
- Pivot Tables
- Using Tables
Section 1: Formulas - Nested Functions
DESCRIPTION: As your skill develops with Microsoft Excel, so too will the complexity of formulations. Depending on the arrangement of data you may choose to have each individual part of the final calculation stored on your workbook or you could combine several formulas into a single cell. Each has its own advantages and disadvantages. Having each calculation stored results in larger spreadsheets potentially spread across different sheets making tracking difficult. Conversely, combining all formulas into a single cell greatly reduces the workbook space but makes understanding individual elements difficult. The solution is to have a balance of each but also be flexible depending on the data itself. In this course, you’ll learn how to create nested functions as well as how to expand it further. Most importantly you’ll learn how to validate your nested functions to make sure it’s constructed correctly.
Section 2: Data Validation for Intermediates
DESCRIPTION: When documents need to be sent out, shared or filled out, data validation is a must. Unlike programs such as Microsoft Access, you do not set the allowable data types in each cell before you use them. Meaning, if not controlled, a user can place a text where there should be a number. In the case where formulas are connected to this particular cell, will result in your formulas all having errors. Other cases include where a specific type needs to be selected. By creating a list to choose from you can remove any variations to the same name when typed in manually i.e. St. Albans, Saint Albans. St. Alban, Saint Albbans, Snt. Albns. Instead, having a dropdown keeps all selections consistency and makes data easier to manage. In this course you’ll learn how to apply the data validation rules. We’ll cover whole and decimal limits, list types (manual and dynamic methods), date and time limits, text length limits, input message setup and error alert setup.
Section 3: Grouping and Ungrouping
DESCRIPTION: The grouping function is a great way to minimize your spreadsheet without losing or having to delete important data. It allows you to temporarily hide rows or columns of data and provides a little switch to open it up again. It is very similar to hiding rows or columns except for the fact that a little open/close switch is provided so that you can clearly see that there is hidden data unlike the standard method of hiding. In this course you’ll learn how to apply grouping in both single and multiple levels, essentially creating hidden groups within other hidden groups. You’ll also learn one of my favourite techniques which is to use freeze panes with grouping on top allowing you more space to work in.
Section 4: Review Functions
DESCRIPTION: Mistakes happen to all of us. We could do a manual search and look for these mistakes but for large documents this will simply take forever. Instead we can use the spelling function which will automatically scan your document for mistakes and give you options on what to do next. You can accept the corrected word provided, ignore or simply change it yourself. Next you’ll learn the research function. Think of this as an in-built google search in Excel which connects to the internet. So if you’re not sure about a certain phrase let Excel look it up for you. Now if you do have a word but it’s not exactly the word you’re thinking of, Excel has a thesaurus that will show you similar words that you can choose from. Lastly, one of my personal favourites, comments. What I love about comments is that they don’t occupy cells. You can create little notes that are embedded into a cell. When you hover over the cell, the comment will appear showing more information.
Section 5: Shortcut Ribbon
DESCRIPTION: I LOVE THE SHORTCUT RIBBON! When spreadsheets get larger, you’ll need to see as much of the workbook as possible to maximize your productivity especially when working on small laptops. One method is to minimize the main menu tab and work directly off the shortcut ribbon. The ribbon itself is a custom collection of the functions and icons you use the most and takes a fraction of the visible computer screen space that the main menu tab does. The best part is, you can change which icons are there depending on the work you’re carrying out. In this course you’ll l earn the two methods of adding and removing icons, how to rearrange icons and finally how to access icons directly from the keyboard to increase your productivity and efficiency further.
Section 6: Shapes for Intermediates
DESCRIPTION: Shapes are powerful tools in Excel simply because they don’t occupy cells. Instead, they overlap cells giving you another dimension of data presentation which is pleasing to the eye if done correctly. There are many customization you can apply to a single shape including rotation angle, bevels for 3D effects, shadows and reflections, textures and fills, alignments and more. In addition to the appearance control of the objects themselves is essential so you’re not wasting time trying to arrange them just right. Excel has built in functions that will allow you to manage all objects known as the selection pane. Alignments are performed by using the borders of each object. You’ll also learn how to layer objects and lastly how to use the grouping function so you can manage multiple objects as one.
Section 7: Charts and Graphs for Intermediates
DESCRIPTION: Effective use of charts is essential in Microsoft Excel. No experienced user is complete without them. Charts allow us to visual data especially large samples of data and is an effective way of identifying patterns, trends, peaks, valleys and clusters in your data that may otherwise go unnoticed if viewed in table form. Excel has a number of charts to choose from and when further customized gives you an infinite number of way of presenting the same data. In this course we delve deeper into charts through the use of pie, column, bar and scatter plots. We’ll explore some added options such as the use of data labels which can be applied to individual points of data, trendlines to automatically create lines of best fit, multiple series to compare data, dual charting and lastly the use of a secondary axis for multiple series data at different magnitudes.
Section 8: Conditional Formatting for Intermediates
DESCRIPTION: Conditional formatting is a powerful tool. This is because it allows you to visualize data using colours and symbols. Our brains are hard-wired to recognize colours and symbols far better than numbers, we’ve evolved that way. Therefore we should use that to our advantage to make reading our data even easier. Conditional formatting allows us to highlight values based on the value inside the cell. For a given range of data, you can highlight all the number 7’s that appear, or you can rank the top 10 highest values by colour. You can even create miniature bars inside the cells that when compared to each other, will show which values are highest and that’s all just the start. In this course you’ll learn how to prepare your data for a conditional format, I’ll show the various ways to highlight values by parameters, we’ll go through rankings, colour scales, icons, colour and font customization, resetting and management.
Section 9: Workbook Presentation
DESCRIPTION: Technical ability is great for Microsoft Excel. Creating pivot tables, formulations, charts and graphs is great but if you cannot present it to your audience in such a way that they understand it quickly, then you lose your impact on others. Effective presentation of data is an art form but to get started doesn’t take much. In this course I’ll teach you how to elevate your data presentation and layout so that it is easier to read, easier to absorb, simpler to communicate and most importantly aesthetically minimalistic. I’ve created 4 examples of the same data set and we’ll be running through each one and considering the font selection, page layout, border types, colour usage and more.
Section 10: IF, AND, OR Functions and Logic Theory
DESCRIPTION: No advanced user would be complete without IF functions. IF functions allow us to program Excel in such a way that it responds by itself based on the result in a cell. By setting criteria which can either be true or false, we can create a specific value or function response. This has a huge range of benefits to us because we avoid having manual set a result ourselves in order to continue the analysis or process. As many users would be new to IF functions we’re going to take it very slow. In fact, we’ve built an IF Function Generator which will build the formula for you so you’re not fumbling around trying to fix errors. Once your familiar with the construction of the formula, you can create it yourself from scratch. We’ll go through several examples, we’ll cover combinations with the AND/OR functions and finally Nested If Functions.
Section 11: Sparklines
DESCRIPTION: Charts and graphs are great and are one of the elements that make using Excel so amazing. However, there are some cases where I don’t need all the functionality that a chart can offer. Sometimes, all I want is a very simple visual on whether the data shows growth, decline or stagnation. Sparklines are the solution. The amazing thing is that they don’t float over the cells like charts do. Instead, they’re in the cell themselves making them look more a part of the data than charts. In this course, I’ll teach you how to prepare your data for Sparklines, we’ll do graphics and artistic effects like line and marker colours, we’ll look at grouping as well as all three sparkline types; lines, columns and win/loss. If you have a lot of data, and don’t want to create heaps of charts, sparklines are for you.
Section 12: Images for Intermediates
DESCRIPTION: I’ve met many who hate handling images (myself included). When you get lots of images, shapes or objects, handling all of them at once or in groups can be very frustrating if you’re not familiar with the available image handling tools in Excel. This course continues on from the basics in the image beginners course which I suggest you take if you haven’t done so already. In this course I’ll teach more of the image editing functions available in Excel such as adjusting brightness, contrast, saturation, tone, colour as well as image compression for large, high-resolution images. In addition we’ll cover in detail the picture effect options such as shadowing, reflection, glow, soft edges, bevelling and 3D rotations. We’ll finish by using the image alignment and grouping functions.
Section 13: Using the Name Manager
DESCRIPTION: Excel is all about simplification. As you advance, you’ll learn that you can squeeze in more and more complex equations in a single cell. To assist with simplification of values, you can store values or even entire table values into variables you create. From there, you can call upon them from anywhere on your spreadsheet. In addition, because these values are stored, changing them in the name manager will automatically update all results. In this course I’ll teach you how to store variables, store tables, change existing data sources and finally how to utilize stored values in a formula.
Section 14: Workbook Protection and Change Tracking
DESCRIPTION: Protecting changes to a published file is critical. Excel by nature allows anyone and everyone to input data. Unfortunately, not everyone knows how to maintain data integrity. Cells that aren’t meant to be filled out get altered, columns or rows are accidentally deleted, important text is changed without permission, as well as many other cases. By protecting and limiting the options of your users or those whom you will share your workbook with, you are guaranteeing that any analysis, calculations, or text is protecting from unwanted changes. This course will show you the difference between workbook and worksheet protection, cell locking, file sharing, change tracking and finally how to approve/reject any changes or modifications.
Section 15: Formula Auditing
DESCRIPTION: As your skill increases in Excel, so too does the complexity of your spreadsheets. Equations start becoming longer and contain more and more elements. Sometimes, several equations can make up the final solution. The problem comes when there is an error somewhere in the relationships. The difficulty then becomes trying to find a small error in a sea of data. Lucky for us Excel has some very neat formula handling functions. These include formulas that show all existing formulas, tracing precedents and dependents to see visually see the connections, error tracing which finds all relationships in a formulas and finds the root problem equation and lastly the formula evaluator which allows you to track step by step each calculation that takes place in a single cell. All of these, you’ll learn in this course!
Section 16: SmartArt for Intermediates
DESCRIPTION: SmartArt is relatively new for Excel. It’s a graphical tool that allows you to create beautiful diagrams with text, flow charts, life cycle charts, process maps, hierarchies and more. This course continues on from the beginner SmartArt course. In this course we’ll be focusing a lot on shape and text formatting. We’ll cover the pre-set formatting options as well as further customization such as reflections, glows, bevels, shadows, edges and rotation.
Section 17: Pivot Tables for Intermediates
DESCRIPTION: When you start using Pivot Tables, it means you’re on your way to becoming an expert at Excel. Pivot Tables have the amazing ability to compress and consolidate huge amounts of data and create a summary of results for you giving you more insight into the data set. Beyond that, you can even use it to clean up data, redesign spreadsheets and more! The possibilities are endless! The way Pivot Tables work is that it analyses your data and looks for any commonalities in a particular row/column. It then reorganises them for you in the way you want to see it. Which means once it’s built, you can change it as many times as you like. As this is approaching more advanced Excel techniques, we’ll be taking it nice and slow. You’ll learn firstly how to prepare your data so that you get no errors during the Pivot creation. Then we’ll go through the 4 groups; rows, values, columns and filters which allow you build the Pivot. Then we’ll cover Grand and Sub Totals and the many options you can choose from. Followed by various Pivot views, data labelling options and most importantly how to change or refresh the data source used to create your Pivot.
Section 18: Pivot Table Slicers and Timelines
DESCRIPTION: To take this course you should be able to create a Pivot Table and understand what it does. If not, be sure to check out the Introduction to Pivot Tables Course. Beyond building a basic Pivot Table, there are additional features you can apply called slicers and timelines. Essentially, these are control that allow you to filter your data based on data, value, range, etc. The great part about it is that they are very visual and perfect for dashboards. This is for intermediate users who have taken the Pivot Tables for Intermediates.
Section 19: Filters for Intermediates
DESCRIPTION: I use filters a hell of a lot. For large data sets with lots of rows and columns, they make sorting, rearranging and organising data a breeze. When filters are applied to a table, little controls appear above on the column headings which give you a range of options to choose from such as sorting alphabetically, numerically, by rank, by top percentage, by value, by limit and more. In this course you’ll learn all of that and more including my favourite, Master ID Theory! An absolute must for data analysts.
Section 20: Using Tables
The Tables function in Excel is very under-utilized. Standard tables are usually constructed by creating a formula and then copying that formula all the way down to all the rows. Problem with this is that if a single formula were accidentally altered, or incorrectly created in the first place, you wouldn’t notice it. Whereas using the tables function ensures that each formula is identical as the referencing method is slightly different. You’re no longer referencing individual cells, but columns instead. Rows will connected to respective rows and will maintain control and integrity of your formulation. In this course we’ll go through the data preparation, how formulas are designed and how they differ from ordinary formulation, slicers, duplicate removal and table styling.