Hi.
I'm Pete from HubSpot, and today, I'm
going to show how to use Microsoft Excel.
If you enjoy this video, don't forget
to like it and subscribe to our channel.
Post any questions or suggestions
you have in the comments below.
Let's get started.
These days, understanding how to use Microsoft Excel
is so common that it hardly warrants a line on your resume,
but how well do you really know how to use it?
This video will provide you with the details
you need to better understand Microsoft Excel basics, Excel
formulas, pivot tables, if functions,
v-look up, and index match.
To start, let's dive into Excel basics.
Number one, how to insert rows and columns--
to add multiple rows or columns in the spreadsheet,
highlight the same number of pre-existing rows or columns
you want to add, right click, and select Insert.
Number two, how to use autofill--
autofill allows you to quickly fill adjacent cells
with several types of data, such as values, series,
and formulas.
The easiest way to do this is with a fill handle tool.
Select the cells you want to be the source.
Locate the fill handle in the lower right corner of the cell,
and either drag the fill handle to cover cells you want
to fill, or just double click.
Number three, how to implement filters--
filters pare down your data so you can only view certain rows.
A filter can be added to each column of your data,
and you can then choose the specific cells
you want to view.
To add a filter, click the Data tab and select Filter.
Click the arrow next to the column
headers to choose whether you want your data to be organized
in ascending or descending order,
and pick the rows you want to see.
Number four, how to sort--
Excel's Sort feature will help you organize and alphabetize
any list.
To do this, click on the data in the column
you want to sort, then click on the Data tab in your toolbar
and find the Sort option on the left.
When A is on top of Z here, your data
will appear in alphabetical order.
When Z is on top of A, your data will
be reverse alphabetical order.
You can toggle between these two options
by double clicking the button.
Number five, how to remove duplicates--
to remove duplicate content in your spreadsheet,
highlight the row column that you
want to remove content from.
Go to the Data tab and select Remove Duplicates under Tools.
A pop-up will appear to confirm you want to remove duplicates.
You can also use this feature to remove an entire row based
on a duplicate column value.
Number six, how to use Paste Special.
You can use Paste Special to transform items in a row
into a column or vice versa.
Highlight the column or row you want to transpose and right
click Copy.
Select the cells in your spreadsheet
where you want your first row or column to begin.
Right click on the cell and click Paste Special.
When the module appears, choose the Transpose option.
Number 7, how to move text to columns.
If you want to distribute information
from one cell to two, highlight the column
that you want to split up.
Next, go to the Data tab and select Text to Columns.
Select Delimited to break up the column
based on characters or fixed width
to select the exact location in the columns
where you want the split to occur.
Excel will show you a preview of your new columns.
When you're happy with a preview, click Next.
The following page will allow you to select advanced formats
if you choose to add them.
When you're done, click Finish.
Number eight, how to use the format painter--
to avoid repeating format commands,
you can use the format painter to copy your formatting
from one area of your spreadsheet to another.
Choose the cell you'd like to replicate,
and select the format painter option from the toolbar.
Now, let's review some commonly-used Excel formulas.
To start, let's look at formulas for simple arithmetic.
Use the plus sign to add, the minus sign to subtract,
the asterisk sign to multiply, the slash to divide,
and the upward caret for exponents.
All formulas in Excel must begin with an equal sign.
You also should wrap your formula in parentheses
to ensure all calculations are completed in order.
And here's an arithmetic formula pro tip--
Excel has built in formulas, if you don't want to type
these formulas in manually.
Next, let's look at formulas for conditional formatting.
This lets you change your cell's color based
on information in the cell.
Highlight the group of cells you want to format.
Choose Conditional Formatting under the Home menu,
and select your f from the drop-down menu.
Select OK when you're done, and your results
will automatically appear.
Another formula to use includes dollar signs,
so your columns and rows are held the same way
when you copy a formula into adjacent rows.
When you copy a relative formula from one cell to another,
it adjusts the values in the formula based
on the location it's moved to.
But sometimes, you want those values
to remain the same whether or not they're moved.
You can do this by turning the formula
into an absolute formula with dollar signs.
If your relative formula is equals A5 plus C5,
you can turn it into an absolute formula
by preceding the row and column values with dollar signs, which
would read like this--
equals dollar sign A dollar sign 5 plus sign
dollar sign C dollar sign 5.
The final formula we'll review is for combining cells.
To do this, use the ampersand symbol
to separate your data to make it as exact as possible.
For example, imagine you want to combine
first names and last names in your spreadsheet
into a full name all in a single column.
First, put your cursor in the blank cell
where you want the full name to appear.
Next, highlight one cell that contains a first name, type
in the ampersand sign, and then highlight
a cell with the last name.
Add the quote symbols with a space between them
where you want a space between the first and last name.
Now that we've covered some common Excel formulas, let's
review how to use pivot tables.
Pivot tables are used to reorganize data
in a spreadsheet.
They don't change any existing data,
but they can sum up values and compare
different information in your spreadsheet
depending on your needs.
To create a pivot table, go to Data and click Pivot Table.
Excel will automatically populate your pivot table,
but you can always change the order of the data.
You see four options here.
Report Filter allows you to only see
certain rows in your data set.
Column labels are your data set headers.
Row labels are your data set rows.
Value allows you to view your data differently.
Now, onto IF-functions.
IF-functions tell you if a condition you set
is true or false for a given value.
If the condition is true, you get one result.
If the condition is false, you get another result.
You can then filter your data to only see line items
where you're going over budget.
You can also string multiple IF-statements together,
or nest them.
This allows you to set multiple conditions,
get more specific results, and organize your data
into manageable groups.
There are three IF-functions to know.
The first is COUNTIF.
This function lets you count the number of times a word
or number appears in any range of cells.
The second IF-function to know is SUMIF.
This function lets you add up cells
that meet certain criteria.
The third IF-function to know is AND, OR.
These functions check multiple arguments
and determine whether they're true or false.
If at least one of the arguments is true, it's returned as OR.
If all of them are true, it's returned as AND.
For this function to work, the AND,
OR OR must be nested inside of another IF-function.
Now, onto V-LOOKUP.
V-LOOKUP is a formula that allows
you to combine two different sets of data
from different spreadsheets into one spreadsheet.
Before using V-LOOKUP, make sure you have at least one column
that's identical in both spreadsheets,
without any extra spaces.
The formula is equals VLOOKUP, lookup value, table array,
column number, range lookup.
Lookup value is the identical value
you have in both spreadsheets.
Choose the first value in your first spreadsheet to start.
Table array is the range of columns on spreadsheet 2
you're going to pull your data from.
Column number is the table array, or the range of columns
you've just indicated.
This tells Excel which column has new data you want
to copy into spreadsheet 1.
Range lookup should read false, to ensure you pull in
only your exact value matches.
V-LOOKUP only pulls values from your second spreadsheet
that are to the right of the column
with your identical data.
This can cause limitations, which
is why some people use the index match function.
Index match also pulls data from other data
sets to a central location.
However, V-LOOKUP is a simpler formula
which means the index match will decrease your load
time if you're working with very large data sets that would
require thousands of lookups.
Index match formulas work right to left in contrast
to V-LOOKUP formulas, which work as a left to right look up.
In other words, if you're performing
a lookup with a lookup column to the right of the results
column, then you'll have to rearrange these columns
in order to do a V-LOOKUP.
This can be tedious with large data sets.
To use index match, you'll need the formula which reads
equals index table array, match, lookup value, lookup array.
Here, table array is the range of columns
on spreadsheet 2 with the new data
you want to bring over the spreadsheet 1.
Lookup value is the column in spreadsheet 1
with identical values in both spreadsheets.
Lookup array as the column in spreadsheet 2
with identical values in both spreadsheets.
Once you have your variables, type the index
match formula into the topmost cell
of the blank column in spreadsheet 1
where you want the combined information to live.
Lastly, onto data visualization.
This allows you to represent your data
with a beautiful graph so your audience can easily
synthesize and retain it.
First, decide on which type of graph you want to use,
whether that's a pie chart, bar graph, or line graph.
Then highlight the data you want to put in your chart,
and choose Charts in the top navigation.
Click on the graph appropriate for your type data,
and you'll be good to go.
Need some additional help getting
started with Microsoft Excel?
Click the link in the description to learn more.
Thanks for watching.
If you enjoyed this video, be sure to like it and subscribe.
Let me know how you're doing in the comments below.