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.
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!
Microsoft Excel: Tips & Tricks
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.
Similarly, you can use the Delete option and choose the appropriate icon to delete a cell, row or column.
To make things easier, you can also right click on any row or column to add/delete a cell.
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.
Then, you need to enter the number you’d like the cell to be and click ok.
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.
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!
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.
Then choose the Alignment tab, change the text orientation according to your choice and hit ok.
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.
Choose all the desired options including the password to protect the sheet and click ok.
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.
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.
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.
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.
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.
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:
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.
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.
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.
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.
You can additionally also dress your printing page with headers and footers using the same dialogue if desired.
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?
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.
