﻿

# Learn EXCEL exercises with solutions

Télécharger Learn EXCEL exercises with solutions

#### Formation Excel en ligne par vidéo

205 leçons vidéos + 20 Livres PDF + 20 TP + Sous supervision + Certificat de réussite à la fin du cours

★★★★★★★★★★3.5 étoiles sur 5 basé sur 1 votes.
Votez ce document:

### Télécharger aussi :

Excel Exercise Solution

# Day 1

To start Excel 2010  go to Start ? All Programs ? Microsoft Office ? Microsoft Excel 2010

Or

Press Window +R  for Run Command and type Excel then click on OK or press Enter key

Or

If Desktop Shortcut is available, then double click on Excel icon

Or

Press window key form keyboard and type Excel then press Enter key.

Or

We can assign and use short-cut key to Open Excel Application                                    Or

We can pin in Taskbar / Start Menu and we can start from there by clicking.

1.      (a) Press CTRL +Navigation Key (Right)

(b)  Press CTRL + Navigation key (down)

(c)   Press F5 or Ctrl + G for go to command or go to Home tab ? Editing Group ? Find            ? Go to ? (it will launch a dialog box), enter the cell name in reference field ?                      press Enter Key or Clickon OK.

(d)  Type the Cell name in Name Box and press Enter Key.

(e)  In name box type A1 ? press Enter Key                  or         press CTRL+Home Key.

2.      To prepare the given data sheet (Sales report) enters the values in respective cells ? apply formatting as required.

*   To merge cells select the cell range ? Home Tab ? Alignment group ? Merge & Centre

*   To set the Row height or Column width, select row/column ? Home tab ? Cells group  ?                  Format Command ? Row Height / Column Width ? provide value in points as  required.

*   For Bold select the cells or cells values ? press CTRL +B or CTRL+2 or Home Tab ? Font group

? Bold

3.      To prepare the given data sheet (Student report) follow the above steps of point no. 2

4.      To rename the Sheet steps are-

Right click on Sheet name ? Rename ? type name as you want       or

Double Click on Sheet name ? enter the name as you desired.

5.      Step 1: To create a folder in D:\STUDENTS location steps are-

Start ? Computer ? D drive ? STUDENTS ? press CTRL+Shift+N to create a folder and type          folder name

Or

use New Folder command to create a folder and provide folder name

Step 2: Now come to Excel and go to File tab ? Save ? select Location ? type the file name as          given (Excel Practice) ?Save.

6.      Step 1: To open a file press CTRL +O  or  CTRL + F12 or File Tab ? Open ? specify file name ?       Open

Step 2: To navigate between Sheets you can directly click on sheet name or use keyboard shortcuts-

CTRL+ Page Down             :              Next Sheet

CTRL+ Page Up                   :              Previous Sheet

7.      To auto fit for Columns select the columns ? Home tab? Cells group ? Auto Fit Column

Or

Double click between columns name.

8.      To merge the cells, select cell range ? Home tab ? Alignment Group ? Merge & Center

9.      To change or apply cell formatting select the cells ?Home tab ? Cells group ? Format ? Format Cells or press Ctrl+1 ? (it will launch a dialog box) select Category and related things. It will display Sample of selected category. ? Click on OK to apply the selected format (Refer the figure).

CTRL + SHIFT + ~               :  General Format

CTRL + SHIFT + 1               :  Number Format

CTRL + SHIFT + 2               :  Time Format

CTRL + SHIFT + 3               :  Date Format

CTRL + SHIFT + 4               :  Currency Format

CTRL + SHIFT + 5               :  Percentage Format

CTRL + SHIFT + 6               :  Scientific Formats

10.     Step 1: To insert a new sheet press Shift+F11 or right click on sheet tab ?Insert command.

Step 2: Then prepare a data sheet as given (Employee Table).

Note: Make sure that the system date format is dd-mm-yyyy, otherwise enter the date values in          system date format only. To change Date format (like Location, Currency), go to Control Panel          ? Region and Language Settings

11.     To apply cell format as Date follow point no. 9.

12.     To apply cell format as Currency follow point no. 9

13.     To rename the Sheet follow “Day 1” point no. 4.

14.     Step 1: To apply border select the cells ? Home tab ? Font group ? border.

Step 2: To fill a pattern select cells and go to Home tab ?Cells group ? Format ? Cell Format ?          Fill ? select a pattern which you want to fill (refer the below figure).

