Top Nav

10 Microsoft Excel 2016 Tips Every User Should Know

Microsoft Outlook, Microsoft Word, and Microsoft Excel are the three heavyweights in the Microsoft Office suite. We recently showed you some of the best features every Microsoft Word user should know and some tips to boost your Outlook productivity. In the same spirit, now we’ll cover the essential Microsoft Excel tips that every user should know.

Microsoft Excel is the industry standard spreadsheet application. Microsoft Excel 2016 is a vast cornucopia of tools that let you manipulate, organize, analyze, and format data in a spreadsheet. Although Excel has been the lifeblood of many a corporate office, research firm, and financial outfit, Excel can be equally as handy and powerful for everyday users. Whether you are a home user managing a household budget, a small business owner managing inventory or a school teacher taking daily attendance, Excel will make your life easier if you learn to use it. Let’s check out those tips.

Essential Tips for Microsoft Excel 2016

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.

MS Excel tips resize-columns-2

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

resize-columns-3 MS Excel tips

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 microsoft excel

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

excel-auto-resize-column-gif-border

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 in excel

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 excel spreadsheet

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

insert-row-or-cell-3 excel

Learn more about deleting blank cells in Microsoft Excel.

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 excel

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

freeze-pane-2

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

excel-tips-freeze-panes-border

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

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

text-alignment-3

Text within the cells will now appear slanted.

text-alignment-4

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

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

protect-cells-1

Click the Format menu, then click Lock Cell.

protect-cells-2

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

protect-cells-3

To protect an entire spreadsheet, check out our article for instructions about applying encryption and passwords to your Excel spreadsheets and Office files.

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

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

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

function-library-2

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

function-library-5

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

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

create-charts-2

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

9 – Reveal Formulas

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

show-forumlas

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

show-formulas-2

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

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

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-1a

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

print-options-2

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-3

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

printing-options-4

So that’s a look at some basics every Excel user should know. If you would like to go on to some advanced topics, check out our previous articles about using features like the VLOOKUP functionpivot tables, adding a watermark to your workbooks or using the built-in calculator. Got a favorite feature or time savers in Excel? Tell us in the comments.

More Reading:

, ,

8 Responses to 10 Microsoft Excel 2016 Tips Every User Should Know

  1. Dave October 10, 2016 at 7:27 am #

    I eagerly looked at this post for new tips on EXCEL and why I should use the 2016 version. Unfortunately there is nothing new at all. I taught EXCEL from 1996 to 2000, and these operations and solutions were available in those early versions of EXCEL. The program is an extremely powerful and versatile one and a few tweaks have come with its age, but the main difference to me is in having to go through a learning curve occasioned by the ‘ribbon’ to learn how to accomplish results that I’ve been using for 20 years successfully with the old menu system. The result is I’m spending much more time in accomplishing advanced features than I did decades ago, using basically the same program. For a new user, I will emphasize the power and versatility of EXCEL, and recommend its use, but for us ‘elder statesmen’ finding little beyond a cosmetic change and the accompanying frustration of having to learn operations that themselves offer nothing new, is aggravating to say the least. This has been true since 2007. I suspect new users being introduced to EXCEL today will in future years be forced to use new versions that will offer little other than adding to Microsoft wealth and market dominance and inconvenience to the experienced user.

  2. Dave October 10, 2016 at 2:54 pm #

    I’ve no argument with EXCEL – I’m a great fan and I consider myself a power user. I just resent having to relearn a presentation that I’m intimately familiar with to be able to use a valuable program that has done everything I ever needed it to do including many ‘advanced’ features and operations.

    I’ve developed EXCEL applications for numerous businesses including insurance brokers, banks, and even a cardiology group at a large hospital. I love the application, just not having to learn the newer presentations. Admittedly a new user will not have to relearn the presentation and should not have too difficult a time in eventually mastering it, but I can’t fathom how anyone could have had undue difficulties with the older menu based presentation. I find that system more intuitive, but that is likely because I mastered it over the years.

    With my ribbon based system, intuition is often out the window. I recall after installing OFFICE 2007, and wanting to insert a new column in a spreadsheet, I naively clicked on the INSERT tab at the top, but of course I couldn’t find any way to insert a column. After a few minutes and finally consulting good old F1, I discovered that I had to go to the HOME tab and find yet another item marked Insert. The HELP system is pretty good and complete but has cost me literally hours of productive time in having to consult it to find stuff I’d been doing for more than ten years with a program that hadn’t materially changed in capability. Many other items I commonly use were hidden from me in non-intuitive nooks and crannies. Even now, I find occasions where seldom used features elude me because of the ubiquitous ribbon.

    Tough to teach an old dog new tricks, especially when the new tricks don’t seem to be an improvement. Then, there’s the same problem with WORD and other OFFICE applications. OUTLOOK is the only one that’s not too far afield from its predecessors. ACCESS has added new useful features, and is not too hard to follow, but on converting from previous versions prior to 2007, one has to do a bit of tweaking. I will consider that the newer OFFICE applications are probably more secure than the older ones, which is a point in their favor.

    I could go on, but I’ll just sound more like an old curmudgeon so I’ll let it go. By the way I look forward each day to these e-mails from Groovy Post. Thanks.

  3. charles brown October 16, 2016 at 6:03 pm #

    With all due respect Dave, if you are not aware the vastly significant improvements and advancements offered by Excel 2016 over Excel 2007, you are not a Power User!!!

    This was an easy intro article for the Excel newbie.

    Charles

  4. Raphael Mizrahi October 19, 2016 at 3:58 am #

    These are useful tips for beginners but I find them a bit too feature-oriented instead of use-case-oriented. Sure, creating charts is great, but which chart would you use for which type of data/message? Special formatting is an amazing tool, but what can you use it for in real-life scenarios: spotting duplicates? Highlighting negative values?

    Also, since the objective is to increase productivity, it could be a good idea to include tips about using and abusing shortcuts and the quick access bar. Or maybe you could weave them into individual tips, like when you describe the “insert / delete row or column” function, you could mention the shortcut (Ctrl + MAJ + +/-).

    Nice post overall, thanks.

  5. Bill November 8, 2016 at 11:47 am #

    Dave, the biggest additions to Excel 2016 is the PowerPivot, PowerReports,3D Maps plus PowerQuery are powerful tools that you would probably like if you tried it.

  6. Holli Robinson November 8, 2016 at 1:04 pm #

    It sure was good to get a quick refresher course of what Excel can do. I kind of knew how to do the majority of them but your explanation was so quick and EASY that I took the time to refresh my mind.

    Thank you so much!

Leave a Reply