Which tab in Excel provides access to a library of formulas and functions?

Microsoft Excel is one of the best tools ever built. It can help you perform easy tasks like calculations but also helps in performing analytical tasks, visualization, and financial modeling. This Excel training course assumes no previous knowledge of Excel, and please feel free to jump across sections if you already know a bit of Excel. This Excel 2016 tutorial is useful for people who would not get acquainted with Excel 2016 and those using older versions of Excel-like Excel 2007, Excel 2010, or Excel 2013. Most of the features and functions discussed here are common across the Excel software version. In this first post on basic Excel 2016, we will discuss the following:

You are free to use this image on your website, templates, etc., Please provide us with an attribution linkHow to Provide Attribution?Article Link to be Hyperlinked
For eg:
Source: Excel 2016 – Ribbons, Tabs and Quick Access Toolbar (wallstreetmojo.com)

Table of contents

How to Open the Excel 2016 Software?

To open the Excel 2016 software, please go to the “Program” menu and click “Excel.” If you are opening this software for the first time, worry not; we will take this Excel training step-by-step.

How to Open a blank workbook in Excel 2016

Once you open the Excel software from the “Program” menu, the first thing that you would notice is a large screen displayed as per below.

Since this is your first workbook, you will not notice any recently opened workbooks. Instead, you can choose from various options; however, this being your first tutorial, I was hoping you could open the “Blank Workbook,” as shown below.

Once you click on the “Blank Workbook,” you will notice the “Blank Workbook” opening in the below format.

You may also take a look at this –  Head to Head Differences Between Excel and AccessHead To Head Differences Between Excel And AccessExcel and Access are two of Microsoft's most powerful tools for data analysis and report generation, but there are some significant differences between them. Excel is an older product of Microsoft, whereas Access is the most advanced and complex product of Microsoft. Excel is very easy to create dashboards and formulas, whereas Access is very easy for databases and connections.read more.

What are Ribbons in Excel

As noted in the picture below, ribbons are designed to help you quickly find the command you want to execute in Excel 2016. Ribbons are divided into logical groups called “Tabs.” Each tab has its own set of unique functions to perform. For example, there are various tabs – “Home,” “Insert,” “Page Layout,” “Formulas,” “Date,” “Review,” and “View.”

How to Collapse (Minimize) Ribbons

If you do not want to see the commands in the ribbons, you can always “collapse” or “minimize” ribbons.

Right-click on the “Ribbon” area, and you will see various options available here. Here, you need to choose “Collapse the Ribbon.”

Once you choose this, the visible groups go away, and they are now hidden under the tab. But, of course, you can always click on the “Tab” to show the commands.

How to Customize Ribbons

It is often handy to customize a ribbon containing the commands you frequently use. It helps save a lot of time and effort while navigating the Excel workbook.

Follow the below-given steps to customize ribbons in Excel:

1. We must first right-click on the “Ribbon” area to customize Excel Ribbonsu0022Excelu0022Theread more and choose “Customize the Ribbon.”
   

2. Once the dialog box opens, click on the “New Tab,” as highlighted in the picture below.
   

3. Now, “Rename” the “New Tab” and the “New Group” as per your liking. We are naming the tab “wallstreetmojo” and the group name “test grouping.”
   

4. From the left-hand side, we can select the list of commands we want to include in this “New Tab.”
   

5. Once we are done, we may notice that our customized tab appears in the “Ribbon” and the other tabs.
   

What is the Quick Access Toolbar

The Quick Access Toolbar is a universal toolbar that is always visible and not dependent on the tab you are working with. For example, if you are in the “Home” tab, you will see commands related to the “Home” tab, and the Quick Access ToolbarQuick Access ToolbarQuick Access Toolbar (QAT) is a toolbar in Excel that may be customized and is located on the upper left-hand side of the window. It enables users to save important shortcuts and easily access them when needed.read more on the top executing these commands easily. Likewise, if you are in any other tab, say “Insert,” then again, you will see the same Quick Access Toolbar.

How to Customize Quick Access Toolbar

To customize the Quick Access Toolbar, we must right-click on any part of the “Ribbon,” and you will see the following:

Once you click on “Customize Quick Access Toolbar,” you get the dialog box from where you can select the set of commands you want to see in the Quick Access Toolbar.

What are the Tabs?

Tabs are nothing but various options available on the “Ribbon.” These can be used for easy navigation of commands you desire to use.

Home Tab

