Microsoft Excel

•   By default file name – Book1

◦         Extension name - .xlsx

•   By default font name – Calibri

•   By default font size – 11

◦         Minimum – 8

◦         Maximum – 11

•   By default zooming – 100%

◦         Minimum – 10%

◦         Maximum – 400%

•   Row – 1048576 (A,B,C,…..)

◦         It is horizontal (left to right)

◦         Go to last row – Ctrl + down arrow

•   Column – (XFD) 16384 (1,2,3,…)

◦         It is vertical (up to down)

◦         Go to last column – Ctrl + right arrow

•   By default sheet – 3

◦         Maximum sheet – 255

◦         Insert worksheet – Shift + F11

•   Insert cell – Ctrl + +

•   Delete cell – Ctrl + -

•   Insert date – Ctrl + ;

•   Insert time – Ctrl + Shift + :

•   Active cell – F2 / Double click

•   Row height – By default 14.4

•   Column width – By default 8.11

•   Total number of character in one cell – 32767

Important notes in Home menu

Clipboard group/block -

•       Cut – Ctrl + X

•       Copy – Ctrl + C

•       Paste – Ctrl + V

•       Format painter – Ctrl + Shift + C

Font -

•       Font name – By default Calibri

•       Font size – By default 11

◦         Minimum font size – 8

◦         Maximum font sizr – 72

•       Increase font size

•       Decrease font size

•       Bold – Ctrl + B

•       Italic – Ctrl + I

•       Underline – Ctrl + U

•       Insert border

•       Fill color – Color the background of selected cells

•       Font color – Change the text color

Alignment -

•       Alignments -

◦         Top align

◦         Middle Align

◦         Bottom align

◦         Align text left

◦         Center

◦         Align text right

•       Orientation – Rotate text to a diagonal angle or vertical orientation.

•   Decrease Indent – (Ctrl + Alt + Shift + Tab) Decrease the margin between the border and the text in the cell.

•   Increase Indent – (Ctrl + Alt + Tab) Increase the margin between the border and the text in the cell.

•   Wrap text – Make all content visible within a cell by displaying it on multiple lines.

•   Merge & Center – Joins the selected cells into one larger cell and centers the contents in the new cells.

◦         Merge & Center

◦         Merge Across

◦         Merge cells

◦         Unmerge cells

Number -

•   Number Format – Choose how the values in a cell are displayed : as a percentage, as currency, as a date or time, etc.

•   Accounting Number Format – Choose an alternte currency format for the selected cell.

•   Percent Style - (Ctrl + Shift + %) Display the values of the cell as a percentage.

•       Comma Style – Display the value of the cell with a thousands separator.

•   Increase Decimal – Show more precise values by showing more decimal places.

•   Decrease Decimal – Show less precise values by showing fewer decimal places.

Styles -

•   Conditional Formatting – Highlight interesting cells, emphasize unusual values, and visulaize data using Data Bars, Color Scales, and Icon Sets based on criteria.

•   Format as Table – Quickly format a range of cells and convert it to a Table by choosing a pre – defined table Style.

•       Cell Styles – Quickly format a cell by choosing from pre – defined styles.

Editing -

•       Insert Cells – Insert cells, rows, or columns into the sheet or table.

•       Delete Cells – Delete rows or columns form the table or sheet.

•   Format – Change the row height or column width, organize sheets or protect or hide cells.

•   Sum - ( Alt + = ) Display the sum of the selected cells directly after the selected cells.

•       Fill – You can fill cells in any direction and into any range of adjecent cells.

•   Clear – Delete everything ftom the , or selectively remove the formatting, the contents, or the comments.

•   Sort & Filter – Arrange data so that it is easier to analyze. You can sort the selected data in ascending or descending order, or you can temporarily filter out specific values.

•   Find & Select – Find and select specific text, formatiing, ir type of information within the workbook. You can also replace the information with new text or formatting.

Important notes in Insert Menu

Tables -

•   Pivot Table – Click here to summarize data using a Pivot Table or to inset a Pivot Chart. It make it easy to arrange and summarize complicated data and drill dowm on details.

•   Table - ( Ctrl + T ) Tables make it easy to sort, filter, and format data within a sheet.

Illustrations -

•       Picture – Insert Picture from a file.

•   Clip Art – Insert Clip Art into the document, including drawing, movies, sounds, or stock photography to illustrate a specific concept.

