Excel for Finance and Accounting Beginner Tutorial

Summary

LearnIt's Excel for Finance and Accounting course, led by IT facilitator Elissa Smith, offers an in-depth exploration of Microsoft Excel tailored for financial professionals. The course covers fundamental navigation, data entry, and formatting techniques, advancing to complex formulas like VLOOKUP and IF statements. It also delves into financial functions such as PPMT and IPMT, aiming to enhance analytical capabilities in a business setting. Additional features include ad-free membership options and practice files for hands-on learning, with further resources available at LearnItAnytime.com.

Takeaways

• 📘 The course 'Excel for Finance and Accounting part one' is designed to teach professionals how to use Microsoft Excel effectively for finance and accounting tasks, covering everything from basic to advanced concepts.
• 🔍 Elissa Smith, an IT facilitator with over 25 years of experience, will guide learners through smart ways to utilize Excel for professional purposes.
• 📈 The course curriculum includes topics such as data entry, conditional formatting, basic and advanced formulas including VLOOKUP and IF statements, and financial formulas like PPMT and IPMT.
• 👍 To enhance learning, the channel offers membership for ad-free videos, individual purchase options for specific videos, and access to exams, certificates, and exclusive content on their website.
• 🧭 Excel navigation is introduced with explanations of cells, columns, rows, and how to use the cell name box for quick location identification within a spreadsheet.
• 🔑 The importance of understanding Excel's interface, including ribbons and quick access toolbar, is highlighted for efficient data manipulation and analysis.
• 🔢 Data entry techniques are demonstrated, including using cells and the formula bar, with an emphasis on keyboard shortcuts for efficient navigation and data selection.
• 📑 The functionalities of rows and columns, such as inserting and deleting them, are explained, showcasing how to manage and organize data in a spreadsheet.
• 🖨️ Printing, saving, and opening spreadsheets are discussed, with a walkthrough of the print preview, page setup, and file management processes in Excel.
• 🎨 Formatting in Excel is covered, including applying color, alignment, and borders to cells, and the use of the Format Painter for copying formats efficiently.
• 📈 Conditional formatting is introduced as a method to visually highlight data based on specific criteria, making it easier to analyze and interpret spreadsheets.

Q & A

What is the purpose of the LearnIt Training course mentioned in the script?

The purpose of the LearnIt Training course is to teach professionals smart ways to use Microsoft Excel for finance and accounting, covering everything from basic to advanced concepts.

Who is the instructor for the Excel for Finance and Accounting course?

The instructor for the course is Elissa Smith, an IT facilitator with over 25 years of experience teaching professionals to use Microsoft Excel.

What are some of the basic Excel features that the course will cover?

The course will cover basic features such as data entry, navigating the Excel interface, using rows and columns, saving, opening, and printing spreadsheets, as well as formatting cells.

Can you explain the use of the AutoSum button in Excel as mentioned in the script?

The AutoSum button in Excel is used to quickly create a SUM function. It automatically assumes the user wants to create a sum function and can dynamically adjust the range of cells to be summed.

What is the difference between the COUNTIF and COUNTIFS functions in Excel?

The COUNTIF function counts the number of cells that meet a single criterion, while the COUNTIFS function allows for multiple criteria to be set across different ranges of cells.

How can conditional formatting be used in Excel as described in the script?

Conditional formatting in Excel is used to apply formatting to cells based on whether they meet certain conditions, such as values being greater than a certain number, helping to highlight important information within the data.

What is the purpose of the Format Painter tool in Excel?

The Format Painter tool in Excel allows users to copy the formatting from one cell or range of cells and apply it to another cell or range, saving time when applying consistent formatting.

What are some of the advanced Excel functions that will be covered in part two of the course?

Part two of the course will cover advanced functions such as VLOOKUP, XLOOKUP, and other functions used for creating financial statements and working with specialty charts in Excel.

How does the script describe the process of inserting and deleting rows and columns in Excel?

The script explains that to insert a new row or column, a user can right-click on the row or column header and select 'insert'. To delete, the same action is taken but selecting 'delete' instead. It also mentions that deleting a column will remove all content within it.

What keyboard shortcuts are highlighted in the script for efficient navigation in Excel?

The script highlights several keyboard shortcuts, including CTRL+Home to quickly navigate to cell A1, CTRL+End to go to the last cell with data in the sheet, and CTRL+SHIFT with arrow keys to select all data in a row or column where there is typed information.

How can users access ad-free videos and support the channel as mentioned in the script?

Users can access ad-free videos and support the channel by becoming a member, which unlocks ad-free videos, or by purchasing individual ad-free courses, with more information available at learnitanytime.com.

Outlines

00:00

📘 Introduction to Excel for Finance and Accounting

Elissa Smith introduces the Excel for Finance and Accounting course, highlighting her 25 years of experience. The course covers basic to advanced Excel concepts, aiming to make Excel a valuable tool for financial professionals. Topics include data entry, conditional formatting, basic and advanced formulas like VLOOKUP and IF statements, and financial formulas such as PPMT and IPMT. The video also mentions the availability of practice files and the benefits of membership for ad-free content and exclusive access to additional resources.

05:01

🔍 Navigating and Understanding Excel Basics

This section covers the fundamentals of navigating Microsoft Excel, explaining the structure of cells, columns, and rows, and how to use the Excel interface. It discusses how to select and manipulate cells and ranges, work with multiple sheets, and enter data. Keyboard shortcuts for navigation are introduced, emphasizing efficiency over using scroll bars. The instructor also explains how to insert and delete rows and columns, and how to access and use practice files to familiarize oneself with Excel.

10:03

🖨 Printing, Saving, and Opening Spreadsheets

The paragraph explains how to print, save, and open spreadsheets in Excel. It details the process of accessing the print option from the file ribbon, which leads to the backstage view, and how to use the print preview to adjust settings before printing. The video also covers how to save a workbook for the first time and how to use 'Save As' for subsequent saves. Additionally, it describes the process of opening files from different locations on the computer and switching between open files using the Windows taskbar.

15:05

🎨 Formatting in Excel: Color, Alignment, and Borders

This part of the course focuses on formatting in Excel to enhance the readability and appearance of cell contents. It covers applying font and fill colors, adjusting cell alignment, and adding cell borders. The importance of selecting cells before formatting is emphasized, and the tools available in the Home Ribbon tab and context menu are highlighted. The video demonstrates how to change row height for better alignment and how to use the Format Painter to copy formats efficiently.

20:09

📈 Formatting Numbers, Dates, and Using Format Painter

The video script discusses how to format numbers as currency, including adjusting decimal places, and how to change the date format in cells to better suit the user's needs. It also explains how to use the Format Painter tool to copy formatting from one cell to another, which is a time-saving feature when applying the same format to multiple cells.

25:10

🔑 Conditional Formatting and Merging Cells

The script covers the use of conditional formatting to highlight cells based on specific criteria, such as values being greater than a certain number. It also explains how to use data bars to visually represent values within cells and how to manage conditional formatting rules. Additionally, the paragraph describes the process of merging and centering cells, which is useful for creating centered titles in a spreadsheet.

30:11

📊 Splitting and Freezing Panes for Better Navigation

This section introduces two Excel features for easier navigation in large workbooks: splitting the screen and freezing panes. Splitting allows users to view different parts of a sheet simultaneously, while freezing keeps certain rows or columns visible as the sheet is scrolled. The video demonstrates how to activate these features and customize them according to the user's needs.