1. Clipboard – This clipboard group is primarily used for cutting, copying, and pasting. Suppose you want to transfer data from one place to another. In that case, you have two choices, COPY (preserves the data in the original location) or CUT (deletes the data from the original location). Also, there are options of Paste SpecialOptions Of Paste SpecialPaste special in Excel allows you to paste partial aspects of the data copied. There are several ways to paste special in Excel, including right-clicking on the target cell and selecting paste special, or using a shortcut such as CTRL+ALT+V or ALT+E+S.read more, which implies copying in the desired format. We will discuss the details of these later in the Excel tutorials. Also, Format Painter ExcelFormat Painter ExcelFormat painter in Excel is a tool used to copy the same format of a single cell or a group of cells to the other cells. You will find it on the home tab in the clipboard section.read more, replicates the format from the original cell location to the destination cell location.
2. Fonts – This font group within the “Home” tab chooses the desired “font” and “size.” There are hundreds of fonts available in the drop-down, which we can use. In addition, you can change the font size from small to large, depending on your requirements. Also helpful is the feature of Bold (B), Italics (I), and Underline (U) of the fonts.
3. Alignment – As the name suggests, this group is used to align tabs – “Top,” “Middle,” or “Bottom” alignment of text within the cell. Also, there are other standard alignment options like “Left,” “Middle,” and “Right” alignment. There is also an orientation option that we can use to place the text vertically or diagonally. We can use “Merge and Center”Merge And CenterThe merge and center button is used to merge two or more different cells. When data is inserted into any merged cells, it is in the center position, hence the name merge and center. read more to combine more than one cell and place its content in the middle. It is a great feature to use for table formatting etc. We can also use the “Wrap text” when there is a lot of content in the cell, and we want to make all the text visible.
4. Number – This group provides options for displaying number format. Various formats are available – general, accounting, percentage, comma style in excelComma Style In ExcelWhen the values are over 1000, the comma style is used to visualize the numbers with commas. For instance, if we apply this style to data with a value of 100000, the result will be 100,000.read more, etc. You can also increase and decrease the decimals using this group.
5. Styles – This is an interesting addition to Excel. You can have various styles for cells – Good, Bad, and Neutral. Other styles are available for data and models like calculation, check, warning, etc. In addition, you can make use of different “Titles” and “Heading” options available within “Styles.” The “Format-Table” allows you to convert mundane data into an aesthetically pleasing data table quickly. Whereas “Conditional formatting” is used to format cells based on certain predefined conditions. These are very helpful in spotting patterns across an Excel sheet.
6. Cells – This group is used to modify the cell – its height, width, etc. Also, you can hide and protect the cell using the “Format” feature. You can also insert and delete new cells and rows from this group.
7. Editing – This group within the “Home” tab is useful for editing the data on an Excel sheet. The most prominent commands here are the Find and Replace in ExcelFind And Replace In ExcelFind and Replace is an Excel feature that allows you to search for any text, numerical symbol, or special character not just in the current sheet but in the entire workbook. Ctrl+F is the shortcut for find, and Ctrl+H is the shortcut for find and replace.read more commands. Also, you can use the sort feature to analyze your data – sort from A to Z or Z to A, or you can do a custom sort here.

Insert Tab

