Top 11 Excel 2016 tips and tricks that you should know! - KING OF EXCEL

Monday, January 13, 2020

Top 11 Excel 2016 tips and tricks that you should know!

Microsoft Excel, Microsoft Word, and Microsoft Outlook are the heavyweights in the suite of Microsoft Office. It is no secret that Microsoft Office and especially Excel is a boon to all entrepreneurs and data entry professionals whose sole job is to keep the data and information stored in an organized manner online. It is a standard spreadsheet tool that is needed in most industries these days.

11 Excel 2016 Tips - And Tricks - Dynamic Web Training
Click image to enlarge 
Excel 2016 is a giant of tools that enable a user to analyze, organize, manipulate and format a set of data into a spreadsheet. It is the lifeblood of all corporate houses, financial outfits, and research firms but its needs are not just limited to those users. People have started using Excel in their daily lives too whether it is for managing a household budget, putting in your students’ attendance or maintaining an inventory.
Excel 2016 is full of tips and tricks that will surprise you and assist you in making your life easier. Let’s check out the top 11 Excel 2016 tips and tricks that you should know!


1. Add or Remove Cells, Rows or Columns

Inserting or deleting cells, rows or columns in an Excel is easy with the commands “Insert Cells” and “Delete Cells.” You have to go to the Insert button and select the option you desire.

Excel 2016 Tips - Add Remove Cells 1 - Dynamic Web Training
Click image to enlarge 
Similarly, you can use the Delete option and choose the appropriate icon to delete a cell, row or column.

Excel 2016 Tips - Add Remove Cells 2 - Dynamic Web Training
Click image to enlarge 

To make things easier, you can also right click on any row or column to add/delete a cell.
Excel 2016 Tips - Add Remove Cells 3 - Dynamic Web Training
Click image to enlarge 


2. Resize Rows and Columns

The size of Excel’s rows and columns is pretty standard, so there’s a fair chance that you will have to adjust their size to accommodate all the data that you need to put in there.
It is relatively simple to change the width or height of a cell by going to the Home icon, clicking on Format and choosing Column Width as the option.

Excel 2016 Tips - Add Resize Cells 1 - Dynamic Web Training
Click image to enlarge 

Then, you need to enter the number you’d like the cell to be and click ok.

Excel 2016 Tips - Add Resize Cells 2 - Dynamic Web Training
Click image to enlarge 

It is also straightforward to manually adjust the rows and columns by placing the pointer anywhere between a row and a column and dragging and expanding the row/column till you reach your desired size.

Excel 2016 Tips - Add Resize Cells 3 - Dynamic Web Training
Click image to enlarge 

3. Freeze Panes

The Freeze Panes function comes in quite handy when you want to scroll through a part of the spreadsheet without losing your focus on a particular part of the sheet.
To Freeze Panes, select the column or the row where the data commences, select the View option and click on the menu that says Freeze Panes to do the job!

Excel 2016 Tips - Freeze Panes - Dynamic Web Training
Click image to enlarge 

4. Change the Alignment of Text in the Cells

It is easy to use the Format Cells dialogue in the Excel if you wish to create labels or a register so that you can adjust the text alignment in a cell. To apply the formatting, right click on the selected row/column and hit Format Cells.

Excel 2016 Tips - Change Alignement 1 - Dynamic Web Training
Click image to enlarge 

Then choose the Alignment tab, change the text orientation according to your choice and hit ok.

Excel 2016 Tips - Change Alignement 1 - Dynamic Web Training
Click image to enlarge 

5. Cell Prevention to Prevent Someone from Editing a Particular Part of the Sheet

The Cell Prevention feature can prove to be quite beneficial if you wish to prevent someone from editing a specific part of the Excel. It dramatically limits accidental editing and saves you from a lot of extra work.
Protecting a group of cells is easy. Just turn on the feature of Protect Sheet by going on the Format menu.

Excel 2016 Tips - Prevent Editing 1 - Dynamic Web Training
Click image to enlarge 

Choose all the desired options including the password to protect the sheet and click ok.

Excel 2016 Tips - Prevent Editing 2 - Dynamic Web Training
Click image to enlarge 

You can also lock a cell if you wish to prevent someone from editing a bunch of rows or columns. To do this, select the row and columns that you want to keep pristine and hit Format → Lock Cell. And, that is it.

Excel 2016 Tips - Prevent Editing 3 - Dynamic Web Training
Click image to enlarge 

6. Apply Special Formatting in Cells to Currencies and Numbers

If you wish to insert a decimal or a particular currency in any of the cells, rows or columns, you can do that in a jiffy.
Go to Format Cells by right-clicking on the selected column/row and click on Numbers to make the desired changes to the numbers or the currency of a cell.

Excel 2016 Tips - Number Formatting - Dynamic Web Training
Click image to enlarge 

