Excel 2016: 10 Useful Things For Beginners to Know

These are ten sections of Excel 2016 you should know when making a spreadsheet, whether you are keeping track of finances or creating detailed data charts.

As part of Microsoft’s four core applications, Excel 2016 is an effective tool for keeping complex information in order and for making intensive calculations. The newest iteration of the program has added some new features and moved classic ones, so it is important to know your way around Excel to be as productive as possible.

For those already familiar with Excel 2016, this list will be an excellent refresher. For those who are wanting to learn the application better, below is a list of the ten things you NEED to know to make effective use of the features that Excel 2016 has to offer:

1. Open a New/Existing Workbook

Open a new workbook: To open a new Excel 2016 workbook from an existing workbook window, select File to open the menu; the default selection is always Open:

Select New in the green column, and then choose a workbook option from the list of examples (blank workbook, academic calendar, inventory list, etc.). Typically, Blank workbook will be your go-to default style of a workbook for school, work, or other data tracking tasks. However, choosing an appropriate theme can lessen the time you spend on customizing the look of your workbook sheets. Select your preferred workbook style to open a new workbook to use.

Open an existing workbook: To open an existing workbook, select the File menu pictured above and remain in the Open section within the blue column. Depending on where you have previously saved a workbook, you will be able to search the following listed options to find it:

• Recent– If you have recently opened or worked in your desired workbook, you will see it displayed in this section. If the workbook is visible, select it to open the workbook.
• Shared with Me – If someone has shared a workbook with you to edit, view, or comment on, you be able to select it here. To view shared workbooks, you must sign into your Microsoft Office account.
• OneDrive– This option links to your Office OneDrive account, a cloud-based storage system in which most Office accounts are granted one terabyte of storage space for personal documents, workbooks, and more. You must be signed into your OneDrive account to access workbooks here.
• This PC– This option links to the local storage on your device where you can search through saved files to find your desired workbook. Excel 2016 defaults to your Documents folder, but you can search for other files by just selecting the arrow pointing up next to the word “Documents” to go back to your whole list of local files.
• Add a Place– If you have not saved your workbook recently on your local hard drive, you may add a different place from which to search for your workbook; this includes Office 365 SharePoint, OneDrive, an external hard drive, or another source you choose to designate.
• Browse– This option is like ‘This PC,’ but will instead open a new window and display the same file information.

If you ever need to return to your current workbook, you can select the arrow in the upper left corner of the File menu that points to the left.

2. Saving a Workbook

The fastest and easiest way to save a document Word 2016 is to select the floppy disk icon in the upper left corner of the screen:

The secondary way to save your workbook is by selecting File and then select Save As in the green column. This menu will provide you with the options as to where you can save your workbook: Recent, OneDrive, This PC, Add a Place, or Browse; the default place to save your workbook is under ‘Documents’ via This PC. There you can enter the name of the file and the format before saving. Once you have named your workbook and chosen a format, select the Save button to the right of the format box.

*Note: If this is the first time you are saving a new document, selecting either the floppy disk icon or the Save option in the File menu will default to the Save As option.

3. Share a Workbook

You can share your workbook with other people who also have Excel 2016 or Office 365 in addition to emailing, presenting online, and posting it to a blog; likewise, they can share their workbooks with you as well. To share your document with other people, you must first save it to your OneDrive account in the cloud; you can do this by saving the document in OneDrive via the Save As menu or selecting the Save to Cloud button under Share with People in Share via the File menu:

Once stored in Excel 2016's cloud, you can share the document from within the File menu or use the Share icon on the main toolbar:

Select File and then select Share. Besides Share with People, you can also choose Email:

Email

You have a few options within the Email menu:

• Send as Attachment - Attach to an email message; you must designate an email address
• Send a Link - Send a link to co-workers or team members; as indicated, the workbook must be saved in a shared location to use this feature
• Send as PDF - Send the workbook in a professional format that cannot be edited or altered by the recipient
• Send as XPS - Akin to the PDF format
• Send as Internet Fax - Send as a fax to a recipient's machine; requires a fax service provider

4. Export