15.     Step 1: To prepare a table style go to Home tab ?Styles group ? Format as Table ? New Table  Style (it will launch New Table Quick Style dialog Box).

Step 2: Here specify the name for new table style and format it as given in exercise by clicking on          Format button available in same dialog box and save it.

16.     To apply a table style select the cell range and go to Home tab ? Format as Table ? Select your Style to apply.

17.     To apply the format painter steps are –

Step 1: Select the table employee ? Home tab ? Clipboard group ? click on Format Painter icon.

The moment you will click on Format Painter your Pointer will turn into Brush.

Step 2: Now drag over the cells where you want to apply.

Note: (i) To apply format painter in multiple cells or cell ranges, double click on format painter(after  applied press ESC button).

(ii) To clear styles select Student table and go to Home Tab ? Editing group ? Clear ? Clear

Formats.

18.     To clear the style, select the table and go to Design Tools Tab ? Table style group ? Clear

Or

Select  Go to Home Tab ? Editing group ? Clear ? Formats

19.     Step 1: To fill series, Enter initial value in  cell and select it.

Step 2: Home Tab ? Editing group ? Fill          ?Series ? Select Series in i.e.  Columns or Row ? set Step Value            and Stop Value as required ? OK. Note: For auto fill, just enter two values  and select cells, then using Fill handle drag it.

20.     Step 1: Select sheet Day 1 ? select the cells range which containing Online marks

Step 2: Home tab ? Styles ? Conditional Formatting ?Highlight Cell Rules ? Greater Than ? specify the value then select Custom format  (it will launch Format Cell dialog box)

Step 3: Select formatting as required ? OK ? OK

21.     To apply conditional formatting steps are-

Step 1: select the cell range (as shown in below figure) ? Home tab  ? Styles group ?Conditional         Formatting ? New rules ? Use Formulae to determine which cell to format (refer to below         figure)

Step 2: Enter the formula with relative reference ? select Format as required ? OK ? OK

22.     Create both tables as given.

23.     Type heading for table 5 as given.

24.     Step 1: Select Total sales values from table 4 and copy it.

Step 2: Now select Total sales values from table 5? press ALT+CTRL+V for paste special command. Step 3: under Paste select Values as well as under Operation select Add operation ? OK. (Refer the below figure)

25.     To transpose steps are-

Step 1: copy the table ? select location (any blank cell) for paste Step 2: press ALT+CTRL+V ? enable Transpose check box ? OK.

26.     To paste as a link steps are - select data and copy ? select the location ? now right click ? Paste Special ? Other paste options ? Paste Link

Or

Now right click ? Paste Option ?click on Paste Link icon.

Note: In case of link, if original data will change, linked data will auto update.

# Day 2

1.    Step 1: Start Excel and open file excel practice ?Select sheet Day 1

Step 2: Tocalculate difference between Actual Qty and Plan Qty use formulae (refer the below figure).

2.    Press Shift+F11 to insert new sheet and rename it as Day 2.

3.    Step 1: Prepare Commission sheet as given in exercise.

Step 2: To calculate commission enter formulae as shown in below figure and fill the remaining. To use Absolute Reference Press F4

4.    To prepare cross table write formulae in Cell B2 as shown below and drag the same to fill the entire table.

5.    Go to sheet Day1

a.    To calculate Sum of Actual Qty for Monitor Category use function  =SUMIF()   as shown in following figure-

b.    To count the number of Products use function  =COUNTA() as shown below -

=COUNTA(A3:A9)

c.    To count the number of Monitors use function =COUNTIF() as shown below-

=COUNTIF(B3:B9,"Monitor")

d.    To count the blank cells in given cell range use function =COUNTBLANK() as below syntax-

=COUNTBLANK(cell Range)

6.    Prepare the table as given.

7.    To calculate total, average and Result refer the below figure

a. For calculating total, use Sum() function as below

=SUM(C48:F48)               and drag the same till last record.

b.    For calculating average, use Average() function as below

=AVERAGE(C48:F48)      and drag the same till last record.

c.    For Rank calculation use the following syntax

=RANK(G48,\$G\$48:\$G\$54)                       and drag the same till last record.

d.    For calculating Result, use IF(), And() function as below

