• MS–Excel is a spread sheet developed by Microsoft.
  • These spreadsheets contain rows and columns where once can enter values for basic and complex arithmetic operations.
  • An excel document can contain text, numbers, tables, graphs, charts, pivot, etc.
  • MS–Excel has a simpler interface, which allows the user to understand and perform activities.
  • These are helpful in large scale as well as in small-scale industries which in the past were documented in papers which includes huge analytics skills. 

Interface elements of MS–Excel

Let us discuss the interface elements of MS – Excel:

The interface elements are:

  1. Window manipulation buttons – these buttons are used to change the size of the screen usually to minimize, maximize or close a window.
  2. Title Bar – We can see the name of the document along with extension (.xlsx) and a program name.
  3. Tabs – these are options available on MS – word for formatting and adding value to any document.

o File     o Formulas

o Home     o Data

o Insert     o Review

o Page Layout     o View  

  1. Toolbars

o There are a few commands that are hidden.

o The additional options are displayed by clicking on the arrow in their lower right corner.

o Few options will be greyed out denoting that tools cannot be applied to a selected object.

  Tools that have a downward arrow, when pressed lead to another sequence of commands.

  1. Selected cells – The area where we key in a number or text.
  2. Column titles – A Horizontal bar which possesses letters.
  3. Row titles – A Vertical bar which possesses numbers.
  4. Sheets – We can add more sheets and each sheet can be renamed.
  5. Horizontal and vertical navigation bars (sliders) – This helps to slide through the documents.
  6. 10. Status Bar – This displays information about some special function of Microsoft Excel.
  7. 11. Formula Bar – This displays the formula or information in a particular selected cell.

 

Basics of MS-Excel

Spreadsheet or Workbook – A Microsoft Excel file includes any number of worksheets. By default, there will be 3 sheets which can be increased or decreased.

Worksheet – It is nothing a large number of cells arranged in rows and columns that form a table.

Cell – This is the basic element in Excel for data entry. It can be text, number or formula. A cell is where a row and a column meet.

Cell address – column letter and row number on a worksheet, e.g. A1, C7, F25.

Selecting cells – To select a cell, press the left mouse button on a cell.

Enter data – Data can be added in cells or even the existing content can also be edited.

  • To add content to a cell, single-click with the left mouse button on a cell and enter data.
  • To modify existing value in a cell, single-click on the selected cell and then click on the formula bar to make changes to the data.

Alternatively, we can also press F2 after selecting the cell to move the cursor into the formula bar.

The values that are entered in the cell, by default numbers get aligned to the right side and the text aligns to the left side of the cell.

However, this can be customised using the Alignment tools in the toolbar.

Wrap Text – If the value entered in the content exceeds cell width, it will be displayed over the adjacent cells which have no data. However, this can also be customised using the Wrap Text to fit the text within the same cell.

To move to another cell – To move to next tab we can use the TAB key to move to the right, the ENTER key to move down, the keys with an arrow on the keyboard, or the left mouse button.

To select a range of cells –

Select the first cell in a range >> press and hold the left mouse button >> move the mouse to the last cell and release the button, or

Select the first cell in a range >> press and hold the Shift key >> select the last cell in the range and release the Shift key

Select a row or column – the mouse button on the row number or column letter.

Select several adjacent rows – press the left mouse button on the row number, press and hold the left mouse button, move the mouse to the last row and release the left button (or use the Shift key, while it is pressed, select the first then the last row and then release the Shift key).

Rename a worksheet – double click on the sheet tab of the worksheet and type the new worksheet name.

Deleting letter by letter: Press the Delete key as it deletes text that is located to the right of the cursor’s position; the Backspace key - deletes text located to the left of the cursor.

Fast delete: Select all the desired cells to be deleted and press the delete key.

  • In order to undo the changes that you have done on the word document, you can click on this icon to undo the same or you can also press Ctrl + Z.
  • In order to redo the changes that you have done on the word document, you can click on this icon to redo the same or you can also press Ctrl + Y.

To copy & move text within an open sheet

Copy / Paste:

  1. Select the text that you want to copy
  2. Copy the selected text by using the Copy tool or by pressing the keyboard shortcut: Ctrl + C
  3. Place the cursor on the cell where you want the text to be copied
  4. Paste the copied text by using the Paste tool or by pressing the keyboard shortcut: Ctrl + V

Cut / Paste:

  1. Select text that you want to cut
  2. Cut the selected text by using the Cut tool or by pressing the keyboard shortcut: Ctrl + X
  3. Place the cursor on the cell where you want to move the text
  4. Paste the text by using the Paste tool or by pressing the keyboard shortcut: Ctrl + V

File menu

The File menu allows you the following actions:

Create a new document: New > Blank document > Create or by pressing the keyboard shortcut: Ctrl + N

 

Open an existing document: You can open a document by using the open command  or by pressing the keyboard shortcut: Ctrl + O

 

Open more than one document: You can open more documents by using the Ctrl key for adjacent files or the Shift key for non-adjacent files

 

