CP Lab : Experiment 4

EXPERIMENT NO 4

Aim:  Customizing quick access tool bar for excel, working with spreadsheet shortcuts

Objectives:

  • Understand structure of MS Excel workbook, Understand and Demonstrate common shortcuts
  • Understand and Demonstrate common shortcuts of MS Excel

Hardware requirements: Any CPU with Pentium Processor or similar, 256 MB RAM or more, 1 GB Hard Disk or more.

Software requirements:  Windows 64 bit / Ubuntu 14 Operating System, MS office 2010 or later, Open Office for Ubuntu

Theory: A spreadsheet is essentially a matrix of rows and columns. Consider a sheet of paper on which horizontal and vertical lines are drawn to yield a rectangular grid. The grid namely a cell, is the result of the intersection of a row with a column. Such a structure is called a Spreadsheet. MS-Excel is the most powerful spreadsheet package brought by Microsoft. The three main components of this package are

  • Electronic spreadsheet
  • Database management
  • Generation of Charts.

Though the spreadsheet packages were originally designed for accountants, they have become popular with almost everyone working with figures. Sales executives, book-keepers, officers, students, research scholars, investors bankers etc, almost any one find some form of application for it.

  • The alphabets A,B… are known as columns
  • The rows are numbered as 1,2,3…
  • Sheet1,Sheet2, Sheet3 are known as worksheet tabs

The Microsoft Quick Access Toolbar (QAT) is a toolbar menu that appears in Microsoft Excel, Microsoft Word, and other Microsoft Office products in the top left corner of the window. The QAT starts out as a tiny toolbar with Save, Undo, and Redo. It is initially located above the File tab in the ribbon. If you start using the QAT frequently, you can right-click the toolbar and choose Show Quick Access Toolbar Below the Ribbon.

Adding Icons to the QAT: The drop-down at the right side of the QAT, offers 12 popular commands you might choose to add to the Quick Access Toolbar. Choose a command from this list to add it to the QAT.

When you find a command in the ribbon you are likely to use often, you can easily add the command to the QAT. To do so, right-click any command in the ribbon and select Add to Quick Access Toolbar. Items added to the Quick Access Toolbar using the right-click method are added to the right side of the QAT.

Removing Commands from the QAT: You can remove an icon from the QAT by right-clicking the icon and selecting Remove from Quick Access Toolbar.

Customizing the QAT: You can make minor changes to the QAT by using the context menus, but you can have far more control over the QAT if you use the Customize command. Right-click the QAT and select Customize Quick Access Toolbar to display the Quick Access Toolbar section of the Excel Options dialog

 5 Reasons Why You Should Be Using Keyboard Shortcuts

  1. Efficiency: It is generally acknowledged by computer pro’s that you can increase your productivity and accomplish more tasks by using the keyboard instead of mouse.
  2. Multitasking: When you use the keyboard you don’t have to follow a pointer to see what you are doing and you can do thing semi-automatically thus freeing your mind for other tasks
  3. Complementary: You can combine the advantages of using the keyboard with those of using the mouse. There are tasks that are much easier done with the mouse, take the example of browsing the web pages and clicking links but at the same time you can use the keyboard to navigate within the page or between tabs
  4. Health issues: Extensive mouse usage is associated with RSI (Repetitive Syndrome Injury) much often than the keyboard usage. By alternating mouse and keyboard usage you can reduce the risks of RSI
  5. Precision: When you have a job that requires precision it is advisable that you use the keyboard, for example if you do a lot of text editing, it is more accurate to handle it through shortcut keys.

Following are some shortcut keys of MS Excel.

Ctrl combination shortcut keys

Key Description
Ctrl+PgDn Switches between worksheet tabs, from left-to-right.
Ctrl+PgUp Switches between worksheet tabs, from right-to-left.
Ctrl+Shift+$ Applies the Currency format with two decimal places (negative numbers in parentheses).
Ctrl+Shift+: Enters the current time.
Ctrl+Shift+Plus (+) Displays the Insert dialog box to insert blank cells.
Ctrl+Minus (-) Displays the Delete dialog box to delete the selected cells.
Ctrl+; Enters the current date.
Ctrl+1 Displays the Format Cells dialog box.
Ctrl+2 Applies or removes bold formatting.
Ctrl+3 Applies or removes italic formatting.
Ctrl+4 Applies or removes underlining.
Ctrl+5 Applies or removes strikethrough.
Ctrl+9 Hides the selected rows.
Ctrl+0 Hides the selected columns.
Ctrl+A ·         Selects the entire worksheet.

·         If the worksheet contains data, Ctrl+A selects the current region. Pressing Ctrl+A a second time selects the entire worksheet.