35:11

⏩ Quick Access Toolbar and Keyboard Shortcuts

The paragraph discusses the Quick Access Toolbar and its importance for adding frequently used shortcuts in Excel. It explains how to customize the toolbar and move it for better accessibility. Additionally, the video emphasizes the value of learning keyboard shortcuts for efficient navigation and data selection within Excel, such as Control+A, Control+Home, Control+End, and Control+Shift with arrow keys.

40:14

🔢 Basic Formulas and Functions in Excel

The script provides an overview of basic Excel functions, including SUM, AVERAGE, MAX, MIN, and COUNT. It explains how to use the AutoSum button and the syntax of functions, as well as how to copy formulas using the fill handle. The importance of correctly specifying the range of cells for accurate calculations is highlighted.

45:16

🤖 IF Statements for Conditional Logic in Excel

This section delves into the use of IF statements in Excel for applying conditional logic to calculations. The video demonstrates how to use the insert function feature to create an IF statement that calculates a bonus based on a sales threshold. It explains the structure of the IF function, including the logical test and the actions for true or false outcomes.

50:17

📊 SUMIF and AVERAGEIF Functions for Conditional Calculations

The script explains the use of SUMIF and AVERAGEIF functions to perform calculations based on specific criteria. It demonstrates how to sum or average values within a range of cells that meet a set condition, such as summing sales for a particular destination or averaging values for a specific criterion.

55:18

👌 LEFT, RIGHT, and MID Functions for Text Manipulation

This part of the course covers the use of LEFT, RIGHT, and MID functions for extracting specific parts of text within a cell. The video shows how to use these functions to retrieve characters from the beginning, end, or middle of a text string in a cell, which is useful for data manipulation and transformation.

🔄 Flash Fill for Pattern Recognition and Text Separation

The script introduces Flash Fill, a tool in Excel that recognizes patterns and separates text within a cell. It demonstrates how to use this feature to extract first names from a column of full names, providing a faster alternative to using text manipulation functions for certain tasks.

📝 COUNTIF and COUNTIFS for Conditional Counting

The paragraph explains how to use COUNTIF and COUNTIFS functions to count cells based on specific criteria. It demonstrates how to count occurrences of 'yes' for commissions paid and how to combine criteria to count commissions paid in a specific location, such as St. Louis.

📈 Advanced Excel Functions for Finance and Accounting

The final paragraph of the script provides a preview of upcoming topics in part two of the Excel course. It mentions the exploration of advanced functions like VLOOKUP, XLOOKUP, and other financial statement and chart-related functions that are essential for finance and accounting professionals.

Keywords

💡Microsoft Excel

Microsoft Excel is a widely used spreadsheet program that is part of the Microsoft Office suite. It is primarily employed for organizing, analyzing, and storing data in a grid of cells. In the video script, Excel is the central tool around which the entire training course is designed, with various lessons focusing on teaching professionals how to utilize its features for finance and accounting purposes.

💡Data Entry

Data entry in the context of Excel refers to the process of inputting information into cells within a spreadsheet. This can be done either by clicking on a cell and typing directly or by using the formula bar. The script emphasizes the basics of data entry as the foundation for working with Excel, showing how to enter data in cells like B2 and C2, and the use of the Enter key to complete the entry.

💡Formulas

Formulas in Excel are equations or calculations that allow users to perform operations on data within the spreadsheet. The script introduces basic formulas such as SUM, AVERAGE, MIN, and MAX, which are essential for financial and accounting tasks. These formulas can be applied to cells or ranges of cells to perform calculations automatically, saving time and reducing manual errors.

💡Conditional Formatting

Conditional formatting is a feature in Excel that enables users to apply specific formats to cells based on whether they meet certain criteria. It helps in visually identifying important data points. The script describes how to use conditional formatting to highlight cells with values greater than a certain threshold, such as highlighting expenses over 5,000.

💡VLOOKUP

VLOOKUP, which stands for Vertical Lookup, is a function in Excel used to retrieve data from a table based on a specific value in the first column of the table. Although not detailed in the script, VLOOKUP is mentioned as an advanced function that will be explored in part two of the course, suggesting its importance in financial data retrieval and lookup tasks.

💡Financial Formulas

Financial formulas in Excel are specialized functions used for calculating payments, interest, and other financial metrics. The script mentions PPMT and IPMT as examples of financial formulas that the course will cover. These formulas are crucial for professionals in finance and accounting to make accurate financial calculations and projections.

💡Workbook and Worksheet

In Excel, a workbook is a file that contains one or more worksheets. Each worksheet is like a separate spreadsheet within the workbook, and it can be used to organize different sets of data. The script refers to these terms while explaining how to navigate, save, and print different parts of an Excel file, emphasizing the importance of organizing data across multiple sheets.

💡Ribbon and Toolbar

The ribbon and toolbar in Excel are user interface elements that provide access to various commands and tools. The ribbon is a row of tabs at the top of the Excel window, each containing a set of icons that perform different functions. Toolbars, such as the Quick Access Toolbar, are customizable and provide shortcuts to frequently used commands. The script describes how to use these elements to navigate Excel and access different features.

💡AutoSum Button

The AutoSum button in Excel is a tool that simplifies the process of creating a SUM function. It automatically selects a range of cells and generates a formula to add them up. The script mentions the AutoSum button as a quick way to perform common calculations like sum, average, max, and min, and as a feature that can be used to learn about functions.

💡IF Statement

An IF statement in Excel is a function that performs a logical test and returns one value if the test is true and another value if the test is false. The script provides an example of calculating a bonus based on whether a sales threshold is met. It illustrates how IF statements can be used to apply conditions in financial calculations and decision-making.

💡Practice Files

Practice files in the context of the script are sample Excel files provided for learners to follow along with the course and apply the concepts being taught. These files are designed to give hands-on experience and allow learners to practice their skills with real-world data. The script repeatedly refers to practice files as a resource for learners to engage with the material.

Highlights

Introduction to LearnIt's Excel for Finance and Accounting course by Elissa Smith, an IT facilitator with over 25 years of experience.

Course covers basics to advanced concepts in Excel for financial and accounting tasks.

Exploration of data entry basics, conditional formatting, and basic formulas like VLOOKUP and IF statements.

Financial formulas such as PPMT and IPMT are discussed for financial analysis in Excel.

Navigation and vocabulary familiarization in Excel, including cell reference systems.

Explanation of Excel's interface similarities with other Microsoft Office applications.

Demonstration of selecting cells, ranges, and non-adjacent ranges for data manipulation.

Understanding the concept of sheets in Excel for data organization.

Data entry techniques using cell click and the formula bar in Excel.

Keyboard shortcuts for efficient navigation within Excel workbooks.

Editing data in Excel by inserting, deleting rows and columns, and using the undo feature.

Printing, saving, and opening spreadsheets in Excel, including the backstage view.

Formatting in Excel to highlight cell contents, including color, alignment, and borders.

Using the Format Painter tool to copy and apply formats in Excel.

Conditional formatting to visually represent data based on specific criteria.

Merging and centering cells for creating titles in Excel.

Techniques to keep the top and left sections of a large workbook visible while scrolling.

Quick Access Toolbar customization and keyboard shortcuts for time-saving in Excel.

Basic formulas and functions in Excel, including SUM, AVERAGE, MAX, MIN, and COUNT.

The IF function for conditional calculations based on logical tests.

SUMIF and AVERAGEIF functions for summing and averaging data that meet specific criteria.