Save a document: To save any document, use the save key  or by pressing the keyboard shortcut Ctrl + S

 

  • When the file is saved for the first time, the Save As dialog box appears with the following fields:
  • Save in – enter the location of the folder where the document will be saved File name – type in the file name
  • Save as type – you can choose the file type in which your file will be saved for example MS Excel 2010 .xltx

 

Print a document:

 

  • Printer: Select the printer which will be used for printing
  • Page range:

 

  1. All
  2. Current page
  3. Pages – Enter the page number to print
  • Orientation:

Portrait (vertical paper) and Landscape (horizontal paper)

Share – With this new feature we can share files easily

  • Change File Type – this helps to change the file type
  • Create PDF / XPS Document – Save the file in the fixed format so that one cannot easily change the format

 Home tab

The Home tab includes the following tools:

 

  • Text formatting to align the text
  • Font Size is the size of the font size, we can enter the value or choose any size from a drop-down menu
  • Font is the different letter types: Times New Roman, Verdana, Ariel, Calibri etc.
  • Bold - Ctrl + B  Italic - Ctrl + I  Underline - Ctrl + U
  • Shading tool is used to fill colour in the text background.
  • Borders tool is used to set different types of borders around the selected text.
  • Font colour tool is used to change the colour of letters from colour palette.
  • Text alignment tool is used to align the text.

 

  1. Left (keyboard shortcut: Ctrl +L)
  2. Center (keyboard shortcut: Ctrl + E)
  3. Right (keyboard shortcut: Ctrl + R)
  4. Justify (keyboard shortcut: Ctrl + J)

 

  • Merge and Center is used to merge the cells that are selected.
  • Orientation  is used to set the direction of the content in a cell
  • Wrap text If the value entered in the content exceeds cell width, it will be displayed over the adjacent cells which have no data. However, this can also be customised using the Wrap Text to fit the text within the same cell.
  • Insert is used for inserting cells, rows, columns
  • Delete is used for deleting cells, rows, columns

 

  • Find & Replace tool:

 

  1. Find What – type in a word or phrase that you want to replace
  2. Replace With – type in a word or a phrase which you want to replace the existing word or phrase with

 

Enter function:

 

  1. select a cell range
  1. enter a function via menu shown on the right

(Or)  select the cell in which you want to enter function value

enter the symbol „=“ 

enter the function manually (e.g. „sum“), and the cell range to which the function will apply, respecting syntax

Most often used functions:

 

=SUM(cell range) à adding the numbers in selected cells

 

=AVERAGE(cell range) à finds the average value

 

=MIN(cell range) à finds the smallest value

 

=MAX(cell range) à finds the biggest value

 

Deleting options:

 

  • Clear All
  • Clear Contents
  • Clear Formats 

 

Sort A to Z - sort data within a table from minimum to maximum value

Sort Z to A – sort data within a table from maximum to minimum values

  • Format Painter tool is used to copy formatting from one part of the cell to another.
  • By pressing MS Excel Help or by pressing the F1 key on the keyboard we can access MS Excel help. 

Insert tab

 

The Insert tab includes the following tools:

 

  • Tables tool inserts a table within a document. Choose the Table tool > press the Insert Table command > enter the number of rows and columns.

 

  • Pictures tool - select a picture by clicking on it with a left mouse button, these images can be wrapped and aligned by setting the size and position.
  • Insert symbol to edit mathematical or relational values €  £  © ∞  ≠ " H Y ^ › ¯ ü J

  -  opens color palette

  -  opens color palette and options for choosing type and     thickness of lines

  -  effects: 3D, shadow, reflection, rotation etc. 

While the object is selected, we can open the Format tab that contains some new formatting options

 

  • Corrections - A tool for contrast, brightness, sharpness correction

 

  • Color - change color of the images in order to better match the rest of the content

 

  • Artistic effects - tools that can make an image appear as if it was a drawing or painting
  • tool for inserting a text box; after selecting this tool, we can set the position and the size of the text box in the worksheet via the drag-and-drop method
  • SmartArt tool - this provides a standard set of shapes to emphasize the information.
  • Chart tool - with the help of chart option, even huge data can be represented in a graphical representation.

Page layout tab

The tools on the Page Layout tab are:

 

  • Margins: This gives a document a proper format by choosing one of the pre-determent margins with regards to height and width. 
  • Orientation: This view can either be portrait and landscape.
  • Size tools: Allows you to set the size of the paper.
  • Scale: this tool is used to fit the data and paper for printing
  • Breaks: it is recommended that the user sets the breaks manually

 

  1. Insert Page Break
  2. Remove Page Break

 

  • Tools for organizing content:
      - Bring Forward

      - Send Backward

Review tab 

The Review tab is used for the following actions:

 

  • Spelling Words will be underlined in red, wavy lines to denote that they are misspelled.
  • You can change Settings: Language > Set Proofing Language
  • Then the Spelling and Grammar tool opens a dialog box with the following options: Ignore once, Ignore all, Change, Change all
  • In case if a word is not in the dictionary you can add it: select the desired word, right-click it and choose to Add to dictionary