DESCRIPTION: Welcome fellow Entrepreneurs! I’m so happy that you are learning Excel as part of being an Entrepreneur. Learning how to manage data especially in Excel is an important skill for any start-up or business owner.
TARGET AUDIENCE: Pivot Tables are useful for those who deal with data that need to be categorized under several factors. For example, if you want to figure out the total number of lunches, you can just use a simple count function. But if you want to group the distribution of lunches by date, student gender and date you’ll need Pivot Tables. This course is ideal for Data Analysts.
- Basics of inserting a pivot table based on existing information
- Column heading selection
- Grand and SubTotal implementation
- Data Supression, Field List and Field Header Editing
- Banded Rows and Columns
- Data Quick Styling
- How to edit existing data and Pivot Table refresh
- Banded Rows and Columns
- Data Quick Styling
- How to edit existing data and Pivot Table refresh
Section 1: 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 2: 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 3: 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 4: 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 5: 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 6: 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 7: 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 8: 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 9: 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 10: 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 11: 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 12: 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 13: Dashboard Development for Intermediates
DESCRIPTION: By this point in your Excel career, you would have no doubt started performing more advanced calculations. Some of which may repeat day to day with changing variables. However, the formulation and procedure remain the same. So instead of redoing the calculation each time, you can build a dashboard which allows you to simply insert your new data and Excel will automatically calculate the new values. It involves a new of thinking in terms of data layout and data flow. So in this course I’ll show you how to set up a dashboard where each part of your analysis is contained in its own specific section.
Section 14: 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.
Excel Tracking System