12 Microsoft Excel 2016 Tips Every User Should Know

Posted on - Last Modified on

Many people encounter difficulties when using Microsoft Excel. Mastering the various functions and formulas can be hard work. Many technology engineers have come up with some useful tips to let beginners and experts alike comprehend the operations of this program.

If you’re looking for a Microsoft Excel 2016 expert, there are hundreds of professionals waiting for you at freelancer.com.

Excel 2016 is a relatively new program. Having an understanding of how it works and what the different formulas can do will enhance your reporting and calculation capabilities, hasten data entry and generally make your work - and life - easier.

Below are our 12 Excel 2016 tips and tricks to help both beginners and experts work more efficiently.

1. Adjust the size of rows and columns

Every column and row in Excel 2016 has a default width and height, but users can easily adjust rows and columns to their preferred size. To achieve this:

  • Click on any column or row.

  • Go to the Home tab and navigate to the Format button.

  • Select what you would like to do from the options provided. You can modify the cell width, height or both.

  • Type in the required size in numbers and click OK.

  • Changes take effect immediately.

You can also modify row and column sizes manually with your mouse. Place your pointer at the edge of a row or column number, left click and drag to the desired size. Additionally, you can simply right click a row or column number and choose Format Cells. Adjust the height or width as desired.

2. Remove or add rows, columns or cells 

To add a cell, column or row, use the Insert and Delete Cells option. Right click within the cells and select the Insert option. Choose what to insert from the provided options. If you want to get rid of some unnecessary cells, right click within the cells but choose Delete Cell, and select what you want to delete from the provided options.

Another option would be to click on the Insert button from the Home tab.

3. Freeze panes

Excel spreadsheets can be long and seemingly endless. This can make scrolling to the desired data a tiresome activity. However, with the Freeze Panes option, you can make life a lot easier. Highlight the column or row by clicking the relevant number at the edge of the spreadsheet. Select and click Freeze Panes from the View option.

4. Modify the alignment of text in cells

To alter the orientation of the text in cells, click within the desired cell(s) and navigate to the Format Cells option from the Home tab. Click on the Alignment option, and select your preferred orientation. You can choose either horizontal or vertical alignment, as well as type in the value of your desired alignment in numbers. Once done, click OK to save the changes.

5. Protecting cells in a spreadsheet

It is important to protect spreadsheets, especially for individuals who share computers and documents. Protecting spreadsheets prevents other people from editing the work. Protecting an entire spreadsheet is simple, but when you need to protect specific cells, follow these steps:

  • Go to the Format option, navigate to Protect Sheet and click to activate it.

  • Select from the provided options what to protect from interference.

  • Type in a password.

  • Click OK and confirm by clicking OK once more.

To protect columns or rows:

  • Choose which rows, columns or cells you would like to protect from editing.

  • Access Lock Cell from the Format option and click.

  • An error message will prompt any user who tries to open the protected columns or rows.  

6. Special formatting of currency and numbers

To change decimal place settings or assign a particular value of currency within a spreadsheet, navigate to the Numbers option from the Format Cells menu. Choose the numbers to format and right click. Click on the Numbers option and choose Currency from the Category menu. Select the desired currency format and decimal places in numbers. 

7. Vital Excel functions to master - Average, Sum, Max, Count and Min

Many Excel users attribute its popularity to the wide range of formulas and functions which can make their work easier. This program is comprised of both basic and advanced formulas. While basic formulas let you easily calculate mathematical problems, advanced formulas enable you to solve more complex exercises. Below are some of the most commonly used Excel functions:

  • Average: Finds the average within cells.

  • Sum: Finds the total within cells.

  • Min: Finds the minimum amount within cells.

  • Max: Finds the maximum amount within cells.

  • Count: Finds the amount only within cells that have numerical data.

To apply a certain function:

  • Type cell labels for the numbers to be calculated.

  • Click on the Function option and select the function category to apply.

  • Click on the Insert Function option. You can also access it by pressing Shift and F3, or by clicking the Function Library option. You can either search for a function attribute or choose it from the menu. Apply all the necessary adjustments and click OK.

8. Build and manage charts

Building charts lets users present adequately organized data in a visual manner. It is easy to achieve this in Excel. Click to highlight a section of data within the spreadsheet. Choose the Insert option, and click on See All Charts. Click the All Charts button and pick the ideal chart style for your figures from the selection.

You can use the preview function to see how the final product will look before you make your final decision. After choosing a chart, click OK to add it into the spreadsheet. To store the chart on a separate sheet, right click on the chart, choose Move Chart, select New Sheet and click OK.

9. Show formulas

Displaying formulas enables you to verify calculations within the workbook. Click on the Formulas option, choose Show Formulas from the Formula Auditing menu. Then you can locate all of the formulas used in the workbook, and print them if you want to. This allows users to identify errors more easily.

10. Printing large Excel spreadsheets

Navigating through Excel spreadsheets can be a cumbersome activity, especially when you need to print them. In fact, many Excel users have difficulties when it comes to printing readable material. However, Excel 2016’s printing options enable individuals to modify a page’s orientation and size. To achieve the best print quality, choose the landscape legal paper size orientation.

To fit extra information on a paper, modify the margins by clicking the Show Margin option. Adjust the columns by modifying the margins to ensure all data fits adequately. Make additional changes using the Page Setup option. Use the Scaling option to reduce text size for a better fit. Avoid excessive scaling as this can make the data harder to read. Users can include headers and footers to spreadsheets by accessing them from the Page Setup menu.

11. Auto filtering

Excel comes with an inbuilt filtering option for particular columns. This means you can choose to filter certain types of data shown on a column. This option lets users find text comprising of a particular number or character. To filter:

  • Click the dropdown menu at the top of the column.

  • The appearance of options depends on the contents of the column.

  • Select the desired filter from the available options.

  • The Custom Auto Filter window will appear. Choose the options according to preference and click ok.

12. Decrease or increase values

You can use the Paste Special menu to permanently decrease or increase values. In order to achieve this:

  • Enter a random number in any blank cell.

  • Copy the value using the mouse or do it manually by pressing CTRL and C.

  • Right click on a new cell and click Paste Special. Choose the Multiply option and click OK. This increases the value by 25%.

Finally:

Excel 2016 is the most advanced version yet. Understanding the changes in basic formulas and functions will allow you to use it with minimal struggle.

Do you have extra tips you would like to share with us? We would be glad to read them. Let us know by commenting below, and please share this article with your friends!

Posted 20 September, 2017

LucyKarinsky

Software Developer

Lucy is the Development & Programming Correspondent for Freelancer.com. She is currently based in Sydney.

Next Article

The Vital Guide To Using Python And Excel For Data Science