•   Shapes – Insert ready–made shapes, such as rectangles, circles, arrows, lines, etc.

•       SmartArt – Insert a SmartArt graphic to visually communicate information.

Charts -

•   Charts – Insert a charts such as Column, Line, Pie, Bar, Area, Scatter, Other Charts.

Links -

•   Hyperlink – (Ctrl + K) Create a link to a Web page, a picture, an e-mail address, or a program.

•       Text Box – Inset a text box that can be positioned anywhere on the page.

•   Header & Footer – The information in the Header or Footer will appear at the top or bottom of each printed page.

•       WordArt – Insert decorative text in your document.

•   Signature Line – Insert a signature line that specifies the indiviual who must sign.

•       Object – Insert an embedded object.

•   Symbol – Insert characters that are not on your keyboard, such as copyright symbols, trademark symbols, paragraph marks and unicode characters.

Important notes in Page Layout

Themes -

•   Themes – Change the overall design of the entire document, including colors, fonts, and effects.

•       Themes Colors – Change the colors for the current theme.

•       Theme Fonts – Chage the fonts for the current theme.

•       Theme Effects – Change the effects for the current theme.

Page Setup -

•   Margins – Select the margin sizes for the entire document or the current section.

•   Page Orientation – Switch the pages between portrait and landscape layouts.

•   Page Size – To apply a specific paper sixe to all sections in the document, click more paper sizes.

•       Print Area – Mark a Specifix area of the sheet for printing.

•   Breaks – Specify where a new page will begin in the printed copy. Page breaks are inserted above and to the left of the selection.

•       Background – Choose an image to display as the background of the sheet.

•       Print Titles – Specify rows and coloumns to repeat on each printed page.

Scale to Fit -

•   Width – Shrink the width of printed output to fit a maximum number of pages.

•   Height – shrink the height of printed output to fit a maximum number of pages.

•   Scale – The maximum width and height must be set to “Automatic” to use this feature.

Sheet Options -

•   Gridlines -

◦         View – These lines will not print unless print is also checked.

◦         Print – Print the lines between rows and columns in the sheet to make reading easier.

•       Headings-

◦         View – Show rows and columns headings.

◦         Print – Print rows and columns headings.

Arrange -

•   Bring to Front – Front of all other object so that no part of it is hidden behind another object.

•       Send to back – Send the selected object behind all other object.

•   Selection Pane – Show the selection pane to help select individual objects and to change their order and visibility.

•       Align – Align the edges of multiple selected objects.

•   Group – Group objects together so that they can be treated like a single object.

•       Rotate – Rotate or flip the selected object.

Important notes in Formulas

Function Library -

•       Insert Function – Shift + F3

•   AutoSum – Display the sum of the selected cells directly after the selected cells.

•       Recently used – Browse and select from a list of recently use functions.

•       Financial – Browse and select from a list of financial functions.

▪         ACCRINT

▪         ACCRINTM

▪         AMORDEGRC

▪         AMORLINC

▪         COUPDAYBS

▪         COUPDAYS

▪         COUPDAYSNC

▪         COUPNUM

▪         COUPPCD

▪         CUMIPMT

▪         CUMPRINC

▪         DB

▪         DDB

▪         DISC

▪         DOLLARDE

▪         DOLLARER

▪         DURATION

▪         EFFECT

▪         FV

▪         FVSCHEDULE

▪         INTRATE

▪         IPMT

▪         IRR

▪         ISPMT

▪         MDURATION

▪         MIRR

▪         NOMINAL

▪         NPER

▪         NPV

▪         ODDFPRICE

▪         ODDFVIELD

▪         ODDLPRICE

▪         ODDLYIELD

▪         PMT

▪         PPMT

▪         PRICE

▪         PRICEDISC

▪         PRICEMAT

▪         PV

▪         RATE

▪         RECEIVED

▪         SLN

▪         SYD

▪         TBILLEQ

▪         TBILLPRICE

▪         TBILLYIELD

▪         VDB

▪         XIRR

▪         XNPV

▪         YIELD

▪         YIELDDISC

▪         YIELDMAT

•       Logical - Browse and select from a list of logical functions.

◦         AND

◦         FALSE

◦         IF

◦         IFERROR

◦         NOT

◦         OR

◦         TRUE