LEFT, RIGHT, and MID functions for extracting parts of text strings in cells.

Flash Fill tool for pattern recognition and text manipulation in Excel.

COUNTIF and COUNTIFS functions for counting cells based on single or multiple criteria.

Course conclusion with an invitation to part two, covering advanced functions and financial statements creation.

Transcripts

Welcome to LearnIt Training.

The exercise files for today's course are located in the video description below.

Don't forget to like and subscribe.

Hi everyone.

Welcome to LearnIt's Excel for Finance and Accounting part one course.

My name is Elissa Smith and I'm an IT facilitator with over 25 years

of experience teaching professionals smart ways to use Microsoft Excel.

During this course, we're going to show you everything from the basics up to

very advanced concepts on how to make Microsoft Excel your best tool when

it comes to finance and accounting.

In this course, we're going to explore everything from the basics of data entry

in Microsoft Excel to using conditional formatting to help make values Pop out.

We'll also be exploring basic formulas and even more advanced formulas

like VLOOKUP and IF statements.

We will also spend time in this course looking at how to use financial formulas

like the PPMT and the IPMT formulas to help you as you begin your journey with

financial formulas in Microsoft Excel.

Looking to support our channel and get a great deal?

Become a member to unlock ad free videos.

That's right, your favorite courses without a single ad.

Interested in a specific video?

Purchase one of our ad free courses individually.

Looking for even more?

Gain access to exams, certificates, and exclusive content at learnitanytime.

com.

More information can be found in the video description below.

Hi everyone.

In this lesson, we want to overview how to navigate in Excel and get you familiar

with some of the vocabulary that you'll actually see used in the platform.

Microsoft Excel is a data analytics program that's been

around for over a quarter century.

It's really the main application that people use to calculate and analyze.

their data in any business setting.

Now, the first thing to know about Microsoft Excel is that everything is

contained in cells, hence the name Excel.

You'll see here that in my current spreadsheet, I have these little boxes.

They're called cells.

They allow me to designate data to a specific spot within this grid system.

It's fantastic when you're trying to analyze and calculate data.

You'll see that columns are known as letters, rows are known as numbers, and

the way you locate a specific cell is to click in it, and it will be known by

its column letter and its row number.

Now, if you look up at the top of Microsoft Excel, it uses the same

interface as the other Microsoft Office applications, like PowerPoint and Word.

So you'll see that there are ribbons that you use to access different buttons.

And when it comes to navigating, you click in a cell to locate

the cell you want to type in.

You can also select rows and columns by clicking on column letters.

And if you ever need to know what cell you're in, you can come up and look

in the top left hand corner, you'll see again, the cell name box that will

tell you the name of the cell that you're currently clicked inside of.

You can select multiple cells.

This is called a range by using your mouse or your keyboard.

And you can select non adjacent ranges by selecting one group of cells, then

holding down your control key on your keyboard to select a different group.

Now the other critical thing to know about a spreadsheet is it also has sheets.

Down in the bottom left hand corner you'll see that my current

workbook has three sheets in it.

The reason for sheets are to allow you to organize data by sheet.

It's a great way to take large amounts of data and split it up

in an organizational fashion.

All you do is click on the sheet you want to go to and that sheet will come.

forward.

You can also insert more new sheets as well.

Now as always, if you need a practice file, we have practice files for

you to use during this course.

Just click in the link in the description and it will take

you to the practice files.

The practice file I'm in right now is called practice one, and you can use

it as you explore Microsoft Excel.

The next thing that we'll talk about is how to do data entry in Excel.

So join me for the next lesson.

Hey, everyone.

I'm ready to look at how to do data entry inside Microsoft Excel.

I just have a blank workbook open, and if you're trying to follow along

with me, feel free to open up Excel as well and do this as we talk about it.

Now, all you need to do to begin doing data entry in cells is

click in a cell and start typing.

I'm going to click in cell B2.

Remember, your cells are known by their column letter and row number.

When I click in cell B2, I can simply start typing.

As soon as I hit enter, you'll notice that The data entry is complete.

Now, another way to do data entry in Excel is to use the formula bar.

It is used for two things, creating formulas and doing data entry.

The formula bar is located right below the ribbons of Microsoft Excel.

It can be turned off, but by default, it should be turned on.

You'll see this long white bar where you can do data entry.

So I've clicked in another cell, C2.

This time I'm going to go ahead and type in The topic that I want entered into my

formula bar, you'll notice that as I type, that whatever I type here in the formula

bar gets entered in the cell below.

Once I hit enter, you'll notice that that information gets put into the cell.

So I can do data entry by clicking and typing or by using my formula bar.

Now let's chat for just a minute about keyboard shortcuts.

When you're navigating inside an Excel workbook, you can use

your scroll bars, but they can be a fairly slow way to navigate.

So I just want to make a few suggestions.

One of them is you can actually use the cell name box next to your

formula bar for fast ways to navigate.

If I know that I need to get down to a cell quite a ways down in my workbook,

I could scroll, but scrolling is one of the slowest ways to navigate.

So instead, I'm going to click in the cell name box and enter in the column letter.

In this case, the column letter is M.

The row number is 500.

And it doesn't matter if your column letter is uppercase or lowercase.

I'm going to hit enter.

And notice that my computer in Excel takes me all the way down to cell M500.

Now, how do I get back to cell A1 quickly?

I love the keyboard shortcut CTRL.

Home.

This is on a PC.

On an Apple computer, you'll need to use the command button for this.

But what you're going to do is look for the page up key on your keyboard.

The home button is always located right next to it.

Hold down your control key and then your home key and it

will take you back to cell.

A1 in any workbook.

This is a fast way to use your keyboard as opposed to using the scroll bars because

sometimes the scrolling will take longer than using those keyboard shortcuts.

So just remember, get a new workbook open and practice

typing in cells and navigating.

You'll get the hang of it very quickly.

Join us for the next lesson.

Welcome back.

We're ready to show you how to use rows and columns in your workbook to edit

the data in your Excel spreadsheet.

I'm using a practice file right now called Practice 2.

Please remember you can use these practice files to follow along.

They're available in the link in the description below the video.

Now, what I want to do is go into my workbook and I'm

going to click on column F.

When you click on the letter for a column, you'll notice that it selects

everything in the column below.

Now, once everything is selected, if I hit the delete key on my

keyboard, notice everything in that column will get deleted.

Now, if I come up and click on the undo button, remember top left hand corner.

up on the toolbar, the quick access toolbar.

It will bring it back.

What if I wanted to insert a new column into my workbook?

I'm going to come into the column and right click on column G.

On a PC, when you right click on a column letter, you'll get a secondary menu that

gives you opportunities to do things like insert new columns or delete columns.

Please remember that if you delete a column, notice that everything including

the content in the column will be deleted.

I'm going to undo that.

In this case, I want to insert a new column.

So I'm going to right click.

By default, when you insert new columns, they always insert to the left.

So you'll see that the current column moves over the one that I had

selected and I get a new blank column.

I'm going to go ahead and undo that.

Rows work the exact same way.

In this case, I want to select more than one row.

All I need to do is come in and I'm going to highlight rows 7 through 11.

You'll notice that I can select all the content in those rows just by

left dragging on the row numbers.

Now that I have these selected, I'm going to right click and say insert.

Now I currently have several rows selected.

When I insert new rows, they'll go above, and the same number of

rows that I have selected will be inserted into my workbook.

