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

        =IF(AND(I48="Pass",H48>=60),"A Grade",IF(AND(I48="Pass",H48>=50),"B          Grade",IF(AND(I48="Pass",H48>=40),"C Grade","Nil")))

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,…)

14.   To add a Data Form follow the steps-

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