=IF(AND(C48>=40,D48>=40,E48>=40,F48>=40),"Pass","Fail")     and drag the same till last record.

8.    Prepare the table Number as given in exercise and then use functions to get result as shown in below figure-

# Day 3

1.    Create the table as given.

2.    Step 1: To create the descriptive statistics, go to Excel Options ? Add-Ins ? Go ? Select Analysis

Tool Pack as shown below ? OK.

Step 2: Select the table   ? go to Data Tab    ? Analysis Group   ? Data Analysis ? select Descriptive analysis as shown below   ? OK.

Step 3: now provide input as shown in the dialog box and click on OK option.

Result will be as following-

3.    To calculate the employee table fields refer the following-

a.For Basic calculation-

=IF(AND(D2="HOD",E2="ITT"),45000,IF(AND(D2="HOD",NOT(E2="ITT")),35000,IF(AND(D

2="Staff",E2="ITT"),30000,23000)))

Or

=IF(AND(Designation ="HOD", Dept="ITT"),45000,IF(AND(Designation ="Staff",

Dept="ITT"),30000,IF(Designation ="HOD",35000,23000)))

Note: In place of Designation and Dept please pass cell reference.b. For DA calculation-

=IF(D2="HOD",F2*0.15,F2*0.12)

c.    For TA calculation-

=IF(AND(D2="HOD",OR(E2="ITT",E2="ACCOUNTS")),F2*0.1,0)

d.    For Special allowance calculation-

# =IF(AND(D2="HOD",C2<DATE(2010,1,1)),2500,0)

e. For Gross calculation - =sum(F2:I2)

?            For PF calculation -

=(F2+G2)*12.5%

f.    For Annual Net Sal -

=(Gross – PF)*12          i.e.        =(J2-K2)*12

g.     For Tax calculation -

=IF(L2>1000000,(L2-1000000)*0.3+125000,IF(L2>500000,(L2500000)*0.2+25000,IF(L2>250000,(L2-250000)*0.1,0)))

Or

# =IF(L2<=250000,0,IF(L2<=500000,(L2-250000)*0.1,IF(L2<=1000000,(L2500000)*0.2+25000,(L2-1000000)*0.3+125000)))

4.    Go to sheet Day2 -Table 8 and to calculate  grade use the following syntax

5.    Step 1: Insert a new sheet and rename it Sales-Report.

Step 2: Now make same heading as heading or field name given in sales report table.

The data in sheet Day 1 is as following-

Step 3: Now in sheet Sales-Report select Cell B2 and enter lookup function as shown in following

figure--

Formula for Plan Qty. is                =VLOOKUP(\$A\$2,\$A\$2:\$E\$9,4,FALSE)

## Day 4

1.    Step 1: To open a new workbook press CTRL+N or go to File Tab ? New ?                   select Blank Workbook  ?Create

Step 2: Enter the data as given in exercise in Sheet1, Sheet2, and Sheet3.

Step 3: To get consolidated report select a cell in Sheet4 (insert new sheet if required). Now follow the steps-

(i)      Data Tab ?Data tools group ? Consolidate

(ii)       Now add references from Sheet1, Sheet2, and Sheet3 one by one ?

check on check boxes for Top Row, Left column and Create Links to source data ? OK

(refer the following figure)

2.    To trace Dependents for Gross of third employee, select that cell and go to Formulae Tab ? Formulae Auditing Group ? Trace Dependents.

3.    To trace Precedents for Gross of third employee select that cell and go to Formulae Tab ? Formulae Auditing Group ? Trace Precedents.

4.    To remove formulae from a cell, copy the formulae and paste it as Value.

5.    To use Goal Seek follow the steps- Data ?What if Analysis ? Goal Seek

In Goal Seek Dialog Box do the following-

Set Cell:                       Select Net Salary cell of first employee

To Value:                     Enter a new value i.e. (current cell value +500)

By Changing Cell:     Select basic cell for first employee and then click on OK Button. It will calculate and                                    display the updated value.

Note: (i)       For using Goal Seek, To Value cell must be formulated including changing cell

(ii)          By Changing Cell must be a constant (it should not be a formulae)

6.    Select entire table, copy it and go to new location (i.e. new sheet or new cell in the same sheet), then go to Paste Special ? select Option Values ? OK.

a.    To sort the records Employee name wise steps are- Select any Employee name Sort A to Z.

Or

