Excel 2016 Ribbons
Microsoft Excel is one of the best tools ever built. It can help you perform not only 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 not only useful for people who won’t get acquainted with Excel 2016 but also for those who are using older versions of Excel-like Excel 2007, Excel 2010, or Excel 2013. The majority of the features and functions discussed here are quite common across the Excel software version. In this first post on Basic Excel 2016, we will discuss the following –
- How to Open the Excel 2016 Software
- How to Open a Blank workbook in Excel 2016
- What are Ribbons
- How to collapse Ribbons
- How to customize Ribbons
- What is Quick Access Toolbar
- How to customize Quick Access Toolbar
- What are Tabs
How to Open the Excel 2016 Software?
For opening the Excel 2016 software, please go to the program menu and click Excel. If you are opening this software for the first time, then worry not; we are going to 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. There are various options that you can choose from; however, this being your first tutorial, I want you to open the Blank Workbook, as shown below.
Once you click on the Blank workbook, you will notice the Blank Workbook opens up 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..
What are Ribbons in Excel
As noted in the picture below, Ribbons are designed to help you quickly find the command that you want to execute in Excel 2016. Ribbons are divided into logical groups called Tabs, and Each tab has its own set of unique functions to perform. 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
For this RIGHT-click on 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. You can always click on the tab to show the commands.
How to Customize Ribbons
Many times it is handy to customize Ribbon containing the commands that you frequently use. This helps save a lot of time and effort while navigating the excel workbook.
Follow below given steps to customize ribbons in excel.
- In order to customize Excel RibbonsExcel RibbonsThe ribbon is an element of the UI (User Interface) which is seen as a strip that consists of buttons or tabs; it is available at the top of the excel sheet. This option was first introduced in the Microsoft Excel 2007., RIGHT click on the Ribbon area and choose, customize the Ribbon.
- Once the dialog box opens up, click on the New Tab as highlighted in the picture below
- Rename the New Tab and the New Group as per your liking. I am naming the tab as “wallstreetmojo” and the group name as “test grouping.”
- You can select the list of commands that you want to include in this new tab from the left-hand side.
- Once you are done, you will notice your customized tab appears in the Ribbon along with the other tabs.
What is Quick Access Toolbar
Quick Access Toolbar is a universal toolbar that is always visible and is not dependent on the tab that you are working with. For example, if you are in the Home Tab, you will see commands not only related to Home Tab but also 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. on the top executing these commands easily. Likewise, if you are in any other tab, say “Insert,” then again, you will same Quick Access Toolbar.
How to Customize Quick Access Toolbar
In order to customize the Quick Access Toolbar, 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 that you desire to use.
- Clipboard – This Clipboard Group is primarily used for Cut copy and paste. This means that if you want to transfer data from one place to another, you have two choices, either 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., which implies copy in the desired format. We will discuss the details of these later in the Excel tutorials. There is 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., which is used to copy the format from the original cell location to the destination cell location.
- Fonts – This font group within the Home tab is used for choosing the desired Font and size. There are hundreds of fonts available in the dropdown which we can use for. 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.
- Alignment – As the name suggests, this group is used for alignment of 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 can be used to place the text vertically or diagonally. Merge and CenterMerge 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. can be used to combine more than one cell and place its content in the middle. This is a great feature to use for table formatting etc. Wrap text can be used when there is a lot of content in the cell, and we want to make all the text visible.
- Number – This group provides options for displaying number format. There are various formats 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., etc. You can also increase and decrease the decimals using this group.
- Styles – This is an interesting addition to Excel. You can have various styles for cells – Good, Bad, and Neutral. There are other sets of styles 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 quickly convert the mundane data into the aesthetically pleasing data table. Conditional formatting is used to format cells based on certain predefined conditions. These are very helpful in spotting the patters across an excel sheet.
- Cells – This group is used to modify the cell – its height and width etc. Also, you can hide and protect the cell using Format Feature. You can also insert and delete new cells and rows from this group.
- Editing – This group within the Home Tab is useful for Editing the data on an excel sheet. The most prominent of the commands here is 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. Command. 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.
- Tables – This group provides a superior way to organize the data. You can use Table to sort, filter, and format the data within the sheet. In addition, you can also use Pivot Tables 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. in our later tutorials.
- Illustrations – This group provides a way to insert pictures, shapes, or artwork into excel. You can insert the pictures either directly from the computer, or you can also use Online Picture Option to search for relevant pictures. In addition, shapes provide additional ready-made square, circle, arrow kind of shapes that can be used in excel. SmartArt provides an awesome graphical representation to visually communicate data in the form of List, 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.. Screenshot can be used to quickly insert a screenshot of any program that is open on the computer.
- Apps – You can use this group to insert an existing App into excel. You can also purchase an App from the Store section. Bing Maps app allows you to use the location data from a given column and plot it on Bing Maps. Also, there is a new feature called People Data, which allows you to transform boring data into an exciting one.
- 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. . It helps you visualize the data in a graphical format. Recommended charts allow Excel to come up with the best possible graphical combination. In addition, you can make graphs on your own, and excel provides various options like Pie-chart, Line Chart, 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., 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., 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., Radar Chart in ExcelRadar Chart In ExcelIn Excel, a radar chart or spider chart displays data in two dimensions for two or more data series. The axes on the radar chart all start at the same point. As a result, it makes it easier to compare more than one or two variables., 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..
- Sparklines – Sparklines are mini charts that are made on the number of data and can be displayed with these cells. There are different options available for sparklines like Line Sparkline, Column Sparkline, and Win/Loss Sparkline. We will discuss this in detail in later posts.
- Filters – There are two types of filters available – Slicer allows you to filter the data visually and can be used to filter tables, pivot tables data, etc. The Timeline filter allows you to filter the dates interactively.
- Hyperlink – This is a great tool to provide hyperlinks from the excel sheet to an external URL or files. Hyperlinks can also be used to create a navigation structure with the excel sheet that is easy to use.
- Text – This group is used to text in the desired format. For example, if you want to have the header and footer, you can use this group. In addition, WordArt allows you to use different styling for text. You can also create your signature using the Signature line feature.
- Symbols – This primarily consists of two parts – a) Equation – this allows you to write mathematical equations that we cannot ordinarily write in an Excel sheet. 2) Symbols are special character or symbols that we may want to insert in the excel sheet for better representation
Page Layout Tab
- Themes – Themes allow you to change the style and visual look of excel. You can choose various styles available from the menu. You can also customize the colors, fonts, and effects in the excel workbook.
- Page Setup – This is an important group primarily used along with 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.. You can choose margins for the print. In addition, you can choose 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. and is helpful in making 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. that we want them to be repeated on each printed copy of the excel sheet.
- 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 as well as height to fit in a certain number of pages.
- Sheet Options – Sheet options is another useful feature for printing. If we want to print the grid, then we can check the print gridlines option. If we want to print the Row and column numbers in the excel sheet, we can also do the same using this feature.
- Arrange – Here, we have different options for objects inserted in ExcelObjects Inserted In ExcelEmbedding allows a user to insert an external object into a worksheet. It generally means inserting an object from another software to an excel worksheet. This option is beneficial to access files related data from within your worksheet space directly. like Bringforward, Send Backward, Selection Pane, Align, Group Objects, and Rotate.
- Function Library – This is a very useful group contain all the formulas that one uses in excel. This group is subdivided into important functions like Financial Functions, Logical Functions, Date & Timing, Lookup & References, Maths and Trignometry, and other functions. One can also make use of Insert Function capabilities to insert the function in a cell.
- Defined Names – This feature is a fairly advanced but useful feature. It can be used to name the cell, and these named cells can be called from any part of the worksheet without working about its exact locations.
- Formula Auditing – This feature is used for auditing the flow of formulas and its linkages. It can trace the precedents (origin of data set) and can also show which dataset is dependent on this. Show formula can also be used 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. is also a useful function to keep a tab on their values as you update other formulas and datasets in the excel sheet.
- Calculations – By default, the option selected for calculation is automatic. However, one can also change this option to manual.
- Get External Data – This option is used to import external data from various sources like Access, Web, Text, SQL Server, XML, etc.
- 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. – This is an advanced feature and is used to combine data from multiple sources and present it in the desired format.
- 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 as well as edit those links from this feature.
- Sort & Filter – This feature can be used to sort the data from AtoZ 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.
- Data Tools – This is another group that is very useful for advanced excel users. One can create various scenario analyses using Whatif 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?” , 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., remove duplicate and consolidate from this group.
- Forecast – This Forecast function can be used to predict the values-based on historical values.
- Outline – One can easily present the data in an intuitive format using the Group and Ungroup options from this.
- Proofing – Proofing 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.. In addition to spell checks, one can also make use of thesaurus if you find the right word. There is also a research button that helps you navigate the encyclopedia, dictionaries, etc. to perform tasks better.
- 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. sheet from English to any other language, then you can use this feature.
- Comments – Comments are very helpful when you want to write an additional note for important cells. This helps the user understand clearly the reasons behind your calculations etc.
- Changes – If you want to keep track of the changes that are made, then one 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. here. Also, you can protect the worksheet or the workbook using a password from this option.
- Workbook Views – You can choose the viewing option of the excel sheet from this group. You can view the excel sheet in the default normal view, or you can choose Page Break view, Page Layout view, or any other custom view of your choice.
- Show – This feature can be used to show or not show Formula bars, grid lines, or Heading in the excel sheet.
- 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.
- Window – The new window is a helpful feature that allows the user to open the second window and work on both at the same time. 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. are another useful feature that allows freezing of particular rows and columns such that they are always visible even when one scrolls to the extreme positions. You can also split the worksheet into two parts for separate navigation.
- Macros – This is again a fairly advanced feature, and you can use this feature to automate certain tasks in Excel Sheet. Macros are nothing but a recorder of actions taken in excel, and it has the capability to execute the same actions again if required.
- Excel for HR Training
- Quick Access Toolbar Excel
- Quick Analysis in Excel
- Toolbar on Excel
- Excel Insert Tab
If you learned something new or enjoyed this post, please leave a comment below. Let me know what you think. Many thanks, and take care. Happy Learning!