So you can see that by, again, working with rows and columns, you can manipulate

large amounts of data in your workbook without having to individually select

sections of the workbook with your mouse, because the columns and rows

allow you to do it all at the same time.

Feel free to use the practice files to play with rows and

columns in a workbook of your own.

Welcome back, everybody.

Let's talk about printing, saving, and opening spreadsheets.

Now, right now, I have the practice file called Practice 5

Open, so feel free to use it as we explore these functionalities.

When it comes to printing, I need to go up to my file ribbon.

to access the print option.

I'm going to come to the top left hand corner because the file ribbon is the

very first one on the ribbon tabs.

You'll notice that when you go to the file ribbon, it takes you to a part of

Excel called the backstage view, where the rest of your spreadsheet is covered up.

All the functionalities run down the left hand side.

Print is right below save as, when you click on print, it

takes you to a combination.

Print task pane on the left and a print preview on the right.

Now, I love this view because everything I need to preview and

also update my print is right here.

You'll see that you can do things like decide the number

of copies you want to print, the printer that you're printing to.

I can view the portion of the workbook that you're going to print, and even do

things like update your orientation, and I can view these changes as I make them,

and even update things like my margins, right here inside the print dialog box.

When I'm ready to print, I just click on the print button.

And my worksheet will suddenly be available on paper.

Do note that the default is only to print the current sheet that you have selected.

Now to exit print preview or the print dialogue, I'm going to

come to the top left hand corner and click on the back button.

When I'm back in my workbook, you'll notice that changes that I make in print.

Do not update the look of the workbook.

The only thing you may notice is that you're going to suddenly see dashed

lines in your workbook representing where page breaks are, and this happens

after you go in and print a workbook.

Now, what about saving this workbook?

Let's say that I've come in and made a few changes and I'd like

to make sure my workbook is saved.

We're going to come up to the file ribbon tab again, go down to save or save as.

Save is commonly used the first time that you save a workbook.

Save As allows you to take an existing workbook, navigate to where you're saving

it, give it a name, and when you click on Save, I'm going to type in my updated

name, that workbook file will be saved and you can see the new name at the top.

But saving in Excel is exactly the same as saving in, for example, PowerPoint for a

presentation or Word for a Word document.

Finally, how do I open up a file?

Well, we're going to go back to the file ribbon tab again and come into open.

This will take you into your computer where you can see different locations

where you can save and open files.

I'm going to browse, locate the file I want to open, either double click

on it or select it and say open, and then that file will open and I'll

see its name up here at the top.

As always, remember, if you have multiple files open, a great way

to switch between files is to come down to your windows taskbar.

Locate the Excel icon and then you could switch between the different files

by just clicking on them and again, try this out in Excel on your own.

So you feel comfortable opening, saving and printing files.

Hi everybody, let's talk about formatting inside Microsoft Excel.

Now what's really important in a spreadsheet is what's inside the cells.

These are what we sometimes call your cell contents.

Formatting is made to highlight or make it easier to understand

the contents inside your cells.

Right now I have practice file format one open.

Feel free to use it to follow along as we try out these different formatting tools.

There are three main things we want to talk about here, applying

color, alignment and cell borders.

Now, first of all, to apply any formatting, you need to select

the contents of your cells.

In this spreadsheet, I'm going to select cells B3 over to G3.

Most of the formatting tools will be located on the Home Ribbon tab

in the font and alignment groups, or if you right click, you'll

notice that you get a toolbar that has a lot of the same tools in it.

For this video, I'm gonna be using the ribbon options.

Let's start with color.

I've selected my cells, and the first thing I want to update

is the color inside the cells.

This is what we refer to as font color.

For this, I'll go to the.

font group and look for the capital letter A on the bottom row, and I'll

see that when I click on the arrow next to it, there are different colors I

can apply to the writing of my cell.

It's really important that when you apply font color, you ensure that the color you

select is still going to allow people to easily read the contents of the cells.

What about the background color of the cell?

This is what we call Fill color.

For this, I'm going to highlight A4 down to A13, and directly next to the font

color button, you'll see a paint can.

This is called fill color.

This allows you to update the color inside the cells.

Again, making sure that whatever fill color you pick does not make it difficult

to read the text inside the cells.

Now, alignment has everything to do with how things are aligned in the cells.

For this, I actually want to go between rows four and

five and make the row taller.

The reason I want to do this, and notice that I'm hovering over row four and five.

My mouse pointer turns into an arrow that points up and down, and I can drag down.

This will make my row taller.

Each cell is like a box, so you actually have a left, center, and right alignment,

and also a top, middle, and bottom.

What I'm going to do is I'm going to highlight row four by just

clicking on the number four.

Remember, this will select everything in that row through

the entire sheet that I'm on.

Then I'm going to come to the alignment group.

On the alignment group, you'll see that on the bottom, I have left,

I'm going to put the numbers in the center, and then I

have top, middle, and bottom.

And I can use these to align something directly in the center of the cell.

Now, in addition to that, you can also do alignments that are at a tilt.

For this, I'm going to actually go in.

and make row three taller by dragging it down.

Then I'm going to highlight, again, cells B3 through G3.

Up on that same alignment group, directly to the right hand side of

the bottom alignment, you'll see a lowercase a and b button with an

arrow pointing up and to the right.

This allows you to actually tilt text inside the cells and put them at an angle.

It's kind of fun, but again, make sure your cells or your row is

tall enough and the cells are wide enough for this to happen.

Finally, cell borders.

Now, it looks like I have borders turned on in my spreadsheet, but

these are actually grid lines, and they don't print by default.

To apply borders, I need to come in and highlight the cells I want to

apply the borders to, and then directly to the left of the paint can or fill

color, I'll see the borders button.

When I click on it, it provides me with different borders that I can apply.

I just select the border I want, click on it.

And then when I click away, I'll see that border applied to the cells.

This is a border that will print.

As always, try this out in one of the practice files or a workbook.

Welcome back.

Let's talk about formatting values or numbers inside of cells and

also using one of my favorite tools to copy formats called the Format

Painter or the Format Paintbrush.

And right now I'm in Practice File Format 2.

Feel free to use it to follow along.

Let's talk about formatting values or numbers.

First of all, you'll see that in any spreadsheet Values are always at the right

and remember that values include dates and up here in this spreadsheet in cell B2,

I actually have a date and if I make the column a little bit wider, we'll notice

that again, it aligns at the right text, which means any combination of letters

and values together or just letters themselves will always align at the left.

This is a tool to help you be able to view automatically a number as opposed

to something that is not a number.

Now, how would I, for example, take these values and make them look like a currency

because that's what they actually are?

Well, first thing you need to do is highlight them.

So I'm going to highlight cells B4 to G4.

Then on my home ribbon tab, I'm going to come to the number group.

Now Microsoft has conveniently put a dollar sign right on the second

row of this group on the ribbon.

When you click on it, it will automatically apply a dollar sign and

two decimal places to all those values.

Again, if you don't want the decimal places, notice that on the right hand

side of the group, you have the ability to both increase and decrease decimals.

Just be careful with this because if your values do have decimals and you

remove them, the values will round up.

I also want to apply this same format to my cells in B7 all the way over to G13.

So again, I'm going to select the values and go up and click on the dollar

sign and apply a currency format to all those values in my spreadsheet.

Now, what about dates?

Right now, if I look at cell B2, I've made it a little bit wider,

the column, to see the full date.

Right now, this is called the short date format.

Can I update this?