Select any Employee name ?Data tab ? Sort & Filter group ? click on   icon or Sort

b.    To sort the records Net salary wise steps are-

Select any Net salary ? Home Tab ? Editing group ? Largest to Smallest.

Note: For multilevel sorting in excel, select the data and use custom sort command from editing group of Home Tab. It will launch Sort dialog box. Here select the fields in given order and also select the sorting order. If you are not getting Fields name then enable Check Box My data has headers.

c.    To calculate Dept wise Net salary do the following-

i.     Sort the data Dept wise

ii.      Select the whole data with heading

iii.    Use Subtotal function from Outline group of Data Tab.  iv.    At each changes: Dept

v.       Use function: Sum

vi.     Add subtotal to: Annual Net Sal

vii.      Now click on Ok button.

It will group the data and provide group wise sum with Grand Total for selected Field.

7.    Prepare the table Party Outstanding Reports as given in exercise and format it accordingly.

a.    To concatenate texts use concatenate function or & operator as shown in following figure.

b.    Copy the above table and paste it in new location and use text functions to convert in upper and proper casein new Location (Cell), Syntax is -

=PROPER(Text)                       or   =PROPER(Cell ref.)

=UPPER(Text)                          or   =UPPER(Cell ref.)

c.    To compare two texts use function Exact.      Syntax is:             =EXACT(text1,text2)

8.    For Data validation -

a)    To apply Data validation which accept only values, steps are-

Select cell range A1:A5 ? Data tab ?Data Tools group ? Data validation ? Setting ?

Allow:          List

Source:          CPT, PCC, IPCC, FINAL  ? Ok (refer the below figure).

b)   Select cell range B1:B5 ? Data tab ?Data Tools group ? Data validation ? Setting ?

Allow:         Whole number

Data:            Between

Minimum:  500

Maximum:  5000    ? Ok.

c)    Select cell range C1:C5 ? Data tab ? Data Tools group ? Data validation ? Setting ?

Allow:          Date

Data:            Less than

End Date:  03/31/2013 (date should entered in date format)? OK.

d)   Select cell range D1:D5 ? Data tab ?Data Tools group ? Data validation ? Setting ?

Allow:          Text Length

Data:            equal to

Length:        5             ? OK.

9.    To analyze the data using Scenario Manager follow the steps-

Step1: Calculate the sales, Expenses and Profit as per given Expected rate. (Refer the formulae in                below figure to calculate).

Step2: Select the cells D2:E3 and Go to Data tab ? Data tools ? What If Analysis ? Scenario

Manager (it will launch a dialog box)

Step3: Now click on Add button

Scenario name:                                best

Changing cells:                                  D3:E3 (for storing values as shown in below figure) ? OKà

Step4: Enter the new value for this scenario as given in exercise and click on Add button to add more  scenarios.

Step 5: Repeat step 4 for Average and Bad ? OK.

Note:  (i)            To view the different result use show command from scenario manager dialog box

(ii)           If required you can insert a summary of recorded scenario by using Summary       command from the same dialog box. It will insert the scenario Summary in new sheet. For                resultant cell select Expected in 2014 i.e. A7:B9 cell range.

10.   Step 1: To calculate loan sheet prepare table as given in following figure-

Step 2: Now use financial functions as shown in below figure.

11.   To prepare a data table follow the steps-

Step 1: Enter the given data in excel sheet as below-

Step 2: Now calculate the EMI using PMT function in cell B6 i.e.

=PMT(B2/12,B3,-B1)

Step 3: Select the cell range B5:F6 and go to Data Tab ?

Data tools group ? What if Analysis ? Data        Table. It will launch a Data table dialog box.  Step 4: Here for Row input cell: select cell B3 as shown in        figure and click on OK button.

NoteA part of data table can’t be change.

12.   To find the date difference use =datedif() function. Refer the following figure-

The function syntax is :

=DATEDIF(FirstDate,SecondDate,"Interval")

Where, FirstDate:         This is the earlier of the two dates.

SecondDate:     This is the more recent of the two dates.

"Interval”:         This indicates what you want to calculate.

These are the available intervals.

 "d" Days between the two dates. "m" Months between the two dates. "y" Years between the two dates. "yd" Days between the dates, as if the dates were in the same year. "ym" Months between the dates, as if the dates were in the same year. "md" Days between the two dates, as if the dates were in the same month and year.