•       Text - Browse and select from a list of text functions.

▪         BAHTEXT

▪         CHAR

▪         CLEAN

▪         CODE

▪         CONCATENATE

▪         DOLLAR

▪         EXACT

▪         FIND

▪         FIXED

▪         LEFT

▪         LEN

▪         LOWER

▪         MID

▪         PROPER

▪         REPLACE

▪         REPT

▪         RIGHT

▪         SEARCH

▪         SUBSTITUTE

▪         T

▪         TEXT

▪         TRIM

▪         UPPER

▪         VALUE

•       Date & Time - Browse and select from a list of date & time functions.

▪         DATE

▪         DATEVALUE

▪         DAY

▪         DAYS360

▪         EDATE

▪         EOMONTH

▪         HOUR

▪         MINUTE

▪         MONTH

▪         NETWORKDAYS

▪         NOW

▪         SECOND

▪         TIME

▪         TIMEVALUE

▪         TODAY

▪         WEEKDAY

▪         WEEKNUM

▪         WORKDAY

▪         YEAR

▪         YEARFRAC

•   Lookup & Reference - Browse and select from a list of Lookup & Reference functions.

▪         ADDRESS

▪         AREAS

▪         CHOOSE

▪         COLUMN

▪         COLUMNS

▪         GETPIVOTDATA

▪         HLOOKUP

▪         HYPERLINK

▪         INDEX

▪         INDIRECT

▪         LOOKUP

▪         MATCH

▪         OFFSET

▪         ROW

▪         ROWS

▪         RTD

▪         TRANSPOSE

▪         VLOOKUP

•   Math & Trig - Browse and select from a list of math and trigonometry functions.

▪         ABS

▪         ACOS

▪         ACOSH

▪         ASIN

▪         ASINH

▪         ATAN

▪         ATAN2

▪         ATANH

▪         CEILING

▪         COMBIN

▪         COS

▪         COSH

▪         DEGREES

▪         EVEN

▪         EXP

▪         FACT

▪         FACTDOUBLE

▪         FLOOR

▪         GCD

▪         INT

▪         LCM

▪         LN

▪         LOG

▪         LOG10

▪         MDETERM

▪         MINVERSE

▪         MMULT

▪         MOD

▪         MROUND

▪         MULTINOMIAL

▪         ODD

▪         PI

▪         POWER

▪         PRODUCT

▪         QUOTIENT

▪         RADIAND

▪         RAND

▪         RANDBETWEEN

▪         ROMAN

▪         ROUND

▪         ROUNDDOWN

▪         ROUNDUP

▪         SERIESSUM

▪         SIGN

▪         SIN

▪         SINH

▪         SORT

▪         SQRTPI

▪         SUBTOTAL

▪         SUM

▪         SUMIF

▪         SUMIFS

▪         SUMPRODUCT

▪         SUMSQ

▪         SUMX2MY2

▪         SUMX2PY2

▪         SUMXMY2

▪         TAN

▪         TANH

▪         TRUNC

•   More Functions – Browse ans select form lists of statistical, engineering, cube and information functions.

◦         Sataistical -

•       AVEDEV

•       AVERAGE

•       AVERAGEA

•       AVERAGEIF

•       AVERAGEIFS

•       BETADIST

•       BETAINV

•       BINOMDIST

•       CHIDIST

•       CHINV

•       CHITEST

•       CONFIDENCE

•       CORREL

•       COUNT

•       COUNTA

•       COUNTBLANK

•       COUNTIF

•       COUNTIFS

•       COVAR

•       CRITBINOM

•       DEVSQ

•       EXPONDIST

•       FDIST

•       FINV

•       FISHER

•       FISHERINV

•       FORECAST

•       FREQUENCY

•       FTEST

•       GAMMADIST

•       GAMMAINV

•       GAMMALN

•       GEOMEAN

•       GROWTH

•       HARMEAN

•       HYPGEOMDIST

•       INTERCEPT

•       KURT

•       LARGE

•       LINEST

•       LOGEST

•       LOGINV

•       LOGNORMDIST

•       MAC

•       MAXA

•       MEDIAN

•       MIN

•       MINA

•       MODE

•       NEGBINOMDIST

•       NORMDIST

•       NORMINV

•       NORMSDIST

•       NORMSINV

•       PEARSON

•       PERCENTILE