Typically, when you send out a workbook to others, you want to keep it safe from tampering or accidental changes from recipients or other team members. Exporting a workbook changes the format to either lock it from editing or put it in a form that is compatible for others viewing it. To export a workbook in Excel 2016, select File and then select Export in the green column to find two options:

• Create PDF/XPS Document - As mentioned above, creating a PDF or XPS file will lock it from being significantly altered by recipients, but can still be filled in with additional information. To change your workbook to a PDF/XPS file in Excel 2016, select the Create PDF/XPS

• Change File Type - Changing your file type format can modify the compatibility of the workbook for other platforms or to be backward compatible. If the file type you want to use is not listed, choose Save as Another File Type to enter your file type manually (Note: some file types are not applicable in Excel 2016).

5. Add a New Sheet

When working on a larger project or adding more data to an on-going project, having separate spreadsheets for distinct types of data can help you to stay more organized. You can have multiple spreadsheets (Sheets) within a single workbook. For example, you may want to track finances for all 12 months of the year, so creating a different sheet for each month can help you to stay better organized and allow for more accurate graphing or visualizing your data.

Each new workbook starts off with one spreadsheet, entitled “Sheet1” until you change the name of the spreadsheet. There are three parts to the addition of spreadsheets to your workbook:

A. Sheet Scrolling Arrows – Once you have created at least one extra sheet, you can select the left and right arrows respectively to cycle between sheets quickly. Additionally, you can use the Ctrl + Page Up or Ctrl + Page Down keyboard shortcuts to cycle between sheets.
B. Sheet Tabs – These tabs are your easy access points to your individual spreadsheets within the current workbook. To change the names of these spreadsheet tabs, double click on the tab name until it becomes highlighted, type in the name you prefer, and click outside of the tab to save the new name. Additionally, you can select and drag the tabs left or right to reorder them as you see fit.
C. New Sheet – To create a new blank spreadsheet in the current workbook, select the gray plus sign (+) within a circle. Alternatively, you can use the Alt + Shift + F1 keyboard shortcut to create a new sheet.

6. The Home Tab

The Home tab is your default tab when opening a new document in Excel 2016, and has some of the most useful tools you will be using when building out your workbooks. There are five sections on the Home tab ribbon (from left to right): Clipboard, Font, Alignment, Number, Styles, Cells, and Editing. For beginners, we will just focus on the Font, Alignment, Number, Cells, and Editing sections:

Font

This section gives you tools for manipulating the text in your workbook, including size, style, and color.

1. Font –This is the style of the text, ranging from simple standards like “Arial” and “Times New Roman” to fancy styles like “Impact.”
2. Font Size– Determine the size of the lettering in your workbook using this tool; you may choose a preset size number from the drop-down menu or enter one manually.
3. Increase or Decrease Font Size – By clicking on either the increase or decrease buttons, you can increase or decrease highlighted text or all text in small increments.

4. Bold –Thicken the selected text for better visibility or to signify importance
5. Italics – Put selected text at a slant for emphasis or contextual significance
6. Underline – Add a horizontal line underneath selected text for emphasis or contextual significance

7. Border – Thicken the walls of individual cells or all cells within a spreadsheet; multiple options allow you to thicken just one wall or multiple walls

8. Fill Color- From here, you can choose to fill selected cells with color, select a fill color, or cancel a fill color
9. Font Color- Choose a color for selected text from a drop-down menu; the automatic color default is black

Alignment

This section controls the alignment and positioning of text within individual cells or across multiple cells within a spreadsheet.

1. Top, Middle, & Bottom Align – These buttons control the vertical placement of text within a cell; cells are split into thirds with each button positioning text along the top, middle, or bottom of the cell

2. Align Left, Center, & Right – These buttons control the horizontal placement of text within a cell; cells are split into thirds with each button positioning text along the left, center, or right of the cell
3. Orientation – This option allows you to rotate the text to appear diagonally or vertically; this feature is ideal for labeling columns that are narrow

4. Increase or Decrease Indent- Set an indentation for text closer to or further from the cell wall

5. Wrap Text – This feature condenses long lines of text that do not fit within the cell’s horizontal space into multiple lines vertically for visibility

