Excel Anatomy Part04 - Home and Insert Ribbons

Home and Insert Ribbons

Home Ribbon

Home tab contains the most frequently used options such as cut-copy-paste, font formatting, alignment, Number, Conditional formatting, etc. All the options are used to format the data.

Clip board

This group contains frequently used commands: Cut, Copy, Paste and Format painter. Clipboard option allows us to collect text and graphic items and paste it.

Font

We use this option to change the font style and font-size. We can make it bold, italic and underline. Also, this group contains border styles, fill color, font color.

Alignment

We use this option to change the alignment of cell’s text to the right, left and middle. Also, we can subject the text to top, bottom, and middle alignment. In this group, we have Wrap text option to adjust and make the text visible within a cell, and we can also merge 2 or more cells, using merge option.

Number

We use this option to change the number formatting into General, Percentage, Currency, Date, Time, Fraction etc. We can increase and decrease the decimal and convert the number into accounting number.

Styles

In this option, we have Conditional Formatting, Format as Table and Cell Styles. Conditional formatting is used to highlight the cell or range on the basis of conditions. Format as table is having ready made table format and Cell styles feature different types of built-in styles that are a combination of Font style, Font color and Fill color.

Cells

We use this option to insert or delete cells, rows, columns and sheets. Also, we have format option to adjust the height, width of cells or range. Using this option, we can hide or un hide the range, protect the workbook, rename the sheet name, fill the tab color, move or copy to sheets, lock the cells.

Editing

This option has Auto Sum feature to return the total of numbers and move the text to right, left, up and down, Clear the format, content, comments and hyperlink; sort the data and find and select option.

Insert Ribbon

We use Insert tab to insert the picture, charts, filter, hyperlink etc. We use this option to insert the objects in Excel. To open the insert tab, press shortcut keys Alt+N.

Tables

We use this option to insert the dynamic table, Pivot table and recommended table. Pivot table is used to create the summary of report with the built-in calculation, and we have option to make our own calculation. Tables make it easy to sort, filter and format the data within a sheet. This option is also having recommended table that means on the basis of data, we can just insert the table as per the Excel’s recommendation.

Illustration

We use this option to insert the Pictures, Online Pictures, Shapes, Smart Art and Screenshot. It means if we want to insert any image, we can use Illustration feature..

Charts

Charts is very important and useful function in Excel. In excel, we have different and good numbers of ready made chart options. We have 8 types of different charts in Excel:- Column, Bar, Radar, Line, Area, Combo, Pie and Bubbles chart. We can insert pivot chart as well as Recommended chart, and if we don’t know which chart we should insert for the data, we can use this option to full fill the requirement.

Spark lines

Spark line is a very tricky and useful option added by Microsoft Excel. On the basis of a range, it can visualize the trends in a single cell as charts. We have 3 different types of cell charts:- Line, Column and Win/Loss chart.

Filters

We use this option to filter data visually and filter dates interactively. We have 2 options: Slicers and Timeline. We use Slicer to make the fast and easier to filter tables, Pivot tables, Pivot Charts and cube functions. Timeline makes it faster and easier to select time periods in order to filter Pivot Tables, Pivot Charts and Cube function.

Links

We use this option to create the link in the document for the quick access to webpage and files.We can also use it to access different locations in the document.

Symbols

We use this option to insert the symbols and equation. Equation is used to insert the common mathematical equations to your document and also we can add equation by
using the mathematical symbols. We use Symbols to insert the symbols which are not on
the keyboard and, to create the equation,

Text

We use this option to insert the Text box, Header and Footer, Word art, Signature and objects. we insert Text box to write something in the image format. We use Header and Footer options to place the content on the top and bottom of the page. Word art makes the text stylish. Insert the add Signature Lines that specify the individual who is supposed to sign it.

Excel Anatomy- Part03 Excel Interface and Ribbon

Excel  interface  and ribbon

Title bar

A title bar that shows the application name and the name of the open file.

Tool bar
Displays the buttons of the most frequently used functions. This tab contains undo, repeat, save keys.

 Menu bar
A menu bar that provides access to the various functions of the spreadsheet.                           

  

File

The File menu is the leftmost item in the Excel ribbon. The File ribbon items enable you to perform file management functions, including open, save, close, and print. You also use the File menu to import from external sources into Excel, along with options that allow you to tweak Excel itself.

Pro Tip: The File menu does not appear as a ribbon in Excel for Mac. Instead, the File menu appears in the Application menu bar.

Home tab

The Home menu is the second menu in the Excel menu bar. The Home ribbon items include options for formatting font, color, conditional formatting, filter, number type, and more. All these functions help one in performing various effective calculations.

Insert