Note: If required to display you can use concatenate function or operator to display as a sentence. Output will be as following-

13.   Please refer the above point no. 11.

To calculate total cost, Syntax is

=product(value1, value2, value3,…)

Step 1: File ? Options ? Quick Access Toolbar ? Commands Not in Ribbon  ? Form ? Add>> ? OK

Step 2: Select employee data including Fields name ? Quick Access Toolbar ?Form (form will appear          like below figure)

Step 3: Here using New command you can enter data into excel sheet. Using Data Form Deletion will          be permanent.

15.   Step 1: Go to Home tab ? Find & Select ? Formulae, (it will select all cells those contain formulae)  Step 2:  Go to format cell dialog box by pressing CTRL+1 ?select protection Tab ?enable Hidden option? OK.

Note: it will be effective only after protecting sheet.

16.   To protect sheet go to Home tab ? Cells group ? Format ? Protect Sheet ? entre password              to unprotect (optional).

Or

Review Tab ? Changes Group ? Protect Sheet

17.   To protect entire workbook go to Review Tab ? Changes Group ? Protect workbook.

Note: Password is optional to unprotect the same.

## Day 5

1.    To insert a Pivot table report steps are-

Step 1: Select data including headings? Insert Tab ? Table group ? Pivot Table. It will launch a dialog box as below figure.

Step 2: In the above dialog box data range already selected. Now specify the location where you want      to place the report ? OK

Step 3: Now from Pivot table field List you can choose fields which you want to add in report as shown  in above figure.

2.    To filter the records do the following steps-

Step 1: Select  Heading  ? Home Tab  ? Sort & Filter ? Filter                      Or Data Tab ? Filter

It will add filter to all selected Fields.|

Step 2: click on Filter Button of product  ?  select Computer.

Step 3: click on Filter Button of Value ?  Number Filer ? set criteria

or

To apply Advance filter do the following-

Step 1:  go to Data Tab ? Advanced ?  it will launch Advance filter dialog box.

Step 2: Select List range (entire data) & criteria range as shown below ? OK

3.    (a)  To record a macro steps are-

Step 1: Select given sheet ? View Tab ? Macros group ? Record Macro (It will launch record  macro dialog box)

Step 2:  In this dialog box provide a macro name as well as set shortcut key as you desired  (make sure          that key has not been assigned earlier) ? OK

Step 3: The moment you will click on OK button, recording will start. Now insert a Pie Chart for  given  data and then Stop recording.

Note:     (i)             Macro will run in Macro enabled workbook only otherwise enable the related setting       from Options. It’s better to record a macro in new workbook and save it as a Macro                          enabled workbook (.xlsm).

(ii)       To enable Macros steps are -

File ? Options ? Trust Center ? Trust Center settings ?

Macro Settings ? Enable all Macros ? OK

(iii)      While naming the macro take care of following points- ?            The name does not begin with a letter or an underscore

•      Space or other invalid characters are not allowed in name

•      The name conflicts with an Excel built-in name or the name of another object        in the workbook

(b)  To run macro steps are-

View Tab ? Macros ? View Macros ? select the Macro to run ? click on Run Button

Or

you can press keyboard shortcut which is already assigned to that macro

Or

Press CTRL+F8 to view macro ? now select the macro and click on Run Command.

4.    Step 1: Go to the “Day 1” worksheet in your workbook.

Step 2: Select a cell from where you want to split it ? View Tab ? Window group ? Split (It will split window in multiple resizable Panes).

5.    To remove the split option, steps are-

View Tab ? Window group ? click on Split icon.

6.    To apply Freeze Panes steps are-

Select cell as given ? View Tab ? Window group ? Freeze Panes ( It will freeze the above rows and left columns from selected cell).

7.    To unfreeze panes click on Freeze panes icon. It will unfreeze if already freeze.

8.    To insert Sparkline steps are-

Step 1: Select the output cell ? Insert Tab ? Sparkline Group ?use Line or Column any.

Step 2: Now select the data range (all subject marks for individual) ? OK.

9.    Step 1: Select the Employee data ?Page Layout Tab ? Page Setup group ? Print area ? Set Print

Area

Step 2: File ? Print          or press CTRL+F2 for print preview.

10.   To set header and footer in Excel steps are-

Step 1: Page Layout Tab ? Page Setup group ? launch this group.