Absolutely.

I'm going to come in and go right to the drop down arrow next to the

current field where it says date, because Excel recognizes this.

And you'll notice that if I come in, it's already showing me a short

date format that I currently have.

But I can update this to a long date format.

Now what's common when you do this is that you're going to see

number signs inside the cell.

Anytime there are numbers in a cell and Microsoft Excel cannot show you

all the information, it just shows you number signs because it never wants

to show you incomplete information.

A quick trick to fix this is to come right up and double click between

the columns where you see those number signs and it will auto fit.

the content so that you can see it.

So now I have the long date format.

Now, what if I have a format, for example, these cells here don't have

decimal places and I'd like all the cells below to also not have decimal places.

This is a great time to use the format painter or the format paintbrush.

The first step is to select the cells that have the format that you like.

by going up and clicking on the format painter.

Then I'm going to come in and I'm going to highlight the cells and notice when I do

this it makes my cell or my mouse pointer in this case look like a paintbrush.

I'm going to highlight the cells that I want to copy the format to.

When I release my mouse it will have copied the formatting for me.

This is a tool that you can use also in Microsoft Word and PowerPoint

and I use it all the time.

If you don't want it to turn off.

Now, as always, try this in a spreadsheet of your own by again,

updating number and date formats and also playing with the format painter.

Welcome back.

Let's talk about conditional formatting and merge and center.

Right now I have practice file format three open.

Feel free to use it to try this out.

as we go.

First of all, up in cell A1, I'd like to merge and center that title.

I'm going to come in and highlight all the cells that I'd like to make into one.

So in this case, cells A1 through G1.

One of my very favorite buttons, the merge and center button is on the home ribbon.

in the alignment group right under the wrap text or next to the wrap text button.

When you click on this button, it takes cell A1 and merges it so all

the other cells, B1 through G1, become part of it and it centers

the content that was in cell A1.

It's a fantastic way to quickly center your title at the top of a workbook.

Now the next thing that I want to do is I want to apply conditional formatting.

Conditional formatting is a way to make things pop inside

of cells with formatting.

I'm going to come in and highlight cells B7 all the way over to G10, basically

all the expenses in the spreadsheet.

Then to apply a conditional format.

From my home ribbon tab, I'm going to come to the conditional formatting button.

Now there are different kinds of conditional formats.

We're going to try out a couple.

The most common type is called highlight cell rules.

This allows you to have something formatted in your

spreadsheet based on a criteria.

For example, is something greater than?

Less than, between, or equal to.

You can even do conditional formats that are based on text contained in a cell.

We're going to start with greater than.

I'm going to come in and say any value that is greater than 5, 000.

You type that in.

Then click on OK.

You'll see that the conditional format turns the text and also

the fill color in the cell red.

Now, can you do multiple conditional formats?

You can, but let's try a different one.

For this, I'm going to go down to row 13 and highlight cells B13 over to G13.

I'm going to go back up to the home ribbon tab.

in the styles group and select conditional formatting.

This time I'm going to come down and show you a data bar.

This is a conditional format where based on the data inside the cells, the

values, the color will go further over.

And you can see that there are different colors that you can pick.

So in this case, I'm picking the green gradient fill.

You'll see that for a cell that has a higher value, the color goes further over.

Now how do you edit a conditional format or clear it?

I'm going to re highlight the cells in row 13 where I applied the conditional format.

Go back to Conditional Formatting and come clear to the bottom

of the menu to Manage Rules.

Here in this box, I'll see the current Conditional Format that I selected.

If I wanted to edit it, I could select it and click on Edit Rule.

To clear a Conditional Format, the easiest way is to highlight

the part of the spreadsheet where the Conditional Format is located.

Go back to the Conditional Formatting button.

and come down to clear rules.

Just be careful that you select clear rules from selected cells because if you

pick clear rules from the entire sheet, we all know what's going to happen.

You're going to lose all your conditional formats in the workbook.

As always, try this out.

It's a great way to make things pop or highlight things in cells.

Welcome back.

Let's talk about how we can easily see the top of our screen with

the bottom in a large workbook.

This practice file I've opened is called Navigate 1.

Feel free to use it to follow along.

Now one of the challenges of Excel is how big a workbook can be.

This particular workbook, if I scroll down in this sheet that I'm

on, there are only about 200 rows.

But I would still spend a significant amount of time scrolling between

the top and bottom if I'm trying to remember what my column headers are.

Very common issue in Excel.

Well, Microsoft has two different ways to deal with this issue.

The first one is to split your screen.

To do this, we're going to go up to the view ribbon.

This ribbon is all about adjusting.

the view of your workbook sheet that you're in and come to the window group.

On the right hand side, top of the window group, you should

see a button that says split.

Now what this does is it puts these gray lines in your workbook.

Basically what you're doing is splitting your workbook into

what are like window screens.

There's a vertical line that you can adjust by just resting your mouse pointer

on it and dragging it to the right and left and a horizontal line as well.

Now what you'll see now is that you have Two sets of vertical scroll bars

and two sets of horizontal scroll bars.

So what I can do here is stay at the top, in this top portion of the

split, and scroll to the very bottom.

And I can also be at the far left in one of the splits, and

the far right in the other.

So I'm able to align the spreadsheet and see it in different portions.

If you don't want one of the splits, for example, if you don't

need the horizontal split, you can rest your mouse pointer on it.

Drag it down into the bottom of your screen, holding down your

left mouse button as you do it.

And you can basically just get rid of a split you don't want.

To turn the splits off, you go back up to the ribbon and click on the split

button and it will remove the split.

Now I prefer a tool called freeze panes.

This allows you to either freeze the top row on your work.

Booksheet, or the top left column, or pick an apex point of a cell.

For me, I'm going to go up and click in cell C2.

I'm currently at the very top of this sheet, and then I'm going to go back to

the view ribbon tab, back to the window group, and look for freeze paints.

There's actually a little snowflake on the button.

When you click on it, it'll give you three different choices.

Now, in this case, I'm not trying to freeze the very top row, even

though I am, but I'm not trying to freeze the very far left column.

I'm actually trying to freeze from cell C to above and to the left of that point.

So I'm going to pick instead the option at the top that says, This means anything

above and to the left of the cell that I have selected will be frozen in place.

Now, it's kind of hard to tell that this has happened, but notice if I look at the

right hand side of the spreadsheet, I see a thin black line that goes above row 2.

And also, if I look at everything to the left of column C,

I can see the same thing.

Now, to really see this in action, you need to start scrolling down.

So as I scroll down, I'm going to see that everything above

row 2 stays fixed in place.

But the exciting part is when you start scrolling to the right.

Because again, I didn't say the first column, I said everything

to the left of cell C2.

So when I start scrolling, Column A and B stay in place, but

everything else is scrollable.

So it's like you've frozen the spreadsheet in place.

To turn it off, go back up to the freeze panes button again and say

unfreeze panes and it turns off.

As always, try this out in your own workbooks.

These are tools that you'll use every day.

Hey, welcome back.

Let's talk about some ways to save you time inside Microsoft Excel.

For this, I have a practice file called Navigate to Open.

Feel free to use it as you follow along.

I want to start by talking about the Quick Access Toolbar shortcuts.

I use these all the time, and don't forget that you can customize

these to add shortcuts of your own.

Now the Quick Access Toolbar is actually one of the final toolbars

still left in Microsoft Excel.

It's important because on mine, this is where the Undo and Redo buttons are.

