The goal of this course is very simple: SPEND LESS TIME IN EXCEL. In order to do this you need to know the Excel Formulas, how to use charts in excel, setting up pivot tables, and of course Excel Macros (written using VBA).
In this tutorial I use Excel 2010 to go over the formulas, charts, pivot tables, formula combinations, VBA code, and tips & tricks I used to master Excel and get better data and better results while not wasting any of my time manually doing stuff in Excel. I got in, I got it done fast, I got it done right, and I delivered results fast using these techniques. Think about how valuable that made me.
The Excel Formulas I show you in this tutorial are the same ones I used to save my a** from getting fired from a job. I then used those same skills, as well as getting better with VBA to automate a business analyst job I had at a Fortune 500 company.
The stuff in this Excel tutorial is powerful, it WILL make you valuable, but most importantly you’ll be able to SPEND LESS TIME IN EXCEL while delivering better data and better results.
This lesson goes over some of the important features of Excel that I use on a day to day basis. The features include how to "Merge & Center" data in cells, Wrap Text, Apply filters, use Advanced Filters, and use the "text to columns" feature.
In this lesson we'll go over Data Connections. Data connections allow you to connect to a multitude of different applications to pull in data directly to an Excel workbook. You can pull data from the web, SQL databases, and Access databases. In this lesson we'll specifically go over how to create a data connection to a table on a website.
Charts are one of the most important parts of Microsoft Excel. Charts allow you to show data so it can be visualized. It's a lot easier to understand data when you can visualize it. When you can visualize and understand data, you can spot trends more easily and you can make the right data-based decisions to run your business. Even if your just creating reports for someone else, when you go the extra steps to make a chart so they can visualize data, it makes you look a lot better too!
Line Graphs are the graphs that I use most often to visualize data. Line graphs allow you to see both specific data points and the trends that they create. They allow you to use data in powerful ways. In this lesson I show you how to create effective and visually stunning Line graphs in Microsoft Excel 2010.
Pivot Tables are one of the features in Excel that I get asked most often about. When your dealing with large amounts of data the best way to represent this data is using a pivot table. Pivot tables allow you to grab all of the data and slice and dice it any way that you need. You can organize it into nice, neat, and efficient tables with row titles, column headers, easy to read data, and filters that allow you to find exactly the data points that you are looking for.
Pivot Charts are a powerful, yet under-utilized part of Microsoft Excel 2010. In this lesson I show you how to create a Pivot chart and I explain to you why using a pivot chart, in combination with a pivot table is better than using a regular chart. I also show you some of the new features of pivot charts in Microsoft Excel 2010.
This section goes over formulas/functions in Excel. In this lesson I'll teach you how to work with formulas, dragging formulas throughout different cells, reference styles, basic arithmetic functions, the count function, and the max & min funcitons.
With the Most Used Formulas I got over some of the functions that I have used most on a day to day basis while creeating reports, dashboards, and automating my work. These are, in my opinion, some of the most useful formulas. The formulas covered are GETPIVOTDATA, CHOOSE, LOOKUP, MATCH, LEFT, RIGHT, MID, LEN, TRIM, AND, OR, ROUND, CONCATENATE, &
After going through this tutorial you'll know a lot of useful formulas that you can use by themselves, or in combination with eachother to understand your data and take care of a lot of the work for you.
The all important VLOOKUP Formula. This is such an important and widely used formula that I gave it it's own lesson. After going through this lesson you will have an understanding of the VLOOKUP formula works and I'm sure you will be able to find a lot of ways to use it.
In this tutorial we'll go over different time formulas that allow you to figure out the time, day, month, year and other useful formulas for different times of the year. I use these formulas a lot in conjunction with VBA code to automate my work and time macros to fire off throughout the day. The formulas covered in this video include: TODAY, NOW, YEAR, MONTH, DAY, DATE, WEEKDAY, WEEKNUM, EOMONTH, NETWORKDAYS, WORKDAY
Conditional formulas are the different types of IF statements inside of Excel. These formulas are what make Excel really powerful. They exponentially expand the situations that you can handle with your data making it a lot easier to handle dynamic and always changing data that occurs in the business world. Covered in this lesson is: IF Statments, Nested IF Statements, IF statements with the AND formula, IF statements with the OR formula, SUMIF, COUNTIF, SUMIFS, COUNTIFS, IFERROR
The Examples section is where I show you how I have specifically combined the formulas that you just learned and used them together to automate my work and get good, clean data without much work. In this tutorial I cover how to do a lot of things that you will need in your day to day work. Most people do these manually and spend hours doing this stuff. After this tutorial and understanding the previous tutorials you will be able to automate a lot of what you do.
Now we jump into Visual Basic for Applications (VBA) and Excel Macros! In this first part I give you an introduction to the developer tab, the VBA editor, how to create macros, comments in VBA, inserting buttons to start macros, and we create our first macro! We create a macro that copies and pastes data for us!
In this video we create more Excel Macros using VBA code. We go over specific macros to automate more processes for us including clearing data from cells, message boxes that pop up so the code can interact with the user, changing the fill color of cells, the macro recorder in Excel, clearing fill colors in cells, inserting text using VBA, inserting formulas using VBA, and selecting a different worksheet inside an open Excel workbook.
Now we're getting real good with VBA. In this tutorial we'll go over specific Excel Macros to filter cells based on certain criteria, select only used cells in a given range, VBA IF Statements, Excel Formulas inside of VBA code, IF ELSEIF VBA code that is the VBA equivilant of Nested IF statements, and we'll dive into LOOPS that allow you to loop through different cells in a given range. We'll start with the FOR EACH loop inside of VBA to go through a range of cells and do something FOR EACH cell in that range.
Now that we're getting good with VBA we'll get into some of the more complicated things in VBA. In this tutorial we'll go over creating a new worksheet with VBA, hiding and unhiding worksheets with VBA, Error Handling in VBA, deleting a worksheet with VBA, creating and new workbook in excel, and we'll cap off the tutorial with learning how to open a Workbook you previously made with VBA. After this lesson you'll be an expert in Microsoft Excel, Visual Basic for applications (VBA), and Excel Macros
Steve Quatrani is an excel expert with years of experience using and teaching Excel. He has created advanced systems using VBA code, advanced dashboards using Excel and other business intelligence software, and he continues to build new ways of viewing and analyzing data in Excel and automating those processes.
I was astounded by how little some people knew about Excel and they use it every day! I decided to start teaching people how to use Excel and utilize the true power of this program to make their lives easier. Throughout my video courses I teach you everything you need to know in Excel to be the Excel Guru in your office.