Step 2: In page seetup dialog box select Header/ Footer group ? Custom Header and set header as  given in exercise. Do the same for Footer also.

11.   To set paper size, orientation and margins you can use page setup window as shown in above figure of Point no. 10 of Day 5

Or

Page Layout Tab ? Page Setup group ? select commands as required and set it.

12.   Open a workbook containing some data.

a)    To repeat row / column in sheets for printing go to Page Layout Tab ? Page Setup group ? Print titles. Here select the row / columns to repeat.

b)   To fit contents in a single page if exceeds one or two row / columns for printing use command Print under file tab ? Scaling ? Fit sheet on One page (refer the below figure).

c)    To move or copy sheet in existing or other workbook steps are- Right click on sheet ?Move or Copy ?select the Book where you want to place it as well as position ? OK  Note : It will Move the sheet. If you want to copy it then enable checkbox for Create a copy

d)   To search from entire workbook go to Home tab ? Editing group ? find & select ? Find

Or

Press CTRL+F ? Options ? Within : workbook.

e)   To work with comments in excel go to Review Tab ? Comments Group and use command as you required. To insert comment in cells you can right click and use command Insert comment.

f)     To insert multiple cells, select the cells and press CTRL++ and choose the direction for shift cell.

To insert multiple rows/columns select multiple row/column and press CTRL++

g)    To insert an object in worksheet go to Insert Tab ? Text group ? Object.

h)   To define a name select the range and type name in name box.

Or

Select the range and go to Formulae Tab ? Define Names group ? Define name.

i)     To enable Track Changes in excel go to Review Tab ? Changes group ? Protect and Share workbook ? Enable the option: Sharing with track changes (refer the below figure).

j)     For multicolumn sorting use Custom Sort command from Sort & Filter of Editing group of Home tab.

## Shortcut keys

(a)    Function Keys

 Shortcut key Functionality F1 Displays the Microsoft Office Excel Help task pane F2 Edits the active cell, putting the cursor at the end F3 Displays the Paste Name dialog box F4 Repeats the last command or action, if possible F5 Displays the Go To dialog box F6 Switches between the worksheet, Ribbon, task pane, and Zoom controls. In a worksheet that has been split, it also includes the split panes. F7 Displays the Spelling dialog box F8 Turns extend mode on or off F9 Calculates all worksheets in all open workbooks F10 Turns key tips on or off F11 Creates a chart (on a chart sheet) using the highlighted range F12 Displays the Save As dialog box

(b)   SHIFT + Function Keys

 Shortcut key Functionality Shift + F2 Inserts or edits a cell comment Shift + F3 Displays the Insert Function dialog box Shift + F4 Repeats the last Find, the same as Find Next Shift + F5 Displays the Find dialog box Shift + F6 Switches between the worksheet, Zoom controls, task pane, and Ribbon Shift + F8 Enables the user to add a nonadjacent cell or range to a selection of cells by using the arrow keys Shift + F9 Calculates the active worksheet Shift + F10 Displays the (Shortcut) menu for the selected item Shift + F11 Inserts a new worksheet Shift + F12 Displays the Save As dialog box

(c)    CTRL + Function Keys

 Shortcut key Functionality Ctrl + F1 Displays or hides the Ribbon Ctrl + F2 Displays the Print Preview window Ctrl + F3 Displays the Name Manager dialog box Ctrl + F4 Closes the selected workbook window Ctrl + F5 Restores the window size of the selected workbook window Ctrl + F6 Moves to the next open workbook or window Ctrl + F7 Performs the Move window command when the window is not maximized Ctrl + F8 Performs the Size window command when the window is not maximized Ctrl + F9 Minimizes the workbook window to an icon Ctrl + F10 Maximizes or restores the selected workbook window Ctrl + F11 Inserts a new macro sheet Ctrl + F12 Displays the Open dialog box

(d)   Other + Function    keys

 Shortcut key Functionality Alt + F1 Creates a chart (on a chart sheet) using the highlighted range Alt + F2 or Alt + Shift + F2 Displays the Save As dialog box Alt + F4 or Alt + Shift + F4 Closes all the workbooks (saving first) and exits Excel Alt + F8 Displays the Macro dialog box Alt + F11 Toggles between the Visual Basic Editor window (in which one can create a macro using VBA) and the Excel window Alt + Ctrl + F9 Calculates all worksheets in all open workbooks Alt + Shift + F1 Inserts a new worksheet Ctrl + Shift + F3 Displays the Create Names from Selection dialog box Ctrl + Shift + F12 Displays the Print dialog box