So Remember that if you go to the right hand side of the Quick

Access Toolbar, you'll see a line with an arrow pointing down.

When you click on this button, you'll see frequent shortcuts that you can add.

I'm a big fan of shortcuts like the Print Preview and Print and the Spell Check.

Also Undo and Redo are usually pinned to your Quick Access Toolbar for you.

If you see a shortcut that you want, or I should say, if you

see a shortcut That's not here.

Feel free to come down to more commands and add it.

All you do to add the command is left click on it and you'll

see the button get added.

And these commands will always be here no matter what workbook file you open.

To remove them you just go back to the same arrow again.

Come in and left click on the particular command you want to take off.

But it's a great way to again add your own shortcuts to Excel.

Don't forget also that the quick access toolbar can be located above

the ribbons, or you can come in and say, show below the ribbon.

And in this case, it'll be placed right above.

Your formula bar.

I like to keep it in the top left corner.

That way it doesn't get hidden, but it's up to you where you put it.

Now, what other options do you have?

We already talked about this, but I highly recommend learning a few critical

keyboard shortcuts to help you in Excel.

I just want to share a couple of my favorites.

One of the first ones is to be able to select the entire sheet

and all the data that's on it.

To do this, I'm going to do control A on my keyboard.

Now you'll notice the cell I was clicked in stays white, but

everything else gets highlighted.

Another way to do this is to go between column A, the letter, and row number one.

You'll see a rectangle, and this does the same thing.

It selects all the content in the workbook.

Another great keyboard shortcut that we already talked about

on a PC is to do control.

Home.

And remember, the home button is located directly next to the

page up on a Windows PC keyboard.

This will always take you back to cell A1.

Another great keyboard shortcut is control and now the end key.

E N D like the end of a book is located by the page down button.

This will take you to the last point where someone typed inside

your sheet that you're on.

So control home and control end can take you between the top and

the bottom of your spreadsheet.

One more good keyboard shortcut to know is how to select all the data where there's

typed information in a row or column.

I'm going to come to the top of column G and click in cell G1.

Now if I scroll down, this goes all the way down to row 200.

It takes quite a while to scroll down to it.

Sorry because I'm making you seasick while I scroll to it, but in this

cell, I'm now going to click I'm going to hit control shift down arrow.

Control shift down arrow will select everything in that column to the

last cell where something was typed.

It's a fantastic shortcut that will save you a lot of time

having to drag and select.

To unselect the content, you just click.

The same thing will work for rows.

If you click in a cell, I'm going to select cell in this case, A10, and then

do control shift arrow to the right, it selects everything inside a row.

So again, those keyboard shortcuts are control A, control home,

control end, and then control shift.

Use your arrow keys to select all the content in a row or a column

where there is typed information.

Try these out in a workbook of your own because these few keyboard

shortcuts will save you hours of time.

Now, are there more?

There are many.

So feel free to explore more Excel keyboard shortcuts because

they will save you so much time.

Hi, everyone.

Let's take a couple minutes and review the most basic formulas or functions of Excel.

So a formula is a type of calculation that Microsoft Excel knows how to do.

And there are many.

They're called functions.

Now, in my spreadsheet that I have open, it's a practice file called formulas one.

I want to review some of the most basic functions that are part of Excel.

The first one we want to do is a.

Sum function.

Sum means to add.

In this case, I want to add up cells B6 down to B9 and

place the answer in cell B10.

When you create a function, you always start at the end.

So I've clicked in the cell where I want the answer to go.

To help me create the functions, I'm going to use one of my most favorite buttons

in Excel called the AutoSum button.

Now, the AutoSum button is located in a couple of places, but one of the most

common ones is on the formulas ribbon.

Since we're creating a formula, that's a good place to go.

You'll see the autosum button is the second button in from the

left, and it looks like a sigma.

Now when you're just doing a sum, you can actually click on the top half

of the button, and it automatically assumes you're creating a sum function.

Now let's look at the syntax of the function.

You'll see that every function begins with an equal sign.

the name of the function, and then in parentheses, the range of

cells that are being calculated.

You'll always see the first cell and then a colon and the last cell.

You'll also see the formula put up in the formula bar.

I'm going to hit enter and we'll see that it's added up the cells B6 through B9.

Now, because this formula is complete, I can click on it.

Go to the bottom right hand corner, and you want to be cautious here because you

don't want your mouse pointer to look like a white plus sign or four black

arrows pointing up, down, left, and right.

It needs to look like a crosshair, like you're aiming at something.

This is called the autofill button.

I'm going to hold down my left mouse button and drag that formula.

from cell B10 over to G10.

The fill handle copies what's in a cell.

Well, in cell B10, I had a formula, so it's copying the formula.

But the exciting part is that if I click in these new formulas that

have been created and look at the formula bar, I can see that as I've

copied the formulas over, They're now copying relative to their position.

So they're updating to a new range of cells.

Let's try another common formula.

This is an average.

I'm going to come in and click in cell B12.

Now remember, to find an average, you add up all the values.

and divide by their number.

It's not fun math, so let's let Excel do it for us.

The AutoSum button can also help us with averages.

What I'm going to do is click on the arrow that's either under or next to the AutoSum

button after I've clicked in cell B12, because that's where I want my resulting

answer to go, and I'll pick Average.

The only problem we're going to see is that the range of cells

is incorrect because I only want my range to be B6 through B, B9.

So what I can do while it still has the box open, I can highlight

the correct cells and update to the correct range and hit enter.

And I'll see that it will now give me the average again.

I can now click on cell B12 where I see my Answer, go to the

bottom right hand corner, get the crosshair and drag it to the right.

Let's try a couple more common functions.

I now want to look at the max function or maximum.

Its goal is to look at a list of values and return the highest value.

You can also use the auto sum button for this.

So I'm going to click in cell B13, go up and click on the

arrow of the auto sum button.

and pick max.

Again, it's probably going to select the wrong set of cells

for your range, so highlight B6 through B9, and then hit enter.

Again, this is one that you can use that fill handle to drag

over to cell, in this case G13.

Now, the min function that's located in cell B14 does the

opposite of the max function.

It finds the lowest value.

So again, I'm going to go up and click on the arrow next to autosum,

and this time I'll pick min.

Now, it might select the wrong range, so highlights cells B6 through B9 to correct

the range and enter in your formula.

Then go to the bottom right hand corner and drag it to the right to find the

lowest value in that range of cells.

The final function we want to try out is the count function.

This will just take a list of values or even text and tell

you how many things there are.

So if you have four cells selected.

The answer will be four, but it's still very commonly used in many functions.

So I'm going to go up again after I've clicked in cell B15.

Click on the arrow next to autosum, and this time pick count numbers.

Then I will select the correct range, which is B6, 3B9, and hit enter.

And again, I had four cells selected, so the answer to the count function is

four, but I can copy that formula over.

So remember, with these five basic functions, what we're doing is

reviewing what a function looks like.

What a range looks like and how you can use the AutoSum button to

help you create these functions.

As always, be careful with the AutoSum button because it's very common for

it to select an incorrect range.

But while you're using it, you can correct that range.

Using these tools, you can start introducing yourself to the basic

functions of Microsoft Excel.

Welcome back.

Let's talk about one of my favorite types of functions in Excel and if statement.

There's a practice file for this called if functions hyphen practice,

feel free to open it up and use it.

Now in this spreadsheet, you'll see that I have months sales reps and their totals,

and I need to calculate their bonus.

