How to Use Excel Basics for Beginners

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 Vlookup 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 a spreadsheet, highlight the same number of pre existing rows or columns you want to add. Right click and select Insert. 2. 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 the 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 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 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 5. How to remove duplicates to remove duplicate content in your spreadsheet, highlight the row or 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. 6. 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 seven 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 the 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 a 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 logic from the drop down menu. Select OK when you're done and your results will automatically appear. Another common 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 a 5 plus C5, you can turn it into an absolute formula by preceding the row and column values with dollar signs, which would read like $A$sign 5 +sign $C$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 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 and 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 Vlookup. Vlookup is a formula that allows you to combine two different sets of data from different spreadsheets into one spreadsheet. Before using Vlookup make sure you have at least one column that's identical in both spreads spreadsheets without any extra spaces. The formula is equals Vlookup Lookup Value Table Array Column Number Range Lookup lookupvalue 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 arrange of columns you 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. Vlookup 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 central location. However, Vlookup 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 Vlookup formulas, which work as a left to right lookup. 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 vlookup. This can be tedious with large data sets. To use Index Match, you'll need the formula which reads index tablearray match lookupvalue lookuparray here tablearray is the range of columns on spreadsheet 2 with the new data you want to Bring over to spreadsheet 1. LookupValue is the column in spreadsheet 1 with identical values in both spreadsheets. LookupArray is 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 typed 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.