Excel – The layout

The basic excel layout has a host of functions and options immediately available in a super user friendly layout, however on first glance it can be overwhelming.

Excel’s main functions are located at the top of the screen on the various “Ribbons”, which can be accessed by the tabs above the ribbons.

By default there are 9 ribbons each containing groups of related functions and options there are: –

  • Home
  • Insert
  • Page Layout
  • Formulas
  • Data
  • Review
  • View
  • Developer
  • New tab

 

The Home ribbon

 

The first section of the home ribbon

The initial section of the Home ribbon is the standard text editing functions that will be familiar from all Microsoft office programs.  The two items that may not be familiar are the “Wrap text” AND “Merge & Center”.

Wrap text – Forces text to remain within the cell in which it is placed, cells often need expanding to display all the text.

Merge & Center – Merges the selected cells into one larger cell and centres the text within the newly created cell.

              Accounting number format

               Displays the value of a cell as a percentage

              Comma style – Display the value of the cell with a thousand separator

      Increase / Decrease the number of decimal points

 

Conditional formatting makes it easy to highlight interesting cells or ranges of cells, emphasize unusual values, and visualize data by using data bars, color scales, and icon sets.

 

     Format a range of cells and convert to a table.

 

 

Insert, Delete and format cells.

Autosum – Applies the sum function to the cell.

Fill – Continue pattern into one or more adjacent cells

Clear – Delete everything from a cell

Sort & Filter – Sort data into ascending or descending order or temporarily filter out specific values

Find & Search – Find and select specific text, formatting or type of information within the document.

 

The insert ribbon

 

Insert PivotTable or table, detailed explanation of these can be found in the Interstellar section.








Insert visual aids to the spreadsheet.







Use the Charts section to insert various different styles of charts into the worksheet, all chart styles can be changed after creation if so required.








Insert Sparkline’s into the worksheet, full explanation can be found in Excel orbit.

 






Insert a slicer to filter a data in a pivot table interactively.

 

 






Insert a hyper link to either internal or external sources.







The text section of the ribbon provides formatting options such as the insertion of text boxes, headers and footers and wordart.






The Symbols section allows for the insertion of non-traditional alpha numerical characters.  The equation option allows you to insert numerous different equation functions into the worksheet.

 

Page Layout Ribbon

 

 

The page layout ribbon does not require a great deal of explanation as the majority of options on here are self-explanatory.

The one feature that may not be familiar however is the “Gridlines” function: –






If the gridlines VIEW option is selected then the work sheet will display grids based on the size of the corresponding row and column.

 

If the Gridlines view is not selected then no grid lines will be present

 

Formula Ribbon

 

The function library provides all available Excel functions in one location.  Categorised by function type Financial, Logical, Text, Date & Time, Lookup & Reference, Math & Trig and More functions.  There is also a handy option to “Insert functions” through the click of a single button.

 

 

 

 

 

The defined names section allows groups of cells to be named in order to assist in the production of formula and functions.  A worked example of creating name ranges can be found here.

 

 

 

 

The formula auditing section allows for formulas to be investigated in order to allow understanding of how the data in the cells is made up.

 

 

 

 

Trace Precedents – Shows which cells affect the cell currently selected

Trace Dependents – Shows which cells are directly affected by changes to the cell selected

Remove arrows – removes the arrows created by the above two operators

Show formulas – Displays the formula of each cell as opposed to the value it calculates.

Error Checking – Checks for common errors within formula

Evaluate formula – Debug formulas by analysing the each part of the formula independently

Watch Window – Monitors the value of certain cells as changes are made to other areas of the sheet.  The cells to be monitored are displayed in a separate window.

 

 

 

 

Calculation options – By default Excel recalculates all formula when any change is made to the sheet, this can become restrictive on very large workbooks.  Toggle between automatic and manual updates using the Calculation options button.

Calculate now – Forces excel to recalculate all formula in the workbook

Calculate sheet – Forces excel to calculate formula in current sheet only

Data Ribbon

 

Excel has great features to integrate with other pieces of software, the get External Data section of the data ribbon allows for connections to external sources to be set.







Refresh All – refreshes all data that is received from external connections

Connections – Display all data within the workbook that is driven by external connections.

 

 

 

 

Sort – Sorts the data based on certain columns in ascending or descending order.  A full worked example can be found here.

Filter – Data can be temporarily hidden by filtering out the values.  Filtering is a quick and easy method of finding required data, again an example of Excel filtering can be found here.

 

 

 

 

Text to columns – Separates the contents of a single cell into numerous columns.  See the example of the Text to column function.

Remove Duplicates – Specify which columns should be used them delete duplicate rows from the data.

Data validation – Use this to prevent invalid data being entered into a cell.  For example ensure only text values are entered into a “Place name” column, returning an error if a number is input.  A full explanation of Excel data validation can be found here.

Consolidate – Combine the values from multiple ranges into a single new range

What-If analysis – Advance feature of Excel allowing for specific values to be found when a certain result is required.  Explanation of What-If analysis.

 

 

 

 

The outline section allows data to be grouped together to reduce the number of visible lines on a spreadsheet whilst retaining the original data.  Excel grouping function explanation.

Group – Allows for a range of data to be collapsed or hidden

Ungroup – reverses the group function

Subtotal – Groups similar data and subtotals at predefined points

 

Review Ribbon

 

The review function should not contain much that is unfamiliar to most Microsoft users the exception being the Changes section.

 

 

 

 

Protect worksheet – Prevents unwanted changes to a worksheet by restricting which cells can be amended.

Protect workbook – Prevents changes to the structure of the workbook such as deleting or adding worksheets.

Share workbook – Allows multiple people to work in a workbook simultaneously.

Protect and share workbook – Shares the work book and protects simultaneously.

Allow Users to Edit Ranges – as described

Track changes – Track all changes made to the document.

 

View Ribbon

 

The majority of the View Ribbon will look similar to other MS applications.  The section that may appear unfamiliar is the Window section of the ribbon.

 

 

 

 

New Window – Opens a new window

Lesson 2: Basic functions and formula