·         When the insertion point is to the right of a function name in a formula, displays the Function Arguments dialog box.

·         Ctrl+Shift+A inserts the argument names and parentheses when the insertion point is to the right of a function name in a formula.

Ctrl+B Applies or removes bold formatting.
Ctrl+C Copies the selected cells.
Ctrl+D Uses the Fill Down command to copy the contents and format of the topmost cell of a selected range into the cells below.
Ctrl+E Invoke Flash Fill to automatically recognize patterns in adjacent columns and fill the current column
Ctrl+F Displays the Find and Replace dialog box, with the Find tab selected.
Ctrl+G Displays the Go To dialog box.
Ctrl+H Displays the Find and Replace dialog box, with the Replace tab selected.
Ctrl+I Applies or removes italic formatting.
Ctrl+K Displays the Insert Hyperlink dialog box for new hyperlinks or the Edit Hyperlink dialog box for selected existing hyperlinks.
Ctrl+N Creates a new, blank workbook.
Ctrl+O ·         Displays the Open dialog box to open or find a file.

·         Ctrl+Shift+O selects all cells that contain comments.

Ctrl+P Displays the Print tab in Microsoft Office Backstage view.
Ctrl+Q Displays the Quick Analysis options for your data when you have cells that contain that data selected.
Ctrl+R Uses the Fill Right command to copy the contents and format of the leftmost cell of a selected range into the cells to the right.
Ctrl+S Saves the active file with its current file name, location, and file format.
Ctrl+U ·         Applies or removes underlining.

·         Ctrl+Shift+U switches between expanding and collapsing of the formula bar.

Ctrl+V Inserts the contents of the Clipboard at the insertion point and replaces any selection. Available only after you have cut or copied an object, text, or cell contents.
Ctrl+W Closes the selected workbook window.
Ctrl+X Cuts the selected cells.
Ctrl+Y Repeats the last command or action, if possible.
Ctrl+Z Uses the Undo command to reverse the last command or to delete the last entry that you typed.

TIP: The Ctrl combinations Ctrl+J and Ctrl+M are currently unassigned shortcuts.

Function keys

Key Description
F1 ·         Displays the Excel Help task pane.

·         Ctrl+F1 displays or hides the Ribbon.

·         Alt+Shift+F1 inserts a new worksheet.

F4 ·         Repeats the last command or action, if possible.

·         Ctrl+F4 closes the selected workbook window.

·         Alt+F4 closes Excel.

F12 Displays the Save As dialog box.

Other useful shortcuts

Key Description
Alt ·         Displays the Key Tips (new shortcuts) on the Ribbon.

For example,

·         Alt, W, P switches the worksheet to Page Layout view.

·         Alt, W, L switches the worksheet to Normal view.

·         Alt, W, I switches the worksheet to Page Break Preview view.

Arrow Keys Ctrl+Arrow Key moves to the edge of the current data region in a worksheet.
End ·         If the cells are blank, pressing End followed by an arrow key moves to the last cell in the row or column.

·         Ctrl+End moves to the last cell on a worksheet, to the lowest used row of the rightmost used column. If the cursor is in the formula bar, Ctrl+End moves the cursor to the end of the text.

Enter ·         Alt+Enter starts a new line in the same cell.

·         Ctrl+Enter fills the selected cell range with the current entry.

·         Shift+Enter completes a cell entry and selects the cell above.

Esc ·         Cancels an entry in the cell or Formula Bar.

·         Closes an open menu or submenu, dialog box, or message window.

Page Down ·         Alt+Page Down moves one screen to the right in a worksheet.

·         Ctrl+Page Down moves to the next sheet in a workbook.

·         Ctrl+Shift+Page Down selects the current and next sheet in a workbook.

Page Up ·         Alt+Page Up moves one screen to the left in a worksheet.

·         Ctrl+Page Up moves to the previous sheet in a workbook.

·         Ctrl+Shift+Page Up selects the current and previous sheet in a workbook.

Spacebar ·         Ctrl+Spacebar selects an entire column in a worksheet.

·         Shift+Spacebar selects an entire row in a worksheet.

·         Ctrl+Shift+Spacebar selects the entire worksheet.

Tab ·         Shift+Tab moves to the previous cell in a worksheet or the previous option in a dialog box.

·         Ctrl+Tab switches to the next tab in dialog box.

·         Ctrl+Shift+Tab switches to the previous tab in a dialog box.

Conclusion: In this experiment, we have studied basic structure of MS Excel and demonstrated QAT and keyboard short-cuts of MS Excel.

R.No Name of Student Date of Performance Date of Evaluation Grade Sign of student Sign of Faculty
             

Leave a Reply

Your email address will not be published. Required fields are marked *