As the name suggests, the Insert menu helps you insert various options and item into and Excel spreadsheet. You can insert a variety of things from pivot table to picture, clip art, shapes, screen shots, charts and graphs, text box, Header and Footer, symbols, equation and more.

Page layout

You’ll see many options for configuring pages for viewing and printing—including page size,margins, colors and fonts, and so forth. You can also customize cell height and width on the Page Layout menu.

Formulas

The Formulas menu is where you find all the number-crunching options. Excel comes with lots of formulas including financial, logical, text, date &time ,lookup & reference, and math &trigonometry.

Data

The Data menu also contains many important functions in Excel, including imports and connections with databases. You also access the sort, filter,remove duplicates, data validation,consolidation, group, ungroup, and subtotal functions on the Data menu.


Review

Earlier in this article, we mentioned Excel’s ability to collaborate on spreadsheets. The Review menu is where many of those tasks take place. You can make comments in cells for your colleagues, check spelling, track changes, and even restrict permission using items in the Review menu

View

On the View menu, you customize the way spreadsheets appear on your screen. Options include displaying grid lines between cells, toggling the formula bar and headings, and more.This menu also gives you options to view and record macros, as well—macros let you record common steps you perform so you don’t have to repeat the same things over and over again

Formula bar

This information that can be entered or edited through the formula bar includes formulas,which are combinations of letters, numbers and symbols that can help users perform calculation.If you want to hide or display the formula bar, you can do it in VIEW >> Show >> Formula Bar.


Click down arrow to see the used values , formulas etc.

Work sheet

Excel worksheet is a single spreadsheet that contains cells organized by rows and columns. A worksheet begins with row number one and column A Each cell can contain a number, text or formula. A cell can also reference another cell in the same worksheet, the same workbook or a different workbook. In Excel 2010, the maximum size of a worksheet is 1,048,576 rows by16,384 columns.

Workbook

A workbook is an Excel file that contains one or more worksheets.

Status bar

The status bar is located at the bottom of the screen, below the working area. It displays information about the current activities in the worksheet. Left side status bar (zoom bar) . The first thing that stands out when you look at the Status Bar is the word READY. It will be present for most of the time when you work with Excel.

As soon as you start typing in a cell, it will change to the ENTER. Double click the cell containing a value, and the text will change to EDIT.

Excel Anatomy- Part02 option and customize ribbon

Option and customize

Introduction

Open the File menu and select Options from the left navigation pane to open the Excel Options dialog. The dialog has categories for General, Formulas, Proofing, Save, Language, Advanced, Customize Ribbon, Quick Access Toolbar, Add-Ins, and Trust Center. The Trust Center leads to another 12 categories.

General

The most commonly used settings, such as user interface settings, default font for new workbooks, number of sheets in a new workbook, customer name, and Start screen.

Formulas

All options for controlling calculation, error-checking rules, and formula settings. Note that options for multi threaded calculations are currently considered obscure enough to be on the Advanced tab rather than on the Formulas tab.

Proofing

Spell-check options and a link to the Auto Correct dialog.

Save

The default method for saving, Auto Recovery settings, legacy colors, and web server options.

Advanced

All options that Microsoft considers arcane, spread among 13 headings.

Quick access tool bar

Icons to customize the Quick Access Toolbar (QAT).

customize

You can personalize your ribbon to arrange tabs and commands in the order you want them, hide or un hide your ribbon, and hide those commands you use less often. Also, you can export or import a customized ribbon.You  can re arrange ribbon menus

language

Choose the editing language, Tool Tip language, and Help language.

formulas and function

Formulas and function

Formula

A formula is an expression which calculates the value of a cell. Functions are predefined formulas and are already available in Excel.An Excel Formula is entered into a spreadsheet cell by typing in the = sign,

Formula insert

If you want in insert formula in the cell , to select the cell and go to ribbon bar click formulas to select require formula. 

Operator precedence

Excel uses a default order in which calculations occur. If a part of the formula is in parentheses, that
part will be calculated first. It then performs multiplication or division calculations. Once this iscomplete, Excel will add and subtract the remainder of your formula .

Function

