Jump to content
Business Intelligence & Analytics Community

Rishav Pandey

10 Microsoft Excel 2016 Tips Every User Should Know

Recommended Posts

1. Resize Columns and Rows

The Excel default cell height and width is hardly one size fits all. Chances are, you’ll need to adjust the column width and row height to accommodate your data. To do that, click the column or row, select the Home tab, then click the Format button within the Cells group. Choose whether you want to adjust the height or width.

Resize-columns-2.png.a4d3de7543aca41d364020e3b68cde10.png

Enter the amount then click OK. The column or row should be adjusted to the exact measurement.

Resize-columns-3.png.29f9f6a1019fc1924731517a751ed59d.png

You can also manually resize columns and rows using the mouse. Place the mouse pointer between the column or row, click the left mouse button, observe the floating balloon then drag and expand until the desired size is achieved.

Resize-columns-4.png.077c545bb53d3e9f9425211851ce59d0.png

And here’s a handy tip: simply double-click the right border of a column to auto-size the width to the data.

58b93f72b4a01_resizecolumn5.gif.0c6016c305b9a5a5a20e7649b0baf8bd.gif

 

Edited by Rishav Pandey

Share this post


Link to post
Share on other sites

2. Add or Remove Columns, Rows or Cells

If you need an additional column, row, or cell, you can easily insert it using the Insert and Delete Cells commands. Click the Insert button within the Cells group, then choose the appropriate option.

Insert-Row-or-Cell.png.8cc577e5912b2879b63ff8fc87994c02.png

You can also delete a column from within the same group; click the Delete menu, then choose the appropriate action.

Insert-Row-or-Cell-2.png.dc67e79fea98d6b0e6e7754ffe0a33d0.png

The same action can be performed by right-clicking on the column or cell row.

Insert-Row-or-Cell-3.png.473156562ab572a05163782e88a87093.png

 

Share this post


Link to post
Share on other sites

3. Freeze Panes

If you want 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. Select the row or column where the data begins in the sheet.

Freeze-pane-1.png.d95f2193fee618dd01281ad9802a2469.png

Select the View tab, click the Freeze Panes menu then click Freeze Panes.

Freeze-pane-2.png.94ec5302dc089ebf2c279a83d5b0647c.png

When you scroll, your headings or columns will remain visible.

excel-tips-freeze-panes-border.gif.8e46dd9e3ee753403e220eb86ef0083c.gif

Share this post


Link to post
Share on other sites

4. Change Text Alignment in Cells

If you need to create a register or labels, you can use the Format Cells dialog to adjust the alignment of text within cells. Select the cells where you would like to apply the formatting, right click on the selection then click Format Cells….

Text-alignment-1.png.cc627337a6b4c16d83316f3740ca2b81.png

Click the Alignment tab, then use the mouse to change the orientation of the text or enter a value. When satisfied, click OK.

Text-alignment-2.png.b401a20c68ed4dff9e78c87107b2767b.png

Text-alignment-3.png.a0348b9bdc76c20fc3d489a8d3830b21.png

Text within the cells will now appear slanted.

Text-alignment-4.png.fc37fcec4a4fdba6280ea6d4de1cec57.png

Share this post


Link to post
Share on other sites

5. Use Cell Protection to Prevent Editing an Area of the Spreadsheet

If you share a workbook with other users, it’s important to prevent accidental edits. There are multiple ways you can protect a sheet, but if you just want to protect a group of cells, here is how you do it. First, you need to turn on Protect Sheet. Click the Format menu then click Protect Sheet. Choose the type of modifications you want to prevent other users from making. Enter your password, click OK then click OK to confirm.

Protect-Cells-1a.png.9db2f5c7d49d1e39caea282fee54164a.png

Make a selection of the rows or columns you want to prevent other users from editing.

Protect-Cells-2.png.64c04dd0748150fd424d261b25da76e5.png

Click the Format menu, then click Lock Cell.

Protect-Cells-3.png.3c558ad3e7dc2c1b9abb952b04f59edb.png

Anytime a user tries to make edits; they will receive the following error message.

Protect-Cells-4.png.fc18e8cea816643bf9b71e277c9b593f.png

Share this post


Link to post
Share on other sites

6. Apply Special Formatting to Numbers and Currency in Cells