1. Tables – This group provides an excellent way to organize the data. You can use a table to sort, filter, and format the data within the sheet. In addition, you can also use PivotTables to analyze complex data very easily. We will be using Pivot TablesPivot TablesA Pivot Table is an Excel tool that allows you to extract data in a preferred format (dashboard/reports) from large data sets contained within a worksheet. It can summarize, sort, group, and reorganize data, as well as execute other complex calculations on it.read more in our later tutorials.
2. Illustrations – This group provides a way to insert pictures, shapes, or artwork into Excel. You can insert the images directly from the computer or use the “Online Picture Option” to search for relevant pictures. In addition, shapes provide additional ready-made squares, circles, and arrows, the kind of shapes that we can use in Excel. SmartArt provides an awesome graphical representation to visually communicate data in lists, organizational charts, Venn diagram to process diagramsVenn Diagram To Process DiagramsThere are two ways to create a Venn Diagram. 1) Create a Venn Diagram with Excel Smart Art 2) Create a Venn Diagram with Excel Shapes.read more. The “Screenshot” can be used to quickly insert a screenshot of any program that is open on the computer.
3. Apps – You can use this group to insert an existing app into Excel. You can also purchase an app from the “Store” section. For example, the “Bing Maps” app allows you to use the location data from a given column and plot it on “Bing Maps.” Also, a new feature called “People Data” allows you to transform boring data into an exciting one.
4. Charts – This is one of the most useful features in ExcelFeatures In ExcelThe top features of MS excel are - Shortcut keys, Summation of values, Data filtration, Paste special, Insert random numbers, Goal seek analysis tool, Insert serial numbers etc. read more. It helps you visualize the data in a graphical format. The “Recommended” charts allow Excel to develop the best graphic combination. In addition, you can make graphs on your own, and Excel provides various options like pie-chart, line charts, Column Chart in ExcelColumn Chart In ExcelColumn chart is used to represent data in vertical columns. The height of the column represents the value for the specific data series in a chart, the column chart represents the comparison in the form of column from left to right.read more, Bubble Chart k in ExcelBubble Chart K In ExcelIn Excel, a bubble chart is a type of scatter plot that uses bubbles to display values and comparisons. Like scatter plots, bubble charts compare data on both horizontal and vertical axes.read more, combo chart in excelCombo Chart In ExcelExcel Combo Charts combine different chart types to display different or the same set of data that is related to each other. Instead of the typical one Y-Axis, the Excel Combo Chart has two.read more, Radar Chart in ExcelRadar Chart In ExcelRadar chart in excel is also known as the spider chart in excel or Web or polar chart in excel, it is used to demonstrate data in two dimensional for two or more than two data series, the axes start on the same point in radar chart, this chart is used to do comparison between more than one or two variables, there are three different types of radar charts available to use in excel.read more, and Pivot Charts in ExcelPivot Charts In ExcelIn Excel, a pivot chart is a built-in feature that allows you to summarize selected rows and columns of data in a spreadsheet. It is a visual representation of a pivot table that helps in the summarization and analysis of datasets, patterns, and trends.read more.
5. Sparklines – Sparklines are tiny charts made on the number of data and can be displayed with these cells. Different options are available for sparklines like “Line Sparkline,” “Column Sparkline,” and “Win/Loss Sparkline.” We will discuss this in detail in later posts.
6. Filters – There are two types of filters available: “Slicer” allows you to filter the data visually and can be used to filter tables, PivotTables data, etc. The “Timeline” filter allows you to filter the dates interactively.
7. Hyperlink – It is a great tool to provide hyperlinks from the Excel sheet to an external URL or files. We can also use hyperlinks to create a navigation structure with the Excel sheet that is easy to use.
8. Text – This group is used to text in the desired format. For example, you can use this group to have the header and footer. In addition, “WordArt” allows you to use different styling for text. You can also create your signature using the “Signature” line feature.
9. Symbols – This primarily consists of two parts – a) Equation – it allows you to write mathematical equations that we cannot ordinarily write in an Excel sheet. 2) Symbols – They are special characters or symbols that we may want to insert into the Excel sheet for better representation.

Page Layout Tab

1. 
   Themes – Themes allow you to change Excel’s style and visual look. You can choose various types available from the “Menu.” You can also customize the Excel workbook’s colors, fonts, and effects.
2. Page Setup – This is an important group primarily used to print printing an excel sheetPrinting An Excel SheetThe print feature in excel is used to print a sheet or any data. While we can print the entire worksheet at once, we also have the option of printing only a portion of it or a specific table.read more. You can choose margins for the print. In addition, you can select your printing orientation from “Portrait” to “Landscape.” Also, you can choose the size of paper like “A3,” “A4,” “Letterhead,” etc. The print area allows you to see the print area within the excel sheetPrint Area Within The Excel SheetIn Excel, the print area is the portion of the workbook or worksheet that we wish to be printed rather than the entire workbook or worksheet. From the page out tab, we can set up a print area. In addition, a single worksheet can contain numerous print areas.read more and helps make the necessary adjustments. We can also add a break where we want the next page to begin in the printed copy. Also, you can add a background to the worksheet to create a style. “Print Titles” is like a header and footer in excelHeader And Footer In ExcelHeader and Footer is the top and bottom portion of a document respectively, similarly excel also has options for headers and footers, they are available in the insert tab in the text section, using this features provides us with two different spaces in the worksheet one on the top and one on the bottom.read more that we want to be repeated on each printed copy of the Excel sheet.
3. Scale to Fit – This option is used to stretch or shrink the printout of the page to a percentage of the original size. You can also shrink the width and height to fit a certain number of pages.
4. Sheet Options – The “Sheet Options” is another useful feature for printing. We can check the print gridlines option if we want to print the grid. If we print the row and column numbers in the Excel sheet, we can also do the same using this feature.
5. Arrange – Here, we have different options for objects inserted in ExcelObjects Inserted In ExcelIn Microsoft Excel, the “Object Insert” option allows a user to insert an external object into a worksheet. Embedding generally means inserting an object from another software (Word, PDF, etc.) into an Excel worksheet.read more like “Bring Forward,” “Send Backward,” “Selection Pane,” “Align,” “Group Objects,” and “Rotate.”

Formulas Tab 

1. Function Library – This is a very functional group containing all the formulas used in Excel. This group is subdivided into important functions like “Financial Functions,” “Logical Functions,” “Date & Timing,” “Lookup & References,” “Maths and Trigonometry,” and other functions. One can also use the “Insert” function capabilities to insert the function in a cell.
2. Defined Names – This feature is fairly advanced but useful. We can use it to name the cell, and these named cells can be called from any part of the worksheet without working about their exact locations.
3. Formula Auditing – This feature audits the flow of formulas and their linkages. It can trace the precedents (origin of data set) and show which dataset depends on this. We can also use the “Show Formula” to debug errors in the formula. The Watch window in excelWatch Window In ExcelThe watch window in excel is used to watch for the changes in the formulas while working with a large amount of data; when we click on the watch window, a wizard box appears to select the cell for which the values are to be monitored.read more is also useful to keep a tab on their values as you update other formulas and datasets in the Excel sheet.
4. Calculations – By default, the option selected for calculation is automatic. However, one can also change this option to manual.

