Microsoft Excel is a fantastic tool for analyzing data. You can upload mounds of information from other sources, but how do you format it all to help the viewer understand the data? Use this tutorial to learn how to use Microsoft Excel, and then check out the video tutorial links at the bottom of the page to get a visual representation as to how some of these tools work.
How to Pin a Recent Document
When you open Excel, the first thing you should see is a list of recent items on the left panel and various kinds of templates on the right. Reopen a recent project or pin something so that you never waste time trying to find an important file.
To pin a project to the startup page, hover your mouse over an item name. A small pin icon will appear on the right side of the highlighted project – click on it to pin that item.
User Interface
The setup of Excel is straightforward if you learn how to use it, and you will see that it is very similar to other software in Microsoft Office. When you open a workbook, the top bar of tools is called the ribbon, the different categories along the top of the ribbon are tabs.
You will utilize the Home tab the most. Most of the tool groups on the ribbon also have a small arrow in the bottom right-hand corner that you can click to expand the customization of each tool.
When you begin typing in a cell on the spreadsheet (a cell is one of the boxes where a column and row meet), it will display the text on the sheet and in the formula bar located at the top of the spreadsheet. You can make edits in either of these locations.
How to Rename a Worksheet
In a new workbook, the first sheet will have a label at the very bottom of the screen that says, “Sheet 1”. Add a new worksheet by clicking the plus sign next to it and change the name of any sheets by double-clicking the sheet name and typing a new one.
How to Zoom
To make the sheet easier to see, change the magnification in the View tab. Click the Zoom tool for zooming options. You can change this at any time.
Also, you can adjust the zoom with the slider at the bottom right-hand corner of the screen, which saves some time.
How to Move Between Cells
Since a workbook in Excel contains cells rather than open space on a blank page, movement while typing is a bit different than Microsoft Word. You can use the arrow keys to move around or click directly in cells.
Here are other keyboard keys you can use:
TAB – move one cell to the right
SHIFT + TAB – Move one cell to the left
ENTER – Move to the beginning of the next row
SHIFT + ENTER – Move to the cell directly above the current one, in the previous row
How to Widen a Column or Row
Move your mouse to where the cursor is over one of the dividing lines between two columns or rows. Click and drag that barrier line to the width that you desire. Another option is to double-click on the line to size the cell to its contents automatically. You can see here that your cursor will look like two adjoining arrows:
Any adjustments like this will make the data easier to read.
How to Change Text in a Cell Without Deleting
When you click directly in a cell and start typing, the text that was previously inside the cell disappears. To avoid this deletion problem and add additional text, double-click in the cell before typing or click once and type in the formula box above the sheet.
How to Change an Entire Row
As well as changing the format in a cell, you can also change it for an entire row or column. To alter the font or other formatting aspects, select the entire row or column by clicking the letter or number. The cursor will change to an arrow, indicating that you are selecting the entire row, and then it will be highlighted in grey. You can then change the font style, size, color, alignment, and so on.
How to Autofill Cells
Auto filling cells is a shortcut that will save you a lot of time. One way to use the Autofill feature is by typing a day of the week, and then clicking and dragging on the corner to automatically fill in the rest.
Click inside the cell, then drag the bottom right corner down as far as you need. You can also use Autofill for months, number patterns, and other similar types of data that never change. The Autofill feature works in any direction.
You can also add a custom autofill.
Click on the File tab. Go down to Options. Excel Options will pop up, and then you should go to the Advanced section in the box. Here, you will scroll down and click Edit Custom Lists… In the List Entries box, enter a list of names or any type of list you want and then press OK. Otherwise, you can import a list from cells, in which case, you would click Import, then highlight the cells of data and press ENTER.
How to Add Borders
To add borders around cells, select the cells you want to change, and then click the Borders button in the Font group of the Home tab. It is right below the font size box. You can add a border around the outside of the cells, on all the barriers in-between, or to just one side.
The Wrap Text Tool
Instead of going through and changing several column widths to make contents fit, long lines of text look best when you wrap the text. In the Alignment group of the Home tab, use the Wrap Text button. First, select a cell, a range of cells, a row, or a column. Look at these before and after screenshots of when you wrap text.
The Merge & Center Tool
For a title, you will want to use the Merge & Center tool to give it emphasis. Start by selecting the cell that has the title plus the cells that will hold the adjusted title.
To select multiple cells, click and drag, starting with the first cell. Do not click the cell and then drag the corner because that will cause it to copy the title several times in the other cells. Instead, click and drag in one move and then click the Merge & Center button to center it across all the selected cells.
It should look like this:
How to Insert a Row
To insert a new row between existing rows of data, right-click on the number of the row (you can find this on the left-hand side of the sheet) and select insert from the drop-down menu. This action adds a new blank row and pushes the row you selected, down.
Putting in Currency Data
When you add dollar amounts to a spreadsheet, there is a way to change the formatting so that Excel recognizes it as currency and treats it as such. Enter the amounts in the cells as regular numbers, with decimals to represent cents if there is any. Then, select all the appropriate cells and click the dollar sign symbol in the tools on the Home tab. This tool changes the number format. It should now say “Accounting” above instead of “General.” If you click the arrow next to where it says, “Accounting,” you can see that there are other options available for the number format as well, like Percentage.
After changing a cell to the currency format, you can then adjust the number of places after the decimal by clicking either the Increase Decimal or Decrease Decimal tool. Although extra decimal places are not always useful for currency, you can use the feature for percentages too.
Click the small arrow in the bottom right-hand corner of the Number group on the Home tab to expand the Format Cells tool for more options and create a better visual of the decimal place change.
Printing
A vertical, dotted line indicates the printing section of the sheet. Adjust column widths to help move all your data into this section. To print, select the File tab and Print. It will show a preview of the page. The barrier lines that were on the sheet do not show on the preview; you must add borders to create separation, if needed.
Basic Formulas
Formulas in Microsoft Excel are equations you enter to make calculations based on a cell’s data automatically. When you type a formula, you can type it into a cell or the formula bar above, while the correct cell is selected.
For basic math equations, start entering a formula by typing the equals sign, followed by the equation. An example would be: =2*2
Enter the formula and then press ENTER. It will show the answer to the equation, like this:
The formula remains in the cell, so you would need to change the formula by typing in the formula bar, thus changing the answer in the cell when you press ENTER.
Another way to use a formula is by referencing cells, rather than just typing a number times a number.
For example, start by typing the equal sign, then use your mouse to click a cell that you want to use in the formula. It would be the same as copying the amount in the cell, but now, when you change the value in the cell being referenced, it will automatically update the cell with the formula and change the answer displayed.
Type the mathematical sign you want to use, then click another cell or type a number—press ENTER.
Each cell has a name according to its placement in the sheet. A cell is called E3 in a formula because its location is in column E, row 3. This name is what shows when you click a cell to reference in a formula.
How to Copy a Formula
Once you enter a formula into a cell and press ENTER, that formula only applies to the cell you just used. You can quickly copy this formula down to the cells below it by clicking the bottom right corner and dragging it down. The formulas will adjust to the new cells.
Another way to copy a formula is to copy and paste.
Click on the cell you want to copy, which has a formula such as SUM (you will learn this formula next). Then, right-click, press copy, click the next cell, right-click again, and select paste. The formula will copy over and make a new calculation using the new row of numbers. The original cell copied should have a dotted line moving around the edges; to deselect that cell, hit ESC on your keyboard.
How to Freeze Rows
The Freeze Panes tool allows you to view two areas of the sheet at once, no matter how far apart they usually are. Click the Freeze Panes tool in the Window group of the View tab to freeze panes, top row, or first column. Now, the frozen area will stay in place as you scroll through the worksheet.
Functions: How to Find a Total Using SUM
The fastest way to find the total of a group of numbers is to use AutoSum. The AutoSum tool is under the Formulas tab or the Home tab. To use it, click the cell where you want the total to show (best placed in line with the amounts being added). Then, hit AutoSum to fill in the formula and total automatically. After you hit AutoSum, you can change the exact range of cells used in the total by clicking and dragging on the highlight box of cells. Hit the ENTER key.
Functions: How to Use the AVERAGE Function
On the Formulas tab, under the AutoSum function, you will see the function, AVERAGE. Select a cell in the sheet and then hit AVERAGE. You can then click and drag to select the appropriate cells that you want to average, and then press ENTER.
Now, when you click that cell, the formula bar will say =AVERAGE and then a reference to the cells, such as (D3:D12).
Functions: How to Use the ROUND Function
To find the round function, start typing in either a cell or the formula bar. Begin with the equals sign = and then type “round” and click the function, ROUND. It should come up like this:
Now that you have the ROUND function up, you will need to click the cell with the value you want to round, then, separated by a comma, type the number of places after the decimal you want. Hit ENTER. Here is an example:
Use ROUNDUP to round up and ROUNDDOWN to force the function to round down.
Tell Me
Most Microsoft software has a Tell Me box, which you can use to search for a tool. In Microsoft Excel, the Tell Me search bar is up at the top of the window. Start typing to search for something, then choose from the list. It will help when you cannot find a tool or if you are just starting to learn how to use Excel.
How to Insert a Chart
Highlight a group of names or words to use for the chart.
Hold down the CTRL key and select the other pieces of data you want to add to the chart.
Click on Recommended Charts on the Insert tab and choose a chart. Look at the All Charts tab to see more options. Find the one you want, then hit the OK button.
The chart will now insert directly onto the worksheet.
The Sort & Filter Tool
To quickly sort a range of cells from A to Z, you can use the Sort & Filter button on the Home tab of Excel. Highlight the cells you want to sort, then press Sort & Filter.
You can also custom sort by a different aspect, such as cell value. To do this, click the Sort & Filter button and click Custom Sort… from the drop-down menu. Make sure to select cells on the sheet first. Change the sorting options on the box that pops up, and then press OK.
Add multiple levels to the custom sort to make sure it sorts the list exactly how you need it, especially if you want it to sort one column first, a certain column second, then the rest. To add a level, press the Add Level button while you have the Custom Sort settings open.
On the Sort & Filter button, there is also an option to use the Filter. Select the cell or cells you want, and then hit Filter. This will add a drop-down menu button to the cell. It is best to apply this to one of the labels so that you can use the dropdown filter to view certain items listed below it.
When you apply a filter to a group of numbers, you can use the Number Filters from the new drop-down menu on the cell. Number Filters can sort based on specific criteria, like if it is greater than a certain amount or above average.
Custom Autofilters are useful as well, which you can find under the Number Filters button.
Cell References
After typing in a formula, such as: =B1*A1 , press F4 to make the cell reference absolute.
In this example, the A1 reference changes to $A$1. The dollar signs represent that it is absolute and will not change when you copy the formula across cells.
If you hit F4 again a few times, it changes which part of the cell reference is absolute – either just the column, just the row or both. If only column A was absolute, and you copied the formula, it would hold onto that A column instead of moving over like normal.
10 Tips for Making the Most of Excel
After learning all these skills, use these 10 helpful tips to help you make the most out of Excel. Microsoft Excel can save you a lot of time on projects, but only if you know some of these tricks.
1. Quick Analysis
The Quick Analysis tool shows up when you highlight a range of cells. It is a small box to the lower right of the highlighted area. It has numerous quick-use tools and allows you to view a preview of each change when you hover over it with the mouse. Some of the tools Quick Analysis provides are conditional formatting, chart creation, automatic total calculations, tables, and sparklines.
When you use Quick Analysis, it saves the time it takes to go through tabs finding the right tools to make big changes like this. Of course, for more customization, you may want to take the harder route, but Quick Analysis speeds things up.
2. Filter
The Filter is something we talked about earlier in this reading that can really help you to find certain items or groups of items from a large group of data. We had used the Sort & Filter button, but you can use the Filter button on the Data tab instead. When you turn on the Filter, it adds arrowed drop-down menus on all sections of the data, which you can use to pull up list items from a certain category or by other criteria.
3. Control Keys
When you normally use the arrow keys in a worksheet on Excel, they move the selection around to different cells. However, this can waste time if you must arrow all the way down a sheet to the right cell you want. To move around faster, hold down the CTRL key, and then hit the arrows. Now, the selection jumps through sections of data quickly.
4. Adjust Column Widths in Excel
When you adjust the width of a column, you can click and drag on the barrier line between two columns. However, the faster way to do this is by double-clicking on that line. The cells in that column will automatically adjust to fit the contents. You can do the same with the rows.
To adjust all the columns and rows at once, click the arrow in the top left corner of the sheet so that everything selected, then double-click in the same spot between two columns. Now, everything will automatically adjust so that you do not have to manually change the widths of every column and row in the worksheet.
5. Flash Fill and AutoFill in Excel
Flash Fill is a feature that comes in handy when you make a list. If you have typed the list before in the spreadsheet, when you begin to type the first word, it will show you a suggestion for a flash fill of the rest of the list. Press ENTER to accept the Flash Fill suggestion.
Auto Fill is when you use the bottom right corner of a cell to drag down a list. You can do this with numbers or letters, even if you start with the beginning of an abnormal number pattern, such as going up by 5 each time.
6. Absolute Cell Reference
An absolute cell reference keeps a referred cell in place when using a formula. What that means is, when you enter a formula using two cells in the equation, you can make one “absolute” to make sure that it does not shift when you copy the formula to multiple cells. You can make either part of the cell reference absolute or the whole thing (row and column). Learn more about this in the Cell References section above.
7. Transpose Tables in Microsoft Excel
When you transpose tables in Excel, you reverse the rows and columns. To transpose a table, select a range, right-click on it, and hit copy. Go to a blank area on the spreadsheet and right-click. Hit Paste Special. A box of options will come up, where you can click the box for “Transpose”. Click OK, and the data will flip itself in the new cells.
8. Text to Columns
When you want to copy a list to a spreadsheet in Excel, this is what you can do to make sure all the elements of the list have a designated spot.
Paste your list in Excel, highlight it, then click the Text to Columns button under the Data tab.
In the Convert Text to Columns Wizard that appears, make sure these settings are selected: Delimited, Tab, Comma (select this according to what is separating your list currently – commas, semicolons, or spaces), General. Then, Finish. The items on the list should now have their places on the sheet.
9. Inserting a Screenshot
Rather than going to another window, hitting the print screen button on your keyboard, then pasting it into the sheet on Excel, you can use the Screenshot tool. On the Insert tab, find the Screenshot button and select the window you want to paste a screenshot from; it will paste itself in your worksheet.
You can also select a smaller area of a window screenshot by using the Screen Clipping button, which is under Screenshot. Click Screen Clipping, then go to the window and use the mouse to click and drag across the screen to make the screenshot. The cursor will look like a small black cross.
10. Show Formulas
To get a better visual of all the current formulas used in the spreadsheet, press Ctrl + ~. Press the keys again to hide the formulas.
How to Use Vlookup
Vlookup is another great tool in Excel that most people do not know. Vlookup helps with large data sets by allowing you to conduct a search.
To set up Vlookup, you will need to type a function in a blank cell off to the side. Type =v and click the VLOOKUP function that pops up. It will tell you the elements you must type next.
You will want to add a lookup value, which you can add by clicking a blank cell that you want to use for conducting a Vlookup search. Then, add a comma and move onto the next element. The table array is going to be a selected range for the Vlookup. Add another comma. The column index number indicates the column that the Vlookup will search through; make sure to count the columns within the range you selected, not the full table on the sheet.
For the last element in the Vlookup function, select either true or false. True is for an approximate match, and false is for an exact match with searches. End the parenthesis at the end of the function and hit ENTER. The cell will say #N/A. Once you type a search in the lookup value spot you identified earlier, it will not show an error.
If the Vlookup function is confusing to you, check out this video tutorial for a visual example:https://www.youtube.com/embed/Lw03WcG4mt4?feature=oembed
How to Create Drop-Down Lists
Adding drop-down lists in an Excel sheet will save you time when you must fill in information on the sheet later down the line.
After you create the start of a table, highlight the cells that you want to have the drop-down lists and click the Data Validation button on the Data tab. A window will pop up that says Data Validation, where you will select “List” under Allow. Then, change the Source based on what you want the options in the drop-down lists to say.
Separate the items under Source by commas, like this:
Once you hit OK, the drop-down menus will be available when you click in one of the cells.
Another way to create a drop-down list:
Follow the same instructions as above, but when you get the Data Validation options box up, use the arrow next to Source to select cells from a table in the spreadsheet. This method makes it easier if you think you will need to add more options to the drop-down list since you can just add it to the table.
How to Create a Pivot Table
To create a custom Pivot Table, click the PivotTable button under the Insert tab. You can either select the range of cells here to use for the table or click the first data cell in the table before hitting the PivotTable button.
Once the Create PivotTable dialog box comes up, make sure the Table/Range is correct and adjust the other settings to what you need.
Press the OK button. On the right side of the window, there will be a PivotTable Fields panel that allows you to select certain items to use and move them to the desired spot. Drag the fields around any of the four boxes at the bottom of the panel to move them from Filters to Columns to Rows to Values.
To quickly see calculations based on the data in the PivotTable, right-click on a cell value and go to Show Values As. There, you can select a calculation, such as % of Grand Total, % of Column Total, % of Row Total, and so on.
You can also use the Recommended PivotTables button for a quick rundown of what you might want to see from the table.
To make a chart based on the data in the PivotTable, you can use the PivotChart tool under the Insert tab. This tool will bring up all the charts you can use.
How to Use 3D Formulas
3D formulas save a lot of time because they allow you to quickly add up totals across multiple sheets in an Excel workbook.
Before setting up a 3D formula, make sure all your sheets have the same setup – the cells have similar placement, and relative data are in the same cell across all sheets.
Start by going to the sheet where the 3D formula will go and click in the appropriate cell. Type the equals sign, and SUM, like this: =SUM
Now, go down to one of the other sheets and click the cell. Hold shift and click on the last sheet name. This makes the same cell from all the sheets show up in the formula, like this:
How to Make a Line Graph in Excel
Select the cells you want to use to make the line graph.
Go up to the Charts tools under the Insert tab and choose a line chart through the Recommended Charts button.
Once you create the line chart, make formatting changes directly on the chart and change the Chart Elements to what looks best for the data. To access the Chart Elements, click the boxed green cross just to the right of the line chart.
For more complex data, you can use a scatter graph. With your data selected, click the Scatter Graph tool in the Insert tab, which is right next to Recommended Charts. This tool will give options for different types of scatter graphs and show a preview for each as you hover over them.
Helpful Video Tutorials: