At the end of the course you will be proficient in Excel by knowing how to use the most commonly used Excel functions in today's business environment. You will no longer be dreading Excel but rather enjoying it because of the concepts-based, and simple-to-understand approach.
Master Excel in just a couple hours.
I designed this course from a concepts point of view, easy to understand and adaptable to newer upcoming versions of Excel. The high quality video lectures along with the assessments and hands-on working file, will make learning effective for you.
Excel is the second most used application in a business environment. Whether you are a student, a professional in a general field or a financial guru, knowing how to use Excel effectively is a must. Moreover, it helps you become more effective in your job or career.
So in this journey together, you will learn in a practical way in first understanding how things work in Excel and then secondly applying them. The key in this course is to understand the concepts first and then actually do them. The reason for that is if you understand how spreadsheets work, then you can use any version of Excel, Google Sheets or even open source software in accomplishing your daily tasks. In this course we will use Excel 2013. However, if you follow closely, the same concepts and functions will work in the same way in prior versions.
Let's get started first by getting to learn about the application and where things are so that when we reference something, we would know where things are and make sense out of them.
In this section we are going to learn about:
Before we proceed, let's check how we did so far.
Now that you already know how to get around the Excel application, now we will learn a bit about:
Make sure you review and follow the video hands-on first using the provided downloadable materials in lesson 2. So when ready, let's check whether you are ready to move to the next lesson.
Here in this section we are going to learn about 15% or more of what Excel users use in their day-to-day profession. It is really the basis of what one does with Excel. Here we use the cases with payroll and calculating deductions, monthly and annual income.
This section may seem a bit out place at this stage of learning. However, it is introduced here because it will save you time and make your use of Excel more effective particularly when used in conjunction with the Autofill feature. After all, that is why you are taking this course.
Remember to use working file provided in the previous lesson. The content in this lesson is on the second worksheet under the Types of References: Basic Absolute References.
Just a basic review before we proceed any further.
So prior to advancing to the more advanced and fancy stuff in Excel, let's learn about conditional formatting and how it works with a set of data.
Data sorting and filtering is another key area when working with data as a professional no matter what your field. In this lesson we learn how to:
Use the Data Filtering and Sorting worksheet from the Working File provided earlier.
Just a couple questions for emphasis.
One of the nice features of Excel is the visual representation of data by using charts. There are a few different types of charts and using the proper kind to represent your data is important. You want to ensure your audience understands the point you are trying to make based on the findings from your data.
Use Charts 1 and Charts 2 Worksheets from the Working File for this lesson.
Let's just quickly make sure you understood a couple of the concepts related to charts before we move to the next section for this course.
Here we will learn how to create a summary worksheet from a bunch of other sheets. Basically how to post the values from one worksheet to another via a formula. This comes in handy for budgets.
There is another way to do this by using Named References. That lesson will be covered later in this course.
Use Cross-Sheet Calculations sheet in the working file for this project. Then also use January, February and March.
Posting the percentage on how your investment or how your business is grown is key in Excel. In this lecture we will learn how to calculate the percentage via a formula. Fairly easy to do.
Use Calculating Percentages sheet from the working file.
The IF logical function is not used quite often in the workplace. However, knowing how to use it is always a good thing. It basically works like this. You are basically saying, if a certain cell value meets a criteria, then you post something like true (or whatever else in words) or you can post a value, if the condition is not true, then you want the computer to post something else.
So checkout the video to understand how this works. It can come in handy to have the computer determine the values or bonuses etc. based on a condition that you set. Note that here you can also use the Autofill and Absolute references that we learned earlier.
Use the If Statement sheet from the working file for this project.
Learn how to calculate the Payment (PMT), Principal Payment (PPMT) and Interest Payment (IPMT) for a loan in Excel. It comes in handy if you are dealing with financial calculations as well as use it for personal loans etc.
Use the Financial Calculations sheet from the working file for this project.
Learn how to use named references in formulas in multiple worksheets within a spreadsheet. Instead of using the physical location as we learned earlier, here you can simply call the name of the reference and you are all set in posting the value.
Use Ranges and Drop-Down Lists worksheet for this project.
If you want to ensure that the data is always entered correctly using validation rules and drop-down lists is always suggested. In this lecture you will learn how to create the lists and set validation rules.
Use Sheet 3 and Sheet 4 for this project from the working file.
Pivot tables are a powerful tool in looking at the data from various angles. I would say it is similar to blending the data and tinkering with it. So you will learn to tinker with the data.
Learn how to set the print area in a spreadsheet prior to to customize printing. The print area is basically an area that you select/choose to print. So here we will learn how to set that.
A lot of times you have to create monthly or regular reports where you will be pulling numbers from an Excel spreadsheet. Wouldn't it be nice to have a mechanism where you can simply change a couple things in your report and then pull the data from your live Excel spreadsheet.
In this video we will learn just that: Link a portion of our spreadsheet with a report in Word. Then we will change data in Excel and open the report and see that that data is automatically updated. Quite helpful.
See the Basic Calculations worksheet from the data file for this project.
Learn about mechanisms to import and export data particularly by using a .csv file. We will import data from a text file in .csv file and then learn how to export data to send to other systems.
See the Invoices List.txt file attached below for this project.
It has been a while since we have not checked how we are doing. So let's see.
Learn how to share the spreadsheet via email, via the cloud or convert the file into PDF format. It will print only what was selected in the Print Area.
This module is pretty short. And if you made it this far, good for you. You deserve a break but it should have been worth the time.
With 17+ years of hands-on experience, a graduate degree in technology, certifications like MCSE, CNA and TCT and even teaching experience at a U.S. university, I strive to add value to my valued audience by providing concept-based and simple to understand technology lessons.
I believe technology is simple for anyone if it is presented the right way. My approach is to present the material in a simple way to understand the concepts and then be able to apply it easily.
Check out the courses on this site. You will not be disappointed.