If you need to apply a specific currency value or determine the decimal place for numbers in your spreadsheet, you can use the Numbers tab within the Formal Cells dialog to do so. Select the numbers you would like to format, right click the selection then select the Numbers tab. Select Currency in the Category list, then choose the number of decimal places and currency format.

Format-number-value-spreadsheet.png.e77ba9f436ca9345f1d80ed1c71ad49e.png

Share this post


Link to post
Share on other sites

7. 5 Essential Excel Functions You Should Know – Sum, Average, Max, Min, Count

Excel’s vast true power lies in its functions and formulas. Basic functions let you do quick math operations, while advanced functions let you crunch some serious numbers and perform complex analysis. Just like everyone should know the formatting ropes in Word, you should also know the most popular functions in Excel.

Sum – calculates the total of a range of cells.

Average – calculates the average of a range of cells.

Max – calculates the maximum value in a range of cells.

Min – calculates the minimum value of a range of cells.

Count – calculates the number of values in a range of cells, avoiding empty or cells without numeric data.

Here is how you use a function. Enter the labels for the numbers you would like to produce the calculation for. Select the Function tab, then choose the category of function you would like to apply. Click Insert Function button within the Function Library group or press Shift + F3 on your keyboard. Select the function you need or use the Search for function feature then click OK.

Function-Library-1.png.46c45c317d35404193963537acd9f624.png

Once you’ve found the function, select it then click OK.

Function-Library-2.png.11193c9c8b9994eaa63a997667ac2390.png

Make any appropriate modifications to the range you are calculating then click OK to apply the function.

58b94297894d8_Function-Library3.png.9336b7a109fc69eb19b68a7ce03a85e9.png

Share this post


Link to post
Share on other sites

8. Create and Manipulate Charts

A hallmark feature of Microsoft Excel, creating charts allows you to visually present your well-formed data. Excel makes the process very easy; highlight a range of data in your sheet, select the Insert tab, then click the See all charts button.

create-charts-1.png.50798046a2479775a22fc79fce60de83.png

Click the All charts tab, then browse the through the list of chart styles.

create-charts-2.png.a1862ac1268bc8f6f5e096a53f92c1dc.png

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 then click OK.

create-charts-3.png.8ea888be24c1aae0ff198db4b11a6bc3.png

Edited by Rishav Pandey

Share this post


Link to post
Share on other sites

9. Reveal Formulas

If you want to validate the calculations in your workbook, revealing your formulas is the way to do it.

58b943ec0585a_Show-forumlas-1.png.41a3546bc5a1f16a81eec04d9aa49615.png

Select the Formulas tab, then click Show Formulas located in the Formula Auditing group.

Show-formulas-2.png.3a22a1e669f256055c9b3d26a7953068.png

Now you can easily check through formulas used in your sheet and also print them. It’s a great way to find errors or to simply understand where the numbers come from.

show-formulas-3.png.4f320ecfe6c6091b7fb7871aaad12c85.png

Share this post


Link to post
Share on other sites

10. Maximize Printing Options when Printing Large Workbooks

Spreadsheets work great on large widescreen monitors, but sometimes you might need to print out your workbook. If you are not careful, you can end up wasting a lot of paper on something mostly unreadable. Excel takes care of this using the Backstage printing options, which let you adjust the page size and orientation. Spreadsheets are best printed on legal size paper using landscape orientation.

printing-options-1.png.81e35c32c4d1267563ad674a7073c490.png

If you need to adjust margins to fit additional information on a single sheet when printing, click the Show Margins button in the right-hand corner of the backstage print tab.

printing-options-2.png.a6a1c0c187e725c989071416c54c7adc.png

You can then use the margins to adjust the columns to fit any data might spill over to another page.

print-options-3.png.da62dfb00ad846e572c5f23695c41412.png

If you can’t get all the data on one page, use the Page Setup dialog to make further adjustments. The scaling menu can help you reduce the size of text to help it fit better. Try not to scale too much, since you want to keep text legible

printing-options-4.png.3906a5bda2b2ac87676d0266090f6c5a.png

You can also use the same dialog to dress up your spreadsheet with a header and footer if desired.

printing-options-5.png.3f0768c97f6b7f7bee53428a5f880b64.png

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now





×