•       PERCENTRANK

•       PERMUT

•       POISSON PROB

•       POSSION

•       PROB

•       QUARTILE

•       RANK

•       RSQ

•       SKEW

•       SLOPE

•       SMALL

•       STANDARDIZE

•       STDEV

•       STDEVA

•       STDEVP

•       STDEVPA

•       STEVX

•       TDIST

•       TINV

•       TREND

•       TRIMMEAN

•       TTEST

•       VAR

•       VARA

◦         Engineering -

•       BESSELI

•       BESSELJ

•       BESSELK

•       BESSELY

•       BIN2DEC

•       BIN2HEX

•       BIN2OCT

•       COMPLEX

•       CONVERT

•       DEC2BIN

•       DEC2HEX

•       DEC2OCT

•       DELTA

•       ERF

•       ERFC

•       GESTEP

•       HEX2BIN

•       DEX2DEC

•       HEX2OCT

◦         Cube -

•       CUBEKPIMEMBER

•       CUBEMEMBER

•       CUBEMEMBERPROPERTY

•       VARP

•       VARPA

•       WEIBULL

•       ZTEST

•       IMABS

•       IMGINARY

•       IMARGUMENT

•       IMCONJUGATE

•       IMCOS

•       IMDIV

•       IMEXP

•       IMLIN

•       IMLOG10

•       IMLOG2IMPOWER

•       IMPRODUCT

•       IMREAL

•       IMSIN

•       IMSQRT

•       IMSUB

•       IMSUM

•       OCT2BIN

•       OCT2DEC

•       OCT2HEX

•       CUBERANKEDMEMBER

•       CUBSET

•       CUBESETCOUNT

•       CUBEVALUE

◦         Information -

•       CELL

•       ERROR.TYPE

•       INFO

•       ISBLANK

•       ISERR

•       ISERROR

•       ISEVEN

•       ISLOGICAL

•       ISNA

Defined Names -

•       ISNOTEXT

•       ISNUMBER

•       ISODD

•       ISREF

•       ISTEXT

•       N

•       NA

•       TYPE

•   Name Manager – (Ctrl + F3) Create, edit, delete and find all the names used in the workbook.

•   Define Name – Name cells so that you can refer to them in formulas by that name.

•   Use in Formula – Choose a name used in this workbook and insert it into the current formula.

•   Create from Selection – (Ctrl + Shift + F3) Automatically generate names from the selected cells.

Formula Auditing -

•   Trace Precedents – Show arrows that indicate what cells affect the value of the currently selected cell.

•   Trace Dependents – Show arrows that indicate what cells are affected by the value of the currently selected cell.

•   Remove All Arrows – Remove the arrows drawn by Trace Precedents or Trace Dependents.

•   Show Formulas – (Ctrl + ‘) - Display the formaula in each cell instead of the resulting value.

•       Error Checking – Check for common errors that occur in formulas.

•   Evaluate Formula – Launch the Evalute Formula dialog box to debug a formula by evaluating each part of the formula individually.

•   Watch Window – Minitors the values of certain cells as changes are made to the sheet.

Calculation -

•       Calculation Options – Specify when formulas are calculated.

•       Calculate Now - ( F9 ) Calculate the entire workbook now.

•       Calculate Sheet – (Shift +F9) Calculate the current sheet now.

Important notes in Data Menu

Get External Data -

◦         From Access – Import data from a Microsoft Access database.

◦         From Web – Import data from a web page.

◦         From Text – Import data from a text file.

◦         From Other Sources – Inport data from other data sources.

◦         Existing Connections – Connect to an extrenal data source by selecting from a list of commonly used sources.

Connections -

•   Refresh All – (Ctrl + Alt + F5) Update all the information in the workbook that is coming from a data source.

•       Connections – Display all data connections for the workbook.

•   Properties – Specify how cells connneted to a data source will update, what contents form the source will be contents from the source will be displayed, and how chnages in the number of rows or coluns in the data source will be hanled in the workbook.

•   Edit Links – View all of the other files this speadsheet is linked to so that ou can update or remove the links.

Sort & Filter -

•   Sort – Launch the sort dialog box to sort data based on several criteria at once.

•       Filter - (Ctrl + Shift + L) Enables filtering of the selected cells.

•       Clear – Clear the filter and sort state for the current range of data.