Data Tab

1. Get External Data – This option is used to import external data from various sources like “Access,” “Web,” “Text,” “SQL Server,” “XML,” etc.
2. Power QueryPower QueryPower Query is an excel tool used to import data from different sources, transform (change) it as required, and return a refined dataset in the workbook.read more – This advanced feature combines data from multiple sources and presents it in the desired format.
3. Connections – This feature is used to refresh the Excel sheet when the data in the current Excel sheet is coming from outside sources. You can also display the external links and edit those links from this feature.
4. Sort & Filter – We can use this feature to sort the data from A to Z or Z to Z, and also, you can filter the data using the drop-down menus. Also, one can choose advanced features to filter using complex criteria.
5. Data Tools – This is another very useful group for advanced excel users. One can create various scenario analyses using What-If analysis – Data Tables, Goal Seek in ExcelGoal Seek In ExcelThe Goal Seek in excel is a “what-if-analysis” tool that calculates the value of the input cell (variable) with respect to the desired outcome. In other words, the tool helps answer the question, “what should be the value of the input in order to attain the given output?” read more, and Scenario Manager. Also, one can convert Text to ColumnText To ColumnText to columns in excel is used to separate text in different columns based on some delimited or fixed width. This is done either by using a delimiter such as a comma, space or hyphen, or using fixed defined width to separate a text in the adjacent columns.read more, remove duplicates and consolidate from this group.
6. Forecast – We can use this “Forecast” function to predict the values based on historical values.
7. Outline – One can easily present the data intuitively using the “Group” and “Ungroup” options from this.

Review Tab 

1. Proofing – It is an interesting feature in Excel that allows you to run spell checks in the excelSpell Checks In The ExcelSpell check in excel is a method of detecting spelling errors in text strings. Unlike MS Word and PowerPoint, MS Excel does not underline a misspelled word. As a result, a user may overlook spelling mistakes. Spell check in excel is beneficial when working with databases containing a mix of numbers and text.read more. In addition to spell checks, one can also use a thesaurus if you find the right word. A research button also helps you navigate the encyclopedia, dictionaries, etc., to perform tasks better.
2. Language – If you need to translate your excelTranslate Your ExcelThe Excel Translate function translates any statement or word into another language. It can be found in the language section of the review tab.read more sheet from English to any other language, you can use this feature.
3. Comments – Comments are very helpful when you want to write an additional note for important cells. It helps the user understand clearly the reasons behind your calculations etc.
4. Changes – If you want to keep track of the changes that are made, you can use the Track Changes optionCan Use The Track Changes OptionTracking changes in Excel is a technique of highlighting changes made in a shared worksheet by any user. It highlights the cell that has been modified. This option is present in the "changes" section of the review tab and can be enabled when we share a workbook.read more. Also, you can protect the worksheet or the workbook using a password from this option.

View Tab 

1. Workbook Views – You can choose the viewing option of the Excel sheet from this group. You can view the Excel sheet in the normal default view, select the “Page Break” view, “Page Layout” view, or any other custom view.
2. Show – We can use this feature to show or not show formula bars, grid lines, or heading in the Excel sheet.
3. Zoom – Sometimes, an Excel sheet may contain a lot of data, and you may want to change zoom in or zoom out desired areas of the Excel sheet.
4. Window – The new window is a helpful feature that allows users to open the second window and work on both simultaneously. Also, freeze panesFreeze PanesFreezing panes in excel helps freeze one or more rows and/or columns so that they remain fixed while scrolling through the database.read more are another useful feature that helps to freeze particular rows and columns such that they are always visible even when one scrolls to extreme positions. You can also split the worksheet into two parts for separate navigation.
5. Macros – This is again a fairly advanced feature, and you can use this feature to automate certain tasks in Excel Sheets. “Macros” are nothing but a recorder of actions taken in Excel, and they can execute the same steps again if required.

Recommended Articles

• Excel for HR Training
• Quick Access Toolbar Excel
• Quick Analysis in Excel
• Toolbar on Excel
• Excel Insert Tab

What next?

If you learned something new or enjoyed this post, please comment below. Let me know what you think. Many thanks, and take care. Happy Learning!

What tab is function library in Excel?

Which tab in the MS Excel allows the user to access the most commonly used functions and Formulas in Excel?

Where is the Formulas tab on Excel?

Which ribbon tab is home to the function library used to insert functions into worksheets?