Useful Microsoft Excel 2017 tips and guides
Fill Multiple Cells
- Select the cells that the formula will be inserted in
- Type or insert the formula or text in the active cell.
- Hold the Cmd key and press Return (windows: Ctrl key and press Enter).
- The text or formula will be copied to all the selected cells.
Add More Than One New Row or Column
To add multiple rows or columns in a spreadsheet, highlight the same number of preexisting rows or columns that you want to add. Then, right-click and select Insert.To make repetitive text
To make repetitive things like dates (1/1/17, 1/2/17, 1/3/17, and so on). Move the cursor on the screen to the lower right part of the last cell. When it turns into a plus sign (+), click and drag down to select all the cells you need to fill.Resize Columns and Rows
click the column or row, select the Home tab, then click the Format button within the Cells group. Choose anything you want to adjust the height or width. Enter the amount and click OK. The column or row should be adjusted to the exact measurement.Apply diagonal borders
If you’ve got a table that needs labels for both the row and column headers in the same cell, use diagonal borders. Click More Borders at the bottom of the borders drop-down menu (on the Home tab of the ribbon), and the diagonal buttons are by the box corners.Use a shortcut to create a table
If you've been creating tables from your data by going to the Excel ribbon, clicking Insert and then Table, there's an easy keyboard shortcut: After first selecting all your data with Ctrl-A (command-shift-spacebar for Mac), turn it into a table with Ctrl-T (command-T on Mac).Quickly add up data
Quickly add up an entire column or row of data by clicking in the first empty adjacent cell and pressing ALT and = (equal key). Excel will then automatically sum all of the numbers it can find in that row or column.Calculating in Excel
To calculate a series of cells you can do simple commands. If you have a column for revenue and one for costs, you can calculate profits by selecting the cell you want this to appear in and typing = (equal key) followed by the number for the revenue-total cell, the minus sign, and then the number for the costs-total cell. Press enter and Excel does the calculation for you. If you used the SUM formula above to calculate those totals, then any number which changes in the revenue or costs columns will make Excel recalculate the profit total.Manually enter Excel formulas
=SUM(B4,B5,B6,B7); =SUM(B4+B5+B6+B7). Or, place your cursor in the first empty cell at the bottom of your list (or any cell, really) and press the plus sign, then click B4; press the plus sign again and click B5; and so on to the end; then press Enter. Excel adds/totals this list you just “pointed to:” =+B4+B5+B6+B7.Adding Filters in Excel 2017
Add a filter by clicking the Data tab and selecting "Filter." Clicking the arrow next to the column headers and you'll be able to choose whether you want your data to be organizing in ascending or descending order, as well as which specific rows you want to show.Copy and paste the values in the spreadsheet when a Filter is on to do additional analysis in another spreadsheet.
Excel 2017 Apply & Clear Table Formatting
If your range already has some formatting in the header row, then sometimes your Table can look not so pretty after creating it. The Table formatting does not get fully applied to the header row for some reason.Tips to fix it:
Select a cell inside the Table » Go to the Design tab in the Ribbon » Right-click one of the Table styles in the Styles Gallery » Choose Apply & Clear Formatting » This will clear all the existing formatting in the range and apply the Table style.
Paste data with a different orientation
You want rows to be columns. Or vice versa. It would be hard and tedious moving things cell-by-cell. You can copy that data, select Paste Special, check the Transpose box, and paste with a different orientation.What is Freeze Panes and How to do it
To scroll through a spreadsheet without losing focus on a particular part of the sheet or data, the Freeze Panes function is the perfect way to do it. When you scroll, your headings or columns will remain visible.Select the row or column where the data begins in the sheet » Select the View tab » click the Freeze Panes menu » click Freeze Panes.
Hiding Cells in Microsoft Excel
Excel trick for hiding cells.With the cells selected » right-click » choose Format Cells » set the format as Custom under the Number tab » Enter ;;; (three semicolons) as the format.
The cell contents disappear but they’re still there and can be used in formulas.
How to see quickly which cells contain a formula
To see quickly which cells contain a formula, or would prefer all the formulas on a page to be visible, just press CRTL/CMD + ` (acute accent key, beside the “1” on most keyboards). You can toggle back to the values view by pressing the same keys again.Add a summary row to a table
add a summary row to a table in the Design ribbon on Windows or the Table ribbon on a Mac by checking "Total Row". You will need to create a total row for each column individually; creating a sum for one column won't automatically generate sums for the rest of your tableCompiling data in Excel 2017
When compiling data on customers, you may record the date they joined you or the date they purchased your product. When you enter a date into Excel, it automatically understands you are using dates and formats the column accordingly. However, you can alter the way a column is formatted by right clicking on the column and selecting “Format cells”. You can also change the way these dates are sorted by clicking on Sort & Filter in the top right of your screen.Use Recently Used button
Click the Recently Used button to show functions you've used recently. It's a timesaver, especially when dealing with a big spreadsheet.=AVERAGE(B4:B13) adds the list, divides by the number of values, then provides the average.
How to remove duplicates in Excel 2017
To remove duplicates, highlight the row or column that you want to remove duplicates of. Then, go to the Data tab, and select "Remove Duplicates" (under Tools). A pop-up will appear to confirm which data you want to work with. Select "Remove Duplicates," and you're good to go. it can also uses to remove an entire row based on a duplicate column value.Paste over existing data
Have you ever pasted some data over existing data, then wondered if the new data is long enough or wide enough to paste over the existing data?Then, the Ctrl + . (period) keyboard shortcut will save you from scrolling all the way down the sheet.
Pressing Ctrl+. (hold the Ctrl key and press the period key) will select the next corner of the selected range. After pasting a range of data, press Ctrl+. to select the top-right cell of the selected range. Then press Ctrl+. again to select the bottom-right cell.
This will get you down to the bottom of the pasted range where you can quickly see if you pasted over the existing data. it can also use to jump down to the bottom of a single column.
Fill multiple cells with same data
click the entire set of cells, either by dragging your cursor, or by holding the Ctrl key as you click each one. Type it on the last cell, then hit Ctrl+Enter—and what you typed goes into each cell selected.Commenting on formulas
Add a space then +N(“your comment here”) to leave comments. Comments don’t appear in the cell but do show up in the Formula bar, and they’re searchable too.Combine data from two or more cells into one cell
If you want to combine first and last name into one. In a third cell type =(. Then click the cell that contains the first text you wish to combine and type &” “& (a space enclosed in quotation marks). Click the next cell with the text that you want to combine. Hit enter, and you’re set.Filter table data with slicers in Excel 2017
Excel tables offer drop-down arrows next to each column header for easy sorting, searching and filtering. However, trying to filter data with that small drop-down when you've got a large number of items can be somewhat cumbersome. Several of the presenters at the Data Insights Summit suggest using slicers instead.VLOOKUP formulas
If you have a large table with a number of different columns and you know one piece of data (“datum” – to save you the effort of reminding me in the comments) and you want to find out about another bit of information related to that data, you can use the VLOOKUP.A formula to calculate the number of days between two dates
formula to calculate the number of days between two datesExample:
End Date October 12, 2015 minus Start Date March 31, 2015 = 195 days
You can use
=DAYS(A30,A29)
How to select visible cells without hidden cells in Excel 2017
select the visible cells. The keyboard shortcut to select visible cells is Alt+; (semicolon) and for Mac Cmd+Shift+Z. Press this shortcut key after selecting the range, to only select the visible cells.Create and Manipulate Charts in Microsoft Excel 2017
Microsoft Excel has hallmark feature, creating charts allows you to visually present your well-formed data.Highlight a range of data in your sheet » select the Insert tab » click the See all charts button » Click the All charts tab » Browse the through the list of chart styles.
You can also hover over a sample to see a preview of what the chart will look like. Once satisfied, click OK to insert the chart into the spreadsheet. If you would prefer to keep it in a separate sheet » select the chart » click Move Chart » select New Sheet » click OK.