However, there is a threshold.

They only get the bonus if they.

If you look at my spreadsheet, I have a few sales reps that did not make that

threshold, and I only want to calculate their bonus if the threshold was met.

This is the perfect use case for an if statement because an if statement

needs to have a logical test.

that can be set to true or false.

I'm going to click in my spreadsheet in cell O3.

And then to help me do my if statement, I'm going to use a tool

called the insert function button.

You can get to the insert function box by going up to the formula bar.

And on the left hand side, you'll see a small FX.

This is the doorway to the.

Insert function box.

Now, the first thing I need to do is go into Microsoft Excel's library of

functions and locate the if function, the top portion, I'm going to type in

the name of the function I'm looking for on the right, there is a go button

that I'll click on and it will take me into the library below and find any

functions that have the word if in them or close to the spelling I've used.

As soon as I see the function I want, I'm going to select it and then come down

to the bottom of the box and click on.

Okay.

So now that I've selected the if function out of Excel's function

library, it shows me the three different portions of the formula that I need.

The first thing is a logical test.

Well, in this case, my logical test is, was the amount, the sales total in cell

N3, greater than or equal to 7 million?

Now, it's really important that I count the correct number of zeros

for this, because if I have too many zeros, It won't calculate correctly.

I don't need to use dollars and cents because it will

automatically format those for me.

Now if that's true, then I need to take the sales total that's

in cell N3 and multiply it by 5%.

You can use that decimal if you prefer here, but I like percentages.

If that's false, I don't want it to calculate a bonus and I also just

want it to tell the person no bonus.

So anytime you use a text string inside a formula.

You need to come in and enter it in quotes.

So I'm just going to put in the words, no bonus included in quotes.

So again, my logical test has three arguments.

I should say my if statement, the logical test is their sales amount over 7 million.

If that's true, then multiply that by 5%.

If it's false, just put in the words, no bonus in the cell.

I'm going to click on okay.

And based on my function, now, if you look at the formula bar, you can see the

function, how it starts with an equal sign if, and then includes the logical test

is N3 greater than or equal to 7 million.

Then a comma followed by what to do if that's true and

what to do if that's false.

And it looks like this person sold over 8.

3 million in product.

So they did meet the threshold for the bonus and we see it.

Now, is this a formula that can be copied?

It is.

So I'm going to click in cell 03, go to the bottom right hand corner, get

the black crosshair and drag it all the way from 03 down to, in this case, 027.

And what I want you to see is anytime a sales rep had less than 7 million, the

formula goes to the false portion and just puts the words no bonus in the cells.

These are a super fun type of function and it's very common in an if statement

where you see the false portion of the formula for another entire

if statement to be nested in there up to several levels so that your

formulas can become very complicated.

Hey, welcome back.

Let's try out two of my favorite functions in one, and it's

actually two different functions.

Some if an average F.

These are a great way to only some or average content based

on them matching criteria.

That's also in the workbook.

Now there's a practice file for this called some if hyphen average.

If practice, Feel free to open it up and use it to follow along.

I'm going to start by clicking in cell K2.

This is where my first function will go.

The SUMIF function.

I'm also going to suggest that you use the insert function box the first time

you do this formula, because it does have a couple of different pieces.

So I'm going to go up to the left hand side of my formula

bar and click on the FX button.

This will take us into the insert function box.

At the top, I'm going to type in the name of the function I want.

Which is SUMIF, no words or no spaces in between the two words, and I'll

click on go on the right hand side.

Make sure you pick SUMIF and not SUMIFS when you select the function name.

Double click on it and it takes us to the second portion of the insert function

box, which is the function arguments.

I'm starting with a range.

This is the group of cells that I'm going to be highlighting.

And you'll see right here that in my case, it's column E.

I'm not going to include cell E1 because it's a column heading.

To select the cells, I'm going to click in cell E2 and then do the keyboard

shortcut control shift down arrow.

This will select all the cells at the bottom of the spreadsheet that are

in that column where my criteria is.

My criteria is going to be based on the fact that I only want to sum

those destinations that are to Cancun.

Now what is my criteria?

My criteria is that the destination is Cancun.

Because it's text, I have to contain it inside quotes.

So for my criteria in quotes, I will type the word Cancun and I

will make sure I spell it correctly.

My third line in the function arguments box is my sum range.

This is the column that I will be summing.

In this case it will be column H.

I'm not going to include sell H one because again, it's a column

header and it's not a value.

So I'll click and sell H two, and on my keyboard I will select

cells H two down to H 200.

Using the keyboard shortcut control shift down arrow.

So here we can see the three arguments of the function, my

range, which contains my criteria.

So it sells E two to E 200, and then what I'm summing, which is H two to H 200.

Now I can already see that the formula will work because in the bottom left

hand corner of the function arguments box, it tells me my formula result.

Then I'll click on, okay.

We'll see right here that it's showing me that if I were to sum all the Cancun

destinations and here I can see Cancun because I sorted the destination column.

If I come over and also select the totals in column H and then just highlight them.

I can see that down on my, again, status bar, if I come over and look

at the auto calculate, it shows me that yes, that would equal 29.

52.

Now let's do the same thing in cell M2, but rather than a SUMIF

function, we will do an AVERAGEIF.

So I've clicked where I want the formula to go, and then I'll come

up and use the insert function box.

This time the name of the function is AVERAGEIF, all one word, no spaces.

I'll click on the go button.

Once I've located AVERAGEIF down in the SELECT a function

list, I'll double click on it.

This one is very similar to the SUMIF function.

You need the range where your criteria is located.

This time it's going to be Boston, so again, it will be the same range I used.

I'll use CTRL SHIFT DOWN ARROW to select E2 through E200.

Not including E1 because it's a column header.

Now my criteria is that it's Boston.

So in quotes on the criteria row, I'll type Boston as the

destination that I want averaged.

Then in my average range, again, I'm going to average H2 down to H200.

So I'm using the control shift down arrow keyboard shortcut to select that range.

And in the bottom left hand corner, I can see if the function's going to work.

When I click on OK, you'll see that there are quite a few decimals included

with this particular formula answer.

So I'm going to come up and just format this as a currency to get

rid of some of those decimals.

Now, it looks like my answer is 530.

91.

I can again come into the spreadsheet, highlight all the Boston destinations,

and go over and highlight the total row as well, and then just highlight

the ones that will be for Boston.

If I come in and look at the average for that group of cells, you'll see that down

on the status bar, it rounds it up to 531.

But that is very close to 530 because my status bar doesn't do decimals.

These are two fantastic functions where you can only sum based on

criteria or average based on criteria.

And they combine some of our very favorite functions, sum, if, and average.

Howdy, I want to show you some of my favorite functions for reforming

data in a cell when you need to take just a portion of information

in a cell to use somewhere else.

These functions are called left, right, and mid, and there's a

practice file with that same name, left hyphen right hyphen mid.

That you can use to follow along.

Now I'm going to click in cell D2.

You'll notice that to the left, I have a customer location and code.

It's the first four characters of that cell that I need to put over in cell D2.

And the left function is a great way to do this.

To start, I'm going to hit an equal sign and type the word left.

Now you'll see that as soon as I come in and double click on

the formula auto complete, it tells me that I need two things.

I need the cell with the characters in it, which is cell C2.

Then a comma and the number of characters.

It's four and then I don't need that closing parentheses.

I can just hit enter and we'll see it's captured the first four characters.