6. Merge & Center – This feature combines highlighted cells into one large cell, and centers the text within; additional options in the pull-down menu include the following:

• Merge Across – Combines highlighted cells within the same row only
• Merge Cells – Combines highlighted cells that are next to one another on any side
• Unmerge Cells – Reduce the selected cell into multiple smaller cells

Number

This section formats numerical values within individual cells to represent as financial, percentage, date, and time values.

1. Number Format – This drop-down menu provides options for value format, such as money, date, and time
2. Accounting Number Format – This drop-down menu provides options for currency types such as US dollars, UK pounds, European euros, Chinese yen, and more
3. Percentage Style – This tool allows you to format the selected cell(s) as a percentage
4. Comma Style – This tool adds commas to signify numbers over one thousand (1000 vs. 1,000)
5. Increase & Decrease Decimal – These buttons either increase or decrease the number of decimal places in numerical values respectively to provide more precise valuations

Cells

This section allows you to edit and format the appearance of the rows and columns within a workbook spreadsheet.

1. Insert Cells – This drop-down menu gives you a few options for inserting individual cells, whole rows, and whole columns; you can also add a new sheet to the workbook from here as well

2. Delete Cells – This drop-down menu gives you a few options for deleting individual cells, whole rows, and whole columns; you can also delete a sheet to the workbook from here as well

3. Format – This drop-down menu provides several options to adjust the appearance of individual cells, whole rows, and whole columns; some of these features include resizing width and height, hide whole rows and columns, organize sheets, and secure sheets from unauthorized tampering

Editing

This section provides you with tools to edit, alter, and filter information in the spreadsheet.

1. AutoSum – This tool allows you to perform calculations for selected cells filled in with data quickly; for example, highlighting three cells in a column and selecting AutoSum with automatically add all the values and display the result in the next cell below the selected cells. Other functions include averages, counting numbers, minimum, maximum, and more.
2. Fill – Fill in neighboring cells with a sequence or series of values in any direction
3. Clear – Use this tool if you need to clear data from certain cells; if you only want to clear certain data, the drop-down menu provides options to only clear format, content, comments, and hyperlinks
4. Sort & Filter – This tool is very helpful for organizing your data, so it’s easier to understand; data parsing options in the drop-down menu include lowest to highest, highest to lowest, custom sorting, and filtering
5. Find & Select – This tool allows you to search your entire spreadsheet for certain text or values quickly; you are also able to find and replace as well as add formulas, comments, and more

7. The Insert Tab

The Insert Tab governs the more interesting additions you can make to your workbooks in Excel 2016. From here you can use your compiled data and transform it into a table, chart, graph, map or illustration. You can also add links, filters, and specialized text to emphasize certain data sets. There are ten sections on the Insert tab ribbon (from left to right): Tables, Illustrations, Add-ins, Charts, Tours, Sparklines, Filters, Links, Text, and Symbols. For beginners, we will just focus on the Tables, Charts, Sparklines, Filters, and Symbols sections:

Tables

Tables are an effective way to display your data in a clean, easy-to-read format. Easily utilizing the column and row format of Excel 2016, tables provide a clear understanding of information to a reader.

1. PivotTable – By highlighting a group of data, you can create a PivotTable based on the values of the data
2. Recommended PivotTable – If you are unsure about what the best kind of PivotTable to use for your data sets is, click this option to get a list of PivotTables that Excel thinks will best compliment your information
3. Table – This option allows you to make a basic table set up that you can edit however you like

Charts

Charts take your information and add a visual element to data sets to allow readers to see comparisons much easier.

1. Recommended Charts – Much like Recommended PivotTable, click this option to get a list of charts that Excel thinks will best compliment your information

2. Charts – There are nine distinct types of charts offered by Excel 2016:

• Column or Bar Chart – good for comparing values across two or more categories
• Hierarchy Chart – good for comparing a part of a set to a whole or to form a hierarchy of data
• Waterfall or Stock Chart – good for showing positive and negative value changes in finance, decreasing proportions over time, or stock performance
• Line or Area Chart – good for showing changes and trends over time
• Statistic Chart – good for outlining analysis of statistical data
• Combo Chart – good for combining multiple types of data into a single chart
• Pie or Doughnut Chart – good for showing proportions and percentages of a larger whole
• Scatter or Bubble Chart – good for trends and relationships between different sets of values
• Surface or Radar Chart – good for displaying data in multiple dimensions (Surface) or values in comparison to a center point (Radar)

3. Maps – This option allows you to compare variables and data across different geographic locations around the world

4. PivotChart – Similar to PivotTables, you can quickly create a chart based on highlighted data

Sparklines

Sparklines are small charts that fit into individual cells in a spreadsheet and represent specific values in your data. There are three types of sparklines: line, column, and win/loss.

Filters

Filters interact with your PivotTables and PivotCharts, making it easier to parse information.

1. Slicer – Filters data to be visually distinguishable
2. Timeline – Filters data by date

Symbols

This section allows you to insert symbols that do not appear on the keyboard as well as mathematical equations for tracking changes in numerical values such as personal finance, sales, and more.

1. Equation – This option provides a drop-down menu with multiple mathematical equations and theorems you can plug into cells to create ongoing calculations as you add data
2. Symbol – This tool provides a wide range of symbols not available on the keyboard or in other parts of the application

8. The Page Layout Tab

The Page Layout tab allows you to customize the overall look of your workbook, adding color, design, and shape to an otherwise plain set of spreadsheets. There are five sections on the Page Layout tab ribbon (from left to right): Themes, Page Setup, Scale to Fit, Sheet Options, and Arrange. For beginners, we will just focus on the Themes and Scale to Fit sections:

Themes

Themes add color and design to your spreadsheets, allowing for many layers of creativity.

1. Themes – This drop-down menu provides many different choices for spreadsheet themes, such as the colors used when making tables and charts or fonts
2. Theme Colors – From here you can change the color scheme currently being used for your data visuals
3. Theme Fonts – From here you can change the text font used in the cells of the spreadsheet
4. Theme Effects – From here you can change the look of your visuals, such as 3D projection, object layering, and more

Scale to Fit

Scale to Fit allows you to adjust the sizing of your spreadsheets for printing, shrinking the width and height to fit a range of data onto individual pages.

9. The Formulas Tab

If you plan on using Excel 2016 for doing calculations and record keeping, you will be making use of the Formulas tab quite heavily. From here you can insert a variety of formulas and make calculations. There are four sections on the Formulas tab ribbon (from left to right): Function Library, Defined Names, Formula Auditing, and Calculation. For beginners, we will just focus on the Function Library and Calculation sections:

Function Library

This section stores all the function equations in Excel 2016 to perform a wide range of mathematical calculations.

Calculation

This section allows you to enact the functions and equations you have inserted into a spreadsheet; you can make calculations manually or set it to do so automatically.

10. The Data Tab

The Data tab is where you control finding and entering in your data. There are seven sections on the Data tab ribbon (from left to right): Get External Data, Get & Transform, Connections, Sort & Filter, Data & Tools, Forecast, and Outline. For beginners, we will just focus on the Data Tools and Outline sections:

Data Tools

This section houses tools that help you make sure your columns and rows convey your data in a comprehensive way.

1. Text to Columns – Break down a single column of data into multiple columns
2. Flash Fill – Automatically fill in values after establishing a value set to follow
3. Remove Duplicates – Remove duplicate rows from the spreadsheet
4. Data Validation – Set specific rules that only allow certain information to be entered into cells as data
5. Consolidate – Consolidate data from multiple categories into a single value set
6. Relationships – Create or adjust connections between tables to show correlations between data sets
7. Manage Data Model – Add and work with data or work on existing data

Outline

This section allows you to group data together for performing functions calculations.

1. Group – Group highlighted cells together
2. Ungroup – Break up a group of cells
3. Subtotal – Quickly perform calculations to create subtotals and totals
4. Show & Hide Detail – Show or hide grouped cells

If you have any further questions regarding Excel 2016 or its features, feel free to contact us!

Source: Tech Supply Shop Blog