•       Reapply – (Ctrl + Alt + L) Reapply the filter and sort in the current range.

•   Advanced – Specify complex criteria to limit which records are included in the result set of a query.

Data Tools -

•   Text to Columns – Separate the contents of one Excel cell into separate columns.

•       Remove Duplicates – Delete duplicate rows from a sheet.

•       Data Validation – Prevent invalid data from being entered into a cell.

•       Consolidate – Combine values from multiple ranges into one new range.

•       What-if Analysis – Try out various values for the formulas in the sheet.

◦         Scenario Manager – It allows you to create and save different groups of values, or scenarios, and switch between them.

◦         Goal Seek – It will find the right input when you know the result you want.

◦         Data Tables – It allow you to see the results of many different possible inputs at the same time.

Outline -

•   Group – (Shift + Alt Right) A range of cells together so that they can be collapsed or expanded.

•   Ungroup – (Shift + Alt + Left) Ungroup a range of cells that were previously grouped.

•   Subtotal – Total several rows of related data together by automatically inserting subtotals and totals for the selected cells.

•       Show Detail – Expand a collapsed group of cells.

•       Hide Detail – Collapse a group of cells.

Proofing -

Important notes in Review

•       Spelling – (F7) Check the spelling of text.

•   Research – (Alt + Click) Open the Research task pane to search through reference materials, such as dictionaries, encyclopedias, and tranlation services.

•   Thesaurus – Suggests other words with a similar meaning to the word you have selected.

•       Translate – Translate the selected text into a different language.

Comments -

•       Comment – (Shift + F2) Add a comment about the selection.

•       Delete Comment – Delete the selected comment.

•       Previous – Select the previous comment in the sheet.

•       Next – Navigate to the next comment in the document.

•   Show/Hide Comment – Show or hide the comment attached to the selected cells.

•       Show All Comments – Displaly all comments in the sheet.

•       Show Ink – Show or hide any ink annotations on the sheet.

Changes -

•   Protect Sheet – Prevent unwanted changes to the data in a sheet by specifying what information can be changed.

•   Protect Workbook – Restrict access to the workbook by preventing new sheets from being created or by granting access only to specific people.

•   Share Workbook – Allow multiple people to work in a workbook at the same time.

•   Protect and Share Workbook – Share the workbook and protect it with a password at the same time.

•   Allow users to edit ranges – Allows specific people to edit ranges of cells in a protected workbook or sheet.

•   Track Changes – Track all changes made to the document, including insertings, deletions and formatting changes.

Important notes in View Menu

Worknook views -

•       Normal – View the document in Normal view.

•   Page Layout view – View the document as it will appear on the printed page.

•   Page Break Preview – View a preview of where pages will break when this document is printed.

•       Custom view – Save a set of display and print setting as a custom view.

•       Full Screen – View the document in full screen mode.

Show/Hide -

•   Ruler – View the rulers, used to measure and line up objects in the document.

•       Grindlines – These lines will not print unless print is also checked.

•   Message bar – Open the message bar to complete any required actions on the document.

•   Formula bar – View the formula bar, in which you can enter text and formulas into cells.

•       View Headings – Show row and columns headings.

Zoom -

•   Zoom – Open the zoom dialog box to specify the zoom level of the document.

•       100% - Zoom the document to 100% of the normal size.

•   Zoom to selection – Zoom the worksheet so that the currentlly selected range of cells fills the entire window.

Links -

•   New Window – Open a new window containing a view of the current document.

•       Arrange All – Tile all open program windows side-by-side on the screen.

•   Freeze Panes – Keep a portion of the sheet visible while the rest of the sheet scrolls.

•   Split – Split the window into multiple resizable panes containing views of your worksheet.

•       Hide Window – Hide the current window so that it cannot be seen.

•   Unhide Window – Unhide any windows hidden by the hide window feature.

•   View Side by Side – View two worksheets side-by-side so that you can compare their contents.

•   Synchronous Scrolling – Synchronize the scrolling of two documents so that they scroll together.

•   Reset Window Position – Reset the window position of the documents being compared side-by-side so that they share the screen equally.

•   Save Workspace – Save the current layout of all windows as a workspace so that it can be restored later.

•       Switch Windows – Switch to a different currently open window.

Macros -

•   Macros – (Alt + F8) View the list of macros, from which you can run, create or delete a macro.