(e)    CTRL + Number Keys

 Shortcut key Functionality Ctrl + 0 Hides the selected columns Ctrl + 1 Displays the Format Cells dialog box Ctrl + 2 Toggles bold on the current selection Ctrl + 3 Toggles italics on the current selection Ctrl + 4 Toggles underlining on the current selection Ctrl + 5 Toggles the strikethrough of text on the current selection Ctrl + 6 Alternates between hiding objects, displaying objects, and displaying place- holders for objects Ctrl + 8 Toggles the display of outline symbols Ctrl + 9 Hides the selected rows

(f)  CTRL + Alphabet Keys

 Shortcut key Functionality Ctrl + A Selects the entire worksheet Ctrl + B Toggles bold on the current selection Ctrl + C Copies the current selection to the clipboard Ctrl + D Copies the contents and format of the first cell in the selection downwards Ctrl + F Displays the Find dialog box Ctrl + G Displays the Go To dialog box Ctrl + H Displays the Replace dialog box Ctrl + I Toggles italics on the current selection Ctrl + K Displays the Insert Hyperlink dialog box for new hyperlinks or the Edit Hyperlink dialog box for selected existing hyperlinks Ctrl + L Displays the Create Table dialog box Ctrl + N Creates a new blank workbook Ctrl + O Displays the Open dialog box Ctrl + P Displays the Print dialog box Ctrl + R Copies the contents and format of the leftmost cell in the selection to the right Ctrl + S Saves the active file Ctrl + U Toggles underlining on the current selection Ctrl + V Pastes the contents of the clipboard at the insertion point Ctrl + W Closes the selected workbook window Ctrl + X Cuts the current selection to the clipboard Ctrl + Y Repeats the last command or action, if possible Ctrl + Z Undo the last command or action

(g)   Other Shortcuts

 Shortcut key Functionality Ctrl + Shift + A Inserts argument names and parentheses when the insertion point is to the right of a function name in a formula Ctrl + Shift + F or Ctrl + Shift + P Opens the Format Cells dialog box with the Font tab selected Ctrl + Shift + O Selects all the cells with comments Enter Enters the contents of the active cell and moves to the cell below Shift + Enter Enters the contents of the active cell and moves to the cell above

 Tab Enters the contents of the active cell and moves one cell (unlocked in a protected worksheet) to the right Shift + Tab Enters the contents of the active cell and moves one cell (unlocked in a protected worksheet) to the left Alt + = Enters the SUM() function in the active cell Alt + 0128 Enters the euro symbol (€) (using Number keypad) Alt + 0162 Enters the cent symbol (¢) (using Number keypad) Alt + 0163 Enters the pound sign symbol (£) (using Number keypad) Alt + 0165 Enters the yen symbol (¥) (using Number keypad) Alt + Enter Enters a new line (or carriage return) into a cell Ctrl + ' Enters the formula from the cell directly above into the active cell Ctrl + ; Enters the current date into the active cell Ctrl + Enter Enters the contents of the active cell to the selected cell range Ctrl + Shift + : Enters the current time into the active cell Shift + Insert Enters the data from the clipboard Alt + Down Arrow Displays a drop-down list of the values in the current column of a range row Esc Cancels the cell entry Ctrl + Tab Switches to the next tab in a dialog box Ctrl + Shift + Tab Switches to the previous tab in a dialog box Home Moves to the first column in the current row End + Arrow Key Move by one block of data within a row or column Page Down Moves one screen down in a worksheet Page Up Moves one screen up in a worksheet Alt + Page Down Moves one screen to the right in a worksheet Alt + Page Up Moves one screen to the left in a worksheet Ctrl + Home Moves to the beginning (cell ?A1?) of a worksheet Ctrl + End Moves to the last cell on a worksheet, in the lowest used row of the rightmost used column Ctrl + Arrow Key Moves to the edge of the current data region Ctrl + Page Up Moves to the previous worksheet in a workbook Ctrl + Page Down Moves to the next worksheet in a workbook Scroll Lock + Arrow Key Moves the workbook window by one cell in the corresponding direction Scroll Lock + End Moves to the last cell in the current workbook window Scroll Lock + Home Moves to the first cell in the current workbook window Scroll Lock + Page Down Moves you down one screen (current selection unchanged) Scroll Lock + Page Up Moves you up one screen (current selection unchanged) Ctrl + . Moves clockwise to the next corner within a selected range Ctrl + Alt + Left Arrow Switches to the next non-adjacent selection to the left Ctrl + Alt + Right Arrow Switches to the next non-adjacent selection  to the right = Starts a Formula Ctrl + ` Alternates between displaying cell values and displaying formulas in the worksheet Ctrl + Delete Deletes text to the end of the line Ctrl + Insert Copies the current selection to the clipboard Ctrl + Shift + ( Unhides any hidden rows within the selection Ctrl + Shift + ) Unhides any hidden columns within the selection Alt Shows key tips Alt + Shift + Left Arrow Displays the Ungroup dialog box Alt + Shift + Right Arrow Displays the Group dialog box Alt + Backspace Undo the last action Alt + Spacebar Displays the control menu for the Microsoft Office Excel window Alt + - (from the Numeric keypad) Displays the Excel application control menu End Moves to the cell in the lower-right corner of the window when SCROLL LOCK is turned on. Delete Deletes the selection or one character to the right Backspace Deletes the selection or one character to the left Shift + Delete Cuts the selection to the clipboard Ctrl + - Displays the Delete dialog box Ctrl + Shift + = Displays the Insert dialog box Ctrl + Backspace Scrolls to display the active cell Ctrl + Shift + U Switches between expanding and collapsing of the formula bar

