Monday, February 26, 2018

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.

Sunday, December 10, 2017

Excel Anatomy- Part01 workbook,worksheet and cells

Work book, work sheet, cells

What is Workbook ?

A workbook is a file that contains one or more worksheets, to help you organize your data. You can create a workbook from a blank workbook or a template. Press CTRL+N to quickly create a new workbook.

In Microsoft Excel a workbook is a collection of one or more spreadsheets, also called worksheets in a single file. Below is an example of a spreadsheet called "Sheet 1" in an Excel workbook file called "Book 1." Our example also has the "Sheet 2" and "Sheet 3" sheet tabs that are also part of the same workbook.

What is Worksheet?

An 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 by 16,384 columns.

There can be many sheets in your Excel document and you can see the sheets listed as tabs along the bottom of your document. We can insert as many worksheets as you want to quickly insert a new worksheet, click the plus sign at the bottom of the document window.

If you want more worksheets select file tab click excel option to general and select to include many new sheets change.
File > options > general > include many new sheets

If you want to move the work sheet, to select right click move to sheet position.

Press ctrl + page down to move the worksheet. Move to the sheet1 from sheet5 to select sheet 1 tab right click on the mouse to select move and click OK.





What is cell?

A cell is the intersection between a row and a column on a spreadsheet that starts with cell A1. Below is an example of a highlighted cell ; the cell address, cell name, or cell pointer "D8" (column D, row 8) is the selected cell and the location of what is being modified.

Active cell is surround by bold layer

Vertical columns are numbered with alphabetic values such as A, B, C. Horizontal rows are numbered with numeric values such as 1, 2, 3.A cell can only store 1 piece of data at a time. you can store data in a cell such as a formula, text value, numeric value, or date value.

Wednesday, October 9, 2013

VBA String Functions - Part 6

LTRIM Function (VBA)

In Microsoft Excel, the LTRIM function removes leading spaces from a string.

Syntax

LTRIM( text )
text is the string that you wish to remove leading spaces from.

VBA Function Example

The LTRIM function can only be used in VBA code in Microsoft Excel. Here are some examples of what the LTRIM function would return:

LTRIM("  Google com") would return "Google com"
LTRIM("  Alphabet") would return "Alphabet"
LTRIM("  Alphabet  ") would return "Alphabet  "


Dim sResult As String
sResult = LTrim("   Alphabet   ")

The variable sResult would now contain the value of "Alphabet  ".



VBA String Functions - Part 7

INSTR Function (VBA)

In Microsoft Excel, the INSTR function returns the position of the first occurrence of a substring in a string.

Syntax

The syntax for the INSTR function is:  

InStr( [start], string, substring, [compare] )
start is optional. It is the starting position for the search. If this parameter is omitted, the search will begin at position 1.

string is the string to search  within.

substring is the substring that you want to find.

VBA Function Example

The INSTR function can only be used in VBA code in Microsoft Excel. Here are some examples of what the INSTR function would return:

InStr(1, "President of India", "of") would return 11
InStr("President of India", "India") would return 14
InStr(5, "President of India", "e") would return 7


Dim iPosition As Integer 
iPosition = InStr(5, "President of India", "e")

In this example, the variable called iPosition would now contain the value 7.



VBA String Functions - Part 5

LEFT Function (VBA)

In Microsoft Excel, the LEFT function allows you to extract a substring from a string, starting from the left-most character.

Syntax

The syntax for the LEFT function is:

LEFT( text, [number_of_characters] )
text is the string that you wish to extract from.

number_of_characters is optional. It indicates the number of characters that you wish to extract starting from the left-most character. If this parameter is omitted, only 1 character is returned.

VBA Function Example

Dim sResult As String
sResult = Left("Alphabet",3)

The variable sResult would now contain the value of "Alp".



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, alignme...