7. Sum, Average, Count, Max, Min – 5 Functions You Cannot Do Without in Excel

The one real power of Excel undoubtedly lies in its formulas and functions. If you do not know how to apply formulas in Excel, you know maybe 40% of the functions of Excel. Hence, it is pertinent to identify the five essential functions of Excel, i.e., Sum, Count, Average, Max, and Min. Let’s check out what these functions do and how to apply them!
  • Sum – Calculates the total sum of the selected cells
  • Count – Counts the number of values in the selected cells leaving empty cells or those that have no numeric data
  • Average – Calculates the average of the selected cells
  • Min – Demonstrates the minimum value of the selected cells
  • Max – Demonstrates the maximum value of the selected cells
To use a function, go to Home, click on AutoSum, go to the drop-down menu and use the appropriate function you wish to apply to a group of cells. Before doing that, do not forget to select the group of cells you want to implement the function to.

Excel 2016 Tips - Functions - Dynamic Web Training
Click image to enlarge 

8. Manipulate and Create Charts

Creating and manipulating charts is probably the hallmark feature of Excel. It enables the users to present their data in a visually well-formed manner. Excel helps significantly in easing the process.
To do this, highlight the part of the spreadsheet you wish to manipulate, click on the Insert tab and then choose the All Charts option from the Recommended Charts icon.

Excel 2016 Tips - Create Chart 1 - Dynamic Web Training
Click image to enlarge 

You can even see a preview by hovering over a sample to check out the final result of the chart. Once you are satisfied, click ok to insert the chosen chart into the spreadsheet.

Excel 2016 Tips - Create Chart 2 - Dynamic Web Training
Click image to enlarge 

9. Reveal Formulas

Revealing all your formulas is probably the best way to validate all the calculations in your workbook. For example: A data shown below:

Excel 2016 Tips - Reveal Formula 1 - Dynamic Web Training
Click image to enlarge 

To get to the equations, click on the Formulas tab and then click on Show Formulas, You will find it in the group of Formula Auditing.

Excel 2016 Tips - Reveal Formula 2 - Dynamic Web Training
Click image to enlarge 

Excel 2016 Tips - Reveal Formula 3 - Dynamic Web Training
Click image to enlarge 

Now, it is super easy to verify all your formulas and even print them if you wish. This way, you can easily find all the errors in your formulas to understand how the numbers are what they are.

10. Maximize the Printing Options When You Are Printing Large Workbooks

Large and widescreen monitors are ideal for working on spreadsheets. However, sometimes, you naturally need to take a print out of your workbook. You need to be especially careful in this case, or you can end up wasting a considerable amount of paper by printing something that will mostly be unreadable and illegible.
It is where; Excel comes into the picture by allowing the users to adjust the orientation and size of the page by utilizing the Backstage printing options. It is recommended to print spreadsheets on legal sized papers and by using the landscape orientation so that every word on the spreadsheet/print out is legible and you do not waste any extra or unnecessary paper.

Excel 2016 Tips - Printing 1 - Dynamic Web Training
Click image to enlarge 

You can also adjust the margins if you wish to fit in any additional details or information in a single sheet. To do that, you can click on the Show Margins tab that is present on the right-hand side of the tab that says backstage print.

Excel 2016 Tips - Printing 2 - Dynamic Web Training
Click image to enlarge 

Then, you can adjust the margins according to your preferences to accommodate any spillover data so that it fits all in a single page.
If you are finding it challenging to provide everything on a single page, you can also go to Page Setup at the bottom of the page to make additional adjustments. You can reduce the text size or use the scaling menu to make further changes so that the text is neither too small nor illegible.

Excel 2016 Tips - Printing 3 - Dynamic Web Training
Click image to enlarge 

You can additionally also dress your printing page with headers and footers using the same dialogue if desired.

Excel 2016 Tips - Printing 4 - Dynamic Web Training
Click image to enlarge 

11. Select All with One Click

We saved the best for the last! For the beginners who are relatively new to the world of Excel, this will be a lifesaver!
Everyone probably knows that you can select all the text on a spreadsheet by hitting the Ctrl + A shortcut at the same time! But did you know that clicking the corner button, as shown in the screenshot, can select all the data you’ve entered within seconds?

Excel 2016 Tips - Select All - Dynamic Web Training
Click image to enlarge 


Bottom Line

Microsoft Excel 2016 is a treasure chest of numerous other tricks and tips that are bound to surprise seasoned users and substantially assist the beginners in knowing how to use this handy tool.
Furthermore, you can also use the Excel app on your smart phones for easy access to all your spreadsheets in the Drive.
#evba #etipfree #kingexcel
📤You download App EVBA.info installed directly on the latest phone here : https://www.evba.info/p/app-evbainfo-setting-for-your-phone.html?m=1

No comments:

Post a Comment