(h)   Selecting data

 Shortcut key Functionality Ctrl + \ Selects the cells in a selected row that do not match the value in the active cell Ctrl + Shift + \ Selects the cells in a selected column that do not match the value in the active cell Ctrl + / Selects the array containing the active cell Alt + ; Selects the visible cells in the current selection Ctrl + Shift + * Selects the current region around the active cell (surrounded by blank rows and columns). In a PivotTable, it selects the entire PivotTable report. Ctrl + [ Selects all the cells that are directly referred to by the formula in the active cell (precedents) Ctrl + Shift + [ Selects all the cells that are directly (or indirectly) referred to by the formula in the active cell Ctrl + ] Selects all the cells that directly refer to the active cell (dependents) Ctrl + Shift + ] Selects all the cells that directly (or indirectly) refer to the active cell Ctrl + Shift + Page Selects the active worksheet and the one after it Down Ctrl + Shift + Selects the active worksheet and the one before it PageUp Ctrl + Shift + Selects all the objects on a worksheet when an object is selected or selects the Spacebar entire worksheet Ctrl + Spacebar Selects an entire column in a worksheet Shift + Spacebar Selects an entire row in a worksheet Shift + Arrow Selects the active cell and the cell in the given direction keys Shift + Backspace Selects the active cell when multiple cells are selected

(i)  Extending data

 Shortcut key Functionality Ctrl + Shift + Arrow Key Extends the selection to the last non-blank cell in the same column or row as the active cell, or if the next cell is blank, extends the selection to the next non-blank cell Ctrl + Shift + End Extends the selection to the last used cell on the worksheet Ctrl + Shift + Home Extends the selection to the beginning of the worksheet Shift + Arrow Key Extends the selection by one cell in that direction Shift + Home Extends the selection to the first column Shift + Page Down Extends the selection down one screen Shift + Page Up Extends the selection up one screen End, Shift + Arrow Key Extends the selection to the next non-blank cell in that direction

(j)  Formatting data

 Shortcut key Functionality Alt + ' Displays the Style dialog box Ctrl + Shift + ! Applies the Number format with two decimal places, thousands separator, and minus sign (-) for negative values Ctrl + Shift + \$ Applies the Currency format with ?\$? sign and two decimal places (negative numbers in parentheses) Ctrl + Shift + % Applies the Percentage format with no decimal places Ctrl + Shift + ^ Applies the Exponential number format with two decimal places Ctrl + Shift + ~ Applies the General number format Ctrl + Shift + @ Applies Time format with the hour and minute, and AM or PM Ctrl + Shift + # Applies the Date format "dd-mmm-yy" to the selection Ctrl + Shift + & Applies the outline border to the selected cells Ctrl + Shift + _ Removes the outline border from the selected cells

All the very best J

29