In this case, it's a code that give me the customer location code.

Now this is a function.

So if I click on cell D2.

Go to the bottom right hand corner and get that black crosshair.

I can drag it down and you'll see that it brings the customer

location code in for me.

And that's the left function.

Now the right function does the same thing, but rather from the far left

side of the cell, it does the right.

So I'm going to go to column F this time to the.

Office code sell.

You'll see that in column E.

I have office names, but I also have a code at the end of each of those.

So here I'm gonna type in an equal sign and sell F two and type in the

name of the function, which is right.

Again, you'll notice the same thing comes up with the formula auto complete.

I'm gonna double click on it to get my opening parentheses.

And here underneath you're seeing that it's telling me again, I need to

provide the cell that has the text in it that I need to return characters from.

So I'm going to click in cell E2, and then I need to do a comma and tell it the

number of characters from the right hand side of the cell that I want to return.

It's four.

We don't need that closing parenthesis because there's

only one set, so I'll hit enter.

And you'll see in this case, it's returned the four digits from

the right hand side of the cell, which is one, three, four, three.

This is again a function.

So if I click in cell F2.

Come to the bottom right hand corner, get the black crosshair.

I can drag it down.

Now the final function I want to show you is called the mid function.

For this one, I'm going to go over to column L under customer rate.

Now column K, I have some pretty complicated decimal places that

represent the customer satisfaction rate.

I want to simplify that.

So what I'm going to do is I'm going to click and sell L2 and use.

the mid function.

This function, we're just going to type in mid after the equal sign, allows you to

select a cell and it can contain values or characters, either one, you need to give

it a little bit more information on this.

So I'm now going to go up and actually select the FX button so that you can

see the two additional arguments.

We need to tell it where the characters need to begin for what we're doing.

I want to capture stuff.

After the decimal place.

So I'm going to tell it as soon as the decimal place starts, after

two characters in, I'd like it to return three characters for me.

And then I'll click on okay.

And you'll see, based on that, it's doing exactly what I said.

Go to the second character in and return three characters after that point.

This is also a function, so I'll drag it down.

And now I'm getting a little bit.

easier information about what the customer satisfaction rate

is with fewer decimal places.

Now these are three functions, but they are all again functions.

I want to show you another tool that actually works a little bit faster and

can accomplish some of the same things.

I'm going to drag over to the right.

In column B, I have my customer names.

Now what if I just wanted the first name of the customer

and not the last name with it?

I'm going to right click on column C and insert a new column that I'm just

going to call first for the first name.

We'll call it first name.

Then underneath, I'm going to type in the first name that I see next to

again in cell B2, which is Antonio.

Now I need to create a pattern here for Excel to understand.

This is using a tool called Flash Fill.

It's also important that you spell it exactly as it appears.

in the cells to the left.

Now, it looks like right now my computer is not picking up on this pattern.

So to force it to recognize the pattern, I have two options.

I can do control E on my keyboard, or if I go up to the data ribbon tab

and come to the data tools group, I can also click on the lightning bolt

button, which is called flash bill.

And this helps Excel to recognize the pattern that I'm asking it to take out.

The first names from each of the cells to the left and put them in

column C for me so that I don't have to use a function to accomplish this.

So this will allow you to combine information and also separate

it as I've just showed you.

Try this out because sometimes it's faster than trying to use a function.

Welcome back.

Let's talk about the count function and how you can combine it with the

if function to make it really useful.

The count function by itself is probably the simplest function in Excel.

It does basically what it sounds like.

It counts how many of something are in cells.

We want to combine it though, to count based on criteria.

This is where you take the count function and an if statement and put them together.

I'm in a practice file right now called count if and count if S

please open it up to follow along.

I'm going to click in cell K two.

My use case is I need to count how many sales reps have received a commission.

I can do that if I only do the count if cell I or anything

in column I has the word yes.

So it's a great use case for account.

If we are going to use.

the insert function button to help us with this.

So I've clicked in cell F2, then I'm going to go over next to my formula

bar and click on the FX button.

Now I need to type at the top, the name of the function that I

want to use, which is COUNTIF, all one word, click on the go button.

And then for this first one, make sure you use COUNTIF and not COUNTIFS.

There's only one letter difference.

Now, again, my range are going to be cells I2 to the bottom.

So I'm going to click in cell I2 and then use my control shift down arrow to get

everything down to the bottom selected.

Now for my criteria, it's if the word yes is located in the cell.

So I'm just going to type the word yes, and you'll see right here,

it's telling me that there are 102.

I'm going to click on OK.

And based on that, yes, being found in certain cells and no in

others, it's done a count for me.

Now for the commission, no, I want to do the same thing.

This time, though, again, I'm going to come in and type in count if

For my function name, the first thing I'll need is my criteria.

And again, that will be column I, cells I2 down to the bottom.

So I'm using that control shift down arrow.

This time I'm not using the insert function button.

Then my criteria this time is if no commission was paid in

quotes, I'll type the word no.

Then because the formula only has one set of opening and closing

parentheses, I'll hit enter.

And we'll see that there's a 97 and really this is correct.

Now, if I come to cell M2, it gets trickier.

I only want it to do a count if the location or the

destination in this case is St.

Louis and a commission was paid.

So you can see here that I've got two different things going on.

And as I scroll through the different locations, we'll

see as we come down that St.

Louis is one of the destinations that's in column E.

So to do this one, I'm going to come in and use the insert function button.

So I've clicked in cell M2, I'm going to go up to my FX button, and this time I'm

going to type in the word count if S.

It's really important you get that S at the end, because this is what allows you

to have two criteria in your formula.

So, the first thing I need to do is come in and select my first criteria,

which in this case is going to be whether a commission was paid or not.

So, I'm going to come in and do the I 2 down to the bottom

again and type the word yes.

Now, you'll notice that as soon as I do my first criteria range, it opens

up to give me an additional one.

For this one, it's going to be, again, is the location, like we

talked about, or the destination.

So, St.

Louis.

So for this one, my criteria range will be cells E2 down to the bottom.

And I'm using that control shift down arrow again to get that.

Now my criteria is going to be St.

Louis.

I need to put it in quotes, and I also need to be sure it's spelled the

same way it is in the spreadsheet.

And I can double check that right here.

I can see the spelling.

Then I'm going to go ahead and click on okay.

And you'll see that based on what I did right there, if

I come down and look at St.

Louis.

If I were to again, have the spreadsheet sorted in ascending order, which I do,

I could come over and I could actually check this and count how many St.

Louis yeses I have and we'll do it.

1, 2, 3, 4, 5, 6.

So if I look at my function answer, it's correct.

So again, it's only counting if yes, they had a commission

and yes, the destination was.

Good old St.

Louis.

These are super fun functions, and you'll see them used a lot in

conjunction with other functions to only count when certain criteria is set.

Thank you so much for joining us for this part one of our Excel

for Finance and Accounting.

In this course, we've started at the very beginning by how to navigate

in Excel, and then we jumped into formatting, basic functions like SUM,

AVERAGE, MIN, MAX, and even COUNT.

And then we jumped into more functions like being able to use SUMIF, Average if,

and even count if and the round function.

Join us for the next portion of this course, the part two, where

we're going to explore more advanced functions like VLOOKUP, XLOOKUP, and

even functions that you will use as you start creating your own financial

statements and some of the specialty charts that come with Microsoft Excel.

Thanks for watching.

To earn certificates and watch our courses without ads, check out LearnItAnytime.

com.