Functions are predefined formulas and are already available in Excel.Formulas in Excel are useful perform various mathematical, statistical, and logical operations. You can type in a formula (though you have to be sure it's exactly right) or you can use Excel's preset formulas called functions.

Function insert

Every function has the same structure. For example, SUM(A1:A4). The name of this function is SUM.The part between the brackets (arguments) means we give Excel the range A1:A4 as input. Thisfunction adds the values in cells A1, A2, A3 and A4. It's not easy to remember which function and which arguments to use for each task. Fortunately, the Insert Function feature in Excel helps youwith this.if you enter =AVERAGE(A1:A56), that is a formula, using the AVERAGE function. If youenter =SUM(G4+A5) - 25 * MIN(B3:B6), that is a formula which uses both the SUM function and theMIN function, as well as standard mathematical operators (-, +, *).

logical function

logical function

And function

The Microsoft Excel AND function returns TRUE if all conditions are TRUE. It returns FALSE if any of the conditions are FALSE.

syntax

AND( condition1, [condition2], ... )

Parameters  Arguments

condition1

The first condition to test whether it is TRUE or FALSE.

condition2, 

Optional. Additional conditions to test whether they are TRUE or FALSE. There can be up to 30 conditions in total.

OR function

The Microsoft Excel OR function returns TRUE if any of the conditions are TRUE. Otherwise, it returns FALSE.

syntax

OR( condition1, [condition2, ... Condition n] )

Parameters

condition1

A condition to test that can either be TRUE or FALSE.

condition2, 

 Condition Optional. Conditions to test that can either be TRUE or FALSE. There can be up to 30 conditions. 

If function

The Microsoft Excel IF function returns one value if the condition is TRUE, or another value if the condition is FALSE.

Syntax

 IF( condition, [value if true], [value if false] )

parameters

condition ;

The value that you want to test.

Value if true Optional; 

It is the value that is returned if condition evaluates to TRUE.

Value if false Optional; 

It is the value that is return if condition evaluates to FALSE.

Iferror function

The Microsoft Excel IFERROR function returns an alternate value if a formula results in an error. It will check for errors such as #N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME? or #NULL.

Syntax

IFERROR( formula, alternate value )

parameter

Formula  ;

The formula or value that you want to test.

Alternate value ;

The alternate value that is returned if the formula results in an error value (#N/A, #VALUE!, #REF!, #DIV/0!, #NUM!, #NAME? or #NULL). Otherwise, the function will return the result of the formula if no error occurs.

True function

The Microsoft Excel TRUE function returns a logical value of TRUE.

Syntax

  TRUE( )

parameters

There are no parameters or arguments for the TRUE function

False function

The Microsoft Excel FALSE function returns a logical value of FALSE.

Syntax

  FALSE( )

parameters

There are no parameters or arguments for the TRUE function

Not function

The Microsoft Excel NOT function returns the reversed logical value

Syntax

  NOT( logical value )

parameters

Logical value

  An expression that either evaluates to TRUE or FALSE. If used with an expression of TRUE, then FALSE is returned. If used with an expression of FALSE, then TRUE is returned.

Text functions

Text functions

Bahttext function
    Converts a number to Thai text and adds a suffix of "Baht."
Syntax
    BAHTTEXT(number)

Number
    Required. A number you want to convert to text, or a reference to a cell containing a number, or a formula that evaluates to a number.

Char function
    Returns the character specified by a number. Use CHAR to translate code page numbers you might get from files on other types of computers into characters.
Syntax
    CHAR(number)

Number
    Required. A number between 1 and 255 specifying which character you want. The character is from the character set used by your computer.

Clean function
    Removes all non printable characters from text. Use CLEAN on text imported from other applications that contains characters that may not print with your operating system.
Syntax
    CLEAN(text)

Text
    Required. Any worksheet information from which you want to remove non printable characters.

Code function
    Returns a numeric code for the first character in a text string. The returned code corresponds to the character set used by your computer.
Syntax
    CODE(text)

Text
    Required. The text for which you want the code of the first character.

Concatenate function
     join two or more text strings into one string.
Syntax
    CONCATENATE(text1,text2,..)

Text 1
    The first item to join,The item can be number,text,or cell reference.
Text 2
    Additional text items to join. You can have up to 255 items, up to a total of 8,192 
characters

Dollar function
    The function described in this Help topic converts a number to text format and applies a currency symbol. 
Syntax
    DOLLAR(number,decimal)

Number
    Required. A number, a reference to a cell containing a number, or a formula that evaluates to a number.
Decimals
    Optional. The number of digits to the right of the decimal point. If decimals is negative, number is rounded to the left of the decimal point. If you omit decimals, it is assumed to be 2.

Exact function
    Compares two text strings and returns TRUE if they are exactly the same, FALSE otherwise. EXACT is case-sensitive but ignores formatting differences. Use EXACT to test text being entered into a document.
Syntax
    EXACT(text 1,text 2)

Text 1
    Required. The first text string.
Text 2
    Required. The second text string

Find function
    FIND  locate one text string within a second text string, and return the number of the starting position of the first text string from the first character of the second text string.
Syntax
    FIND(find_text, within_text, [start_num]

Find_text
    Required. The text you want to find.
Within_text
    Required. The text containing the text you want to find.
Start_num
    Optional. Specifies the character at which to start the search. The first character in within_text is character number 1. If you omit start_num, it is assumed to be 1.

Rounds a number to the specified number of decimals, formats the number in decimal format using a period and commas, and returns the result as text.

Fixed function

   Rounds a number to the specified number of decimals, formats the number in decimal format using a period and commas, and returns the result as text.

    FIXED(number, [decimals], [no_commas])

Number

    Required. The number you want to round and convert to text.

Decimals

    Optional. The number of digits to the right of the decimal point.

No_commas

    Optional. A logical value that, if TRUE, prevents FIXED from including commas in the returned text.

Left function

    LEFT returns the first character or characters in a text string, based on the number of characters you specify

Syntax

    LEFT(text,num chars)

Text

    Required. The text string that contains the characters you want to extract.

Num_chars 

   Optional. Specifies the number of characters you want LEFT to extract. Num_chars must be greater than or equal to zero. 

Right function

   RIGHT returns the last character or characters in a text string, based on the number of characters you specify.

Syntax

   RIGHT(text,num chars)

Text

    Required. The text string that contains the characters you want to extract.

Num_chars 

   Optional. Specifies the number of characters you want right to extract. Num_chars must be greater than or equal to zero.

Length function

    LEN returns the number of characters in a text string.

syntax
LEN(text)

Text

    Required. The text whose length you want to find. Spaces count as characters.

Lower function

    Converts all uppercase letters in a text string to lowercase.

Syntax

    LOWER(text)

Text

    Required. The text you want to convert to lowercase. LOWER does not change characters in text that are not letters.

Upper function

   Converts text to uppercase.

Syntax

   UPPER(text)

Text

    Required. The text you want to convert to uppercase. upper does not change characters in text that are not letters.

Mid function

    MID returns a specific number of characters from a text string, starting at the position you specify, based on the number of characters you specify

Syntax

     MID(text,start_ num,num_chars)

Text

    Required. The text string containing the characters you want to extract.

Start_num

    Required. The position of the first character you want to extract in text. The first character in text has start_num 1, and so on.

Num_chars

    Required. Specifies the number of characters you want MID to return from text
.

Proper function

    Capitalizes the first letter in a text string and any other letters in text that follow any character other than a letter. Converts all other letters to lowercase letters.

Syntax

    PROPER(text)

Text

    Required. Text enclosed in quotation marks, a formula that returns text, or a reference to a cell containing the text you want to partially capitalize.

Replace function

    REPLACE replaces part of a text string, based on the number of characters you specify, with a different text string

Syntax

    REPLACE(old_text, start_num, num_chars, new_text)

Old_text

    Required. Text in which you want to replace some characters.

Start_num

    Required. The position of the character in old_text that you want to replace with new_text.

Num_chars

    Required. The number of characters in old_text that you want REPLACE to replace with new_text.

Repeat function

    Repeats text a given number of times. Use REPT to fill a cell with a number of instances of a text string.

Syntax

    REPT(text, number_times)

Text

    Required. The text you want to repeat.

Number_times

    Required. A positive number specifying the number of times to repeat text

Search function

    The search functions locate one text string within a second text string, and return the number of the starting position of the first text string from the first character of the second text string.

Syntax

    SEARCH(find_text,within_text,[start_num])

find_text

    Required. The text that you want to find.

within_text

    Required. The text in which you want to search for the value of the find_text argument.

start_num

    Optional. The character number in the within_text argument at which you want to start searching.

Substitute function

    Substitutes new_text for old_text in a text string. Use SUBSTITUTE when you want to replace specific text in a text string; use REPLACE when you want to replace any text that occurs in a specific location in a text string.

Syntax

    SUBSTITUTE(text, old_text, new_text, [instance_num])

Text

    Required. The text or the reference to a cell containing text for which you want to substitute characters.

Old_text

    Required. The text you want to replace.

New_text

    Required. The text you want to replace old_text with.

Instance_num

    Optional. Specifies which occurrence of old_text you want to replace with new_text. If you specify instance_num, only that instance of old_text is replaced. Otherwise, every occurrence of old_text in text is changed to new_text.

T function

    Returns the text referred to by value.

Syntax

    T(value)

Value 

    Required. The value you want to test.

Text function

    The Microsoft Excel TEXT function returns a value converted to text with a specified format.

Syntax

    TEXT(value ,format)

value

     The value to convert to text

Format

     The format use to display the result.
    

value function

   Converts a text string that represents a number to a number.

Syntax

    VALUE(text)

Text

    Required. The text enclosed in quotation marks or a reference to a cell containing the text you want to convert.