Excel Beginner Crash Course – How to Solve Problems in Excel
5.0 (3 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
12 students enrolled
Wishlisted Wishlist

Please confirm that you want to add Excel Beginner Crash Course – How to Solve Problems in Excel to your Wishlist.

Add to Wishlist

Excel Beginner Crash Course – How to Solve Problems in Excel

Non-intimidating Microsoft Excel course for beginners. Microsoft Excel 2010, Excel 2013, Excel 2016 basic training.
5.0 (3 ratings)
Instead of using a simple lifetime average, Udemy calculates a course's star rating by considering a number of different factors such as the number of ratings, the age of ratings, and the likelihood of fraudulent ratings.
12 students enrolled
Created by Roy Davis
Last updated 5/2017
English
English
Current price: $10 Original price: $30 Discount: 67% off
5 hours left at this price!
30-Day Money-Back Guarantee
Includes:
  • 1.5 hours on-demand video
  • 7 Articles
  • 6 Supplemental Resources
  • Full lifetime access
  • Access on mobile and TV
  • Certificate of Completion
What Will I Learn?
  • Take over someone else’s basic spreadsheet and be able to understand how it works in a matter of minutes.
  • Avoid getting lost in long formulas due to the efficiency tips I’ll give you.
  • Use key math and key stat functions with ease to help solve problems.
  • Understand common error codes and what to do about them.
View Curriculum
Requirements
  • Have access to Microsoft Excel 2007 or Higher (I’m using Excel 2016).
  • Have Windows or Mac operating system (a full feature OS). Some functions may have compatibility issues on iOS and Android (limited feature OS).
  • Know how to do basic computing tasks such as copying and pasting text, opening files, etc.
  • Be willing to learn and practice.
Description

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:

  • Financial Analyst
  • Accountant
  • Business Analyst
  • and virtually any IT position

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:

  • a course workbook (Excel file), both student and instructor versions
  • visual instruction on Udemy
  • a course companion manual in PDF form
  • and other materials to help you learn

Enroll in Beginner's Guide to Solving Problems in Microsoft Excel today! Thanks for reading and I can't wait to serve you.

Who is the target audience?
  • Beginners who aren't familiar with common functions and formulas.
  • Those who haven't used Excel in a while and need a quick refresher.
  • This Excel course is probably not for you if you’re at an intermediate level.
  • This Excel course is definitely not for you if you’re looking for advanced Excel techniques such as scripting, macros, database connections, modeling, etc.
Students Who Viewed This Course Also Viewed
Curriculum For This Course
27 Lectures
01:20:08
+
Introduction
7 Lectures 28:53

Join me for an introduction to the course. I'll briefly go over:

  • What you'll need to take the course
  • Expected user level and who this course IS for
  • Who this course IS NOT for and the reasons why
  • The goals I have for you in this course and beyond
  • Explanation and location of materials available to you
  • And what you can expect by the end

I'm super excited to be here and I'm looking forward to connecting with you.

Preview 04:28

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 a Good Student
00:12

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.

Download Course Workbook (Beginning XLSX file)
00:08

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:

  • File manipulation controls – Info to Close.
  • Account – Office 365 settings, license information, connected services, current build, etc.
  • Options – Excel specific settings, add-ins, default save file location, etc.
  • Ribbon navigation breakdown – tabs or ribbons > groups > commands.
  • Under Excel ribbon - Name box, function bar, and formula bar.
  • Page views – Normal, Page Layout, Page Break Preview, and Zoom.
  • Cells (A1), references (=A1), columns (A), and rows (1).
  • Workbooks (.xlxs file) and worksheets (tabs of workbook, found at bottom).
Preview 03:55

There are 3 types of cell references:

  1. Relative - Default state of cells
  2. Absolute - Cell is "anchored," meaning the reference stays the same until you change it. The anchor is denoted by the $ sign.
  3. Mixed - Anchored row, relative column or relative row, anchored column


References are very important aspect of a spreadsheet as you go beyond basic calculations.

Cell References
05:32

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.

Cell Reference Examples
10:26

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:

  • Click on the Hyperlink command found under Insert > Links. Or simply press CTRL + K on your keyboard.
  • You can use the HYPERLINK function: =HYPERLINK(Link Location, Friendly Name)
Linking and Hyperlinking
04:12
+
Formula Fundamentals
3 Lectures 08:18

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:

  1. Parentheses ()
  2. Exponents ^
  3. Multiplication and division */
  4. Addition and subtraction +
Preview 02:51

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:

  • =, -, or + can start a formula
  • Watch the date formatting: number / or - will auto format as date (like 9/ or 5-)
  • Formulas can contain static numbers, 1 or more cells, or functions
  • Cells can contain data, aka range, a data range can be 1 or multiple cells
  • Otherwise you're data could be stored as text
Editing and Copying Formulas
02:35

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)

Calculations Using Dates
02:52
+
Famous Functions
9 Lectures 27:58

This lecture is the mega function introduction. We'll discuss:

  • Overview of Library Categories
  • Top 5 Common Functions
  • Parts of a Function
  • 4 Ways to Insert a Function - Type it out, AutoSum menu, Insert function command, and Function library
  • AutoSum Command
Function Library Overview and Ways to Insert a Function
04:29

See how basic math and stats functions perform across a conventional matrix of data. The specific functions covered in this lecture are as follows:

  • SUM
  • AVERAGE
  • AVERAGEA
  • COUNT
  • COUNTA
  • COUNTBLANK
  • MIN
  • MINA
  • MEDIAN
  • MAX
  • MAXA
Applying Popular Functions on Statistical Values
04:36

IF functions return a value based on a condition. In this lecture we'll see the results of these IF style functions:

  • SUMIF
  • AVERAGEIF
  • COUNTIF
Functions Ending in IF
01:18

IFS functions return a value based on 2 conditions. In this lecture we'll see the results of these IFS style functions:

  • SUMIFS
  • AVERAGEIFS
  • COUNTIFS
Functions Ending in IFS
01:20

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:

  • Level 1: Grand total
  • Level 2: Subtotals and Grand total
  • Level 3: All data in range

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.

Subtotal Command
03:07

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.

PRODUCT and QUOTIENT Functions
01:24

There are 8 major ways of rounding depending on the level of precision you need:

  • INT rounds DOWN to the next lowest integer.
  • ABS is the distance from 0.
  • ROUND - The first argument rounds number 1-4 DOWN, and 5-9 UP. The second argument specifies the decimal precision.
  • TRUNC or truncate simple drops the decimals (fractional part). The second argument specifies the precision.
  • ROUNDUP rounds numbers 1-9 UP, the precision argument works the same way as the regular ROUND function. Away from zero.
  • ROUNDDOWN rounds numbers 1-9 DOWN, the precision argument works the same way as the regular ROUND function. Towards zero.
  • EVEN function rounds UP to the next even integer, skipping odd integers.
  • ODD function rounds UP to the next odd integer, skipping even integers.
Rounding Functions
04:44

The CONCAT function is perfect to get you used to using text functions. Things of note:

  • Text, numbers, and references that refer to one cell can be concatenated.
  • Numbers are converted to text.
  • Ampersand (&) can be used as an alternative to the CONCAT function. The results are the same.
Other Functions of Note: CONCAT
03:53

The PMT function is a great financial function to start playing with. The specifics:

  • Rate is interest rate.
  • Pmt (nper) is the total number of payments.
  • Pv is the present value, or the amount of the loan (paid now).
  • Future value is optional, a cash balance after last payment is made. Default is 0.
  • Type defines when payments are due. 0 is default which is end of the period. 1 is the beginning of the period.
Other Functions of Note: PMT
03:07
+
Efficiency and Error Correction
4 Lectures 13:01

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.

Range Names
06:37

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.

Auditing Features
03:26

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.

  1. #######
  2. #VALUE!
  3. #DIV/0!
  4. #NAME?
  5. #N/A
  6. #REF!
  7. #NUM!
  8. #NULL!
  9. Circular Reference
Common Errors
02:50

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.

Deprecated Function List (Includes XLSX file)
00:08
+
Conclusion
4 Lectures 02:09


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!

Test Your Knowledge
10 questions

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.

Instructor Workbook (Master XLSX file)
00:04

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!

Other Awesome Course Materials (3 PDF Documents)
00:19

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.

Discussion, Feedback, and Keeping the Course Updated
00:48

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.

Additional Reading
00:55
About the Instructor
Roy Davis
5.0 Average rating
3 Reviews
12 Students
1 Course
<3 Teaching

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:

Education

Bachelor of Science in Interdisciplinary Information Technology

Master of Business Administration, track of study in Information Systems

Professional Certifications

CompTIA A+, CompTIA Network+, CompTIA Security+