Microsoft Excel 2016 makes it easier to work with a large amount of data and give it even more meaning. We’ve included some tips here that might be helpful with using it.
Each Excel workbook contains worksheets. You can put a lot of different data sets in your workbook by accessing your worksheets. You can find them on the tabs at the bottom of the screen.
Excel organizes itself according to the columns (with letter headings) and rows (with numbered headings.) To select either you click on the corresponding letter or number. To select an entire column or row, select the corresponding letter or number.
If you click both a column and row, Excel will create an address for the particular space that intersects the two. This will be highlighted at the top. It’s helpful when creating formulas in Excel. You can quickly find the address of the row or column in the name box. It’s the cell immediately left to the Formula Bar
You can resize all the columns and rows.
Select a row and move the bars to the right or left as you wish. You can do the same with the height of a cell moving the bars up or down.
You can select multiple columns or rows.
Select a column (or row), click and drag to select multiple columns. From here you can increase the size of the column or row.
To quickly see what’s in a group of cells, you can select a range of cells and double-click them.
To undo something hit Control V. Or Control Y to redo.
To change the height or width of cells, select all the cells you want to change and adjust one to a particular height or width, and all of them will adjust.
Shortcut Keys allow you to perform tasks without touching the mouse:
To move your cursor: Use the arrow keys on your keyboard. Or hit Tab to move to the right/Shift+Tab to move to the left. Enter it will go down or Shift+ Enter it will go down.
To get into a cell: Clicking F2 will take you to the F2 cell
To Modify Data in Cells:
Go to the Home tab on the Ribbon to Formatting Tools. Here you can:
- Modify Font Color
- Highlight Cells
- Align Font in a Cell
- Angle the Text
- Merge Cells
To select the entire worksheet: Go to the top of rows and columns on the left, and click on the grey arrow.
Flash Fill This works in the background when you use Excel. It will automatically fill in data that it thinks you want based on your previous patterns working in Excel. Flash Fill is on by default. If it’s not working, you can turn it on in File>Options>Advanced and check the Automatically Flash Fill box.
Auto Fill The AutoFill handle looks like this — + — (with a white plus) and appears when you position the mouse on the lower-right corner of the active cell (green box) the white plus will turn to a black plus. If you drag a cell selection with the white-cross mouse pointer rather than the AutoFill handle, Excel simply extends the cell selection to those cells you drag through. If you drag a cell selection with the arrowhead pointer, Excel moves the cell selection. Also, look for the blue-headed arrow in the bottom right corner of the cells, and you can click to see Formatting or Auto Fill options.
To correct data in a cell: click in the cell or on the formula bar. If you have hyperlink (it will be blue text) in a cell to correct use the formula bar, or you’ll open the hyperlink.
To modify formatting for numbers:
- Select the cell or range of cells that you want to format.
- On the Home tab, in the Number group, click More Number Formats at the bottom of the Number Format list.
- In the Format Cells dialog box, under Category, click Custom.
To Format numbers as currency you can display a number with the default currency symbol by selecting the cell or range of cells, and then clicking Accounting Number Format in the Number group on the Home tab. (If you want to apply the Currency format instead, select the cells, and press Ctrl+Shift+$.)
To display numbers as phone numbers Excel provides a special number format that lets you format a number as a phone number. For example, you can format a 10-digit number, such as 5555551234, as (555) 555-1234. Select the cell or range of cells that you want to format.
To total numbers use the Auto Sum Function. Select the cell to the right or below the numbers you want to add. Click the Home tab, and then click AutoSum in the Editing group. AutoSum adds up the numbers and shows the result in the cell you selected. You can also hit ALT + = after clicking on all the cells you want to add.
To Sort Cells:
- Select the cell range you want to sort.
- Select the Data tab on the Ribbon, then click the Sort command.
- The Sort Dialog box will appear.
- Decide the sorting order (either ascending or descending).
- Once you’re satisfied with your selection, click OK.
- The cell range will be sorted by the selected column.
Formulas are equations that perform calculations on values in your sheet. All formulas begin with an equal sign (=). You can create a simple formula by using constant and calculation operator. For example, the formula =5+2*3, multiplies two numbers and then adds a number to the result.
When you want to refer to variables instead of constants, you can use cell values, for example, =A1+A2.
If you’re working with long columns of data, or data that’s located in different parts of a sheet (or on another sheet), you can use a range —For example: =SUM(A1:A100)/SUM(B1:B100), which represents the division of the sum of the first hundred numbers in column A by the sum of those numbers in column B.
When your formula refers to other cells, any time that you change the data in any of the cells Excel recalculates automatically.
You can also create a formula by using a function, a predefined formula that simplifies entering calculations.