Excel is still popular today.
This course will provide a foundation to students who will be taking classes or starting work where Excel use is required. Jobs that use Excel include but are not limited to:
Other less involved professions may include administrative assistants or simply someone who has inherited someone else’s spreadsheet and needs to be able to quickly jump in.
Join me as we talk about the basic formula creation process and take a look at popular Math and Stat functions.
We won’t be exhausting every Excel tidbit but I’m willing to bet you’ll remember the content in this course. The overarching theme is mastery versus overload and the goal is to get busy people up and running in Excel as quick as possible.
Even with web apps and the software as a service (SaaS) business model being prevalent as they are, Excel is still used in today’s businesses. Sometimes these expensive web based businesses don’t scale very well to a specific calculation or forecast. Being able to whip up something quickly can be the difference in the perception of job performance.
A great example of this is something that happened at one of my jobs:
I was tasked to show the stats of blocked and allowed emails that were processed by our email filtering system. This system had built-in reporting capabilities but it didn’t present the data very well.
So I imported the data in Excel, separated it nicely, and added charts and graphs. After I setup the data sheet I added another sheet that predicted the amount of emails in each category that would be received within the next 60 days.
Not only was this report a hit with my boss and other management, but I was able to communicate expertise that management didn’t even know I had.
This report opened the opportunity to jump from being a specialist to an analyst - all this from spending a little extra time in an Excel.
The point is Excel is more than a simple office program that runs your home budget.
Knowing Excel can be beneficial in your professional career. And I’ll teach you the basics of Excel in this course.
I designed this Excel course to be easily understood by absolute beginners. At the end of the course you will understand how to identify and navigate pieces of Excel, how the formula creation process works, how to use popular math and stat functions, and how to use the diagnostic functions to help solve those pesky errors.
I'll provide materials to you to help you learn.
The method of teaching is a walkthrough in Excel. I'll provide:
Enroll in Beginner's Guide to Solving Problems in Microsoft Excel today! Thanks for reading and I can't wait to serve you.
Join me for an introduction to the course. I'll briefly go over:
I'm super excited to be here and I'm looking forward to connecting with you.
In this lecture, I'll briefly cover 5 ways you can get the most out of this course.
I promise you won't get the benefits of this course by watching alone. Be a good student by actively following along and participating in discussions and feedback.
Be sure to grab your course workbook so you don't have to manually re-type everything! The student workbook is attached to this brief lecture.
In this quick tour you'll get a general overview of the interface. That way when I get to specific terms, we'll both be on the same page. Topics covered:
There are 3 types of cell references:
References are very important aspect of a spreadsheet as you go beyond basic calculations.
It's time to test your knowledge on cell references! If you're up for it, go ahead and try to complete the References worksheet in the student workbook before watching this lecture. If not, no worries, we'll go over it together. The important part is participating somehow.
Linking focuses on pulling data, whereas hyperlinking focuses on jumping to targets. All major types of hyperlinking are supported.
Ways to insert a hyperlink include:
Simple formulas can be described as something that can be entered in a calculator. As you create simple formulas, be mindful of the order of operations, otherwise your calculation will be inaccurate.
Order of Operations:
There are many different ways to easily edit and copy formulas. Cell references can change depending on how the reference is made. A couple tips:
Dates are stored as serial numbers so date calculations are easy!
The serial numbers start at January 1, 1900 (serial number 1). January 1, 2016 is serial number 42,370. The more recent date is 42,369 days after the original January 1, 1900 date.
If you create a simple formula with dates, you can calculate the days in between. Just take Date 2 minus Date 1 (for example =A2-A1)
This lecture is the mega function introduction. We'll discuss:
See how basic math and stats functions perform across a conventional matrix of data. The specific functions covered in this lecture are as follows:
IF functions return a value based on a condition. In this lecture we'll see the results of these IF style functions:
IFS functions return a value based on 2 conditions. In this lecture we'll see the results of these IFS style functions:
The Subtotal command adds automatic subtotals to a table of data without the need of creating additional cell spaces and subsequently, multiple SUM functions.
1 | 2 | 3 represents the level of detail in view:
If you no longer need the subtotal display, simply remove them by toggling the Subtotal command. It’s not necessary to delete the added rows.
In additional to manually multiplying and dividing cells, you can also calculate the same results by using the PRODUCT and QUOTIENT functions.
The PRODUCT function really shines when you have a large(r) set of numbers to multiply.
The QUOTIENT function can't string together multiple arguments like the PRODUCT function. Also, notice how this function is called QUOTIENT and not divide.
There are 8 major ways of rounding depending on the level of precision you need:
The CONCAT function is perfect to get you used to using text functions. Things of note:
The PMT function is a great financial function to start playing with. The specifics:
As your spreadsheet grows so will your frustration if you don't start naming your ranges. Range names make it extremely easy to calculate values, even without the need to navigate to the appropriate cell!
You can use the Name Box to insert names - mentioned in the Interface Tour (Lecture 4) or you can navigate to Formulas > Defined Names and select the Name Manager.
This lecture takes place in the Formulas > Formula Auditing group.
Precedents and dependents are labels of cells to help trace formulas. Tracer arrows visually display this trace. Evaluate Formula is a great command to see different parts of a nested formula evaluated in order.
There are 9 common errors that you're mostly likely to come across. I'll go over their diagnostic code and the reason why they show up.
Compatibility functions are old functions that are now deprecated. These functions should be replaced in your spreadsheet as soon as you're able to safely replace them. A deprecated functions list workbook is attached to this short lecture.
It's not as important to memorize key Excel terms and processes as it is to understand how Excel generally works.
This will be fun!
Let's see how well you do!
You've made it to the end! Interested in the instructor's workbook? I have it attached to this short lecture.
The student workbook is blank in the calculation areas. The instructor workbook is the completed student workbook with a few extras.
Supplement your learning with 3 awesome PDFs. The course keyboard shortcuts PDF has shortcuts covered in this course and other shortcuts that are relevant for beginners, the cheat sheet is essentially a summary, and the course companion is essentially the full course in written form. They are all attached to this short lecture. They are all DRM-free PDFs. Enjoy!
Thanks for joining me in this course! Did you see all resources available to you? Did you participate in discussion and feedback? What would you like to see added? You're awesome, thank you! I plan to keep this course alive with updates and fixes as much as possible.
This lecture is primarily links to free resources for future reference. Feel free to recommend a resource but it has to be free, per Udemy policy.
There’s something interesting about the way we learn new tools and applications. We say things like “This project will look good on my resume” or “I better learn how to use this program to get (or keep) this job.”
I’m willing to bet you’ve said this once or twice before.
I know I have.
By being reactionary towards learning new skills and abilities, you’re putting yourself in the same bin as everyone else. Knowledge building and skill acquisition shouldn’t focus solely on self-development; it should be focused on team or even community development.
I challenge you to not only stop adversely “binning” yourself, but also to view new projects, gaining new skills, and new opportunities as a way to help solve someone else’s problem. Better put, I want you to use your new found skills to serve others first.
This isn’t a hold hands and sing along by the campfire type of venture. Trying to figure out solutions, especially for others, makes your mind work. Whereas, simply learning and occasionally using your new skills at your job let’s your mind off the hook.
It’s not knowledge that is power; it’s the application of knowledge by taking action that holds power. The best way to apply your knowledge is to flex that big ol’ brain of yours.
All good instructors want to see their students apply the knowledge that was taught. I want to take it a step further. My goal is to not only bring applicable knowledge into my courses, but to also shift your perspective, even by a little bit. If you’re interested in the topics I teach, I’d love to see you on the inside.
Enroll in one of my classes today. I can’t wait to serve you!
Topics of interest: Software applications, Internet marketing, Copywriting, eCommerce, Web design and development, Business, Management and Leadership, Entrepreneurship, Technology, Automation and productivity, Health and exercise.
The boring background stuff:
Bachelor of Science in Interdisciplinary Information Technology
Master of Business Administration, track of study in Information Systems
CompTIA A+, CompTIA Network+, CompTIA Security+