Microsoft Excel
This tutorial will help you to use Microsoft excel with screen reader. You will be learning various important topics along with the keyboard shortcuts.
We don’t have information about the person who had prepared this brief tutorial. If you are the original person who created this brief tutorial and want credit or if you want to takedown this tutorial from this site, then please let us know.
Email:
ar****************@gm***.com
If you want to share similar kind of things through this site then please email us to
ar****************@gm***.com
introduction:
Microsoft Excel refers to a spreadsheet application program in which data can be stored, organized, analyzed and manipulated. It is developed and configured by the Microsoft corporation so as to provide support for the Microsoft Windows and MAC operating systems.
Shortcuts to go to different main Tabs in Microsoft Excel Application:
Each Tab is having upper ribbon and lower ribbon. The lower ribbon contains different options. Press down arrow to move to lower ribbon and press either Tab or press left and right arrow keys to move different options relating to particular Tab.
Following are the short cuts:
A). Home Tab – – Press Alt and H.
B). Insert Tab – – Press Alt and N.
C). Page Layout Tab – – Press Alt and P.
D). Formulas Tab – – Press Alt and M.
E). Data Tab – – Press Alt and A.
F). Review Tab – – Press Alt and R.
G). View Tab – – Press Alt and W.
Navigate ribbon tabs
The ribbon tabs are the main menu bar of Excel. To reach the ribbon tabs, press F6 until you hear “Ribbon tabs,” followed by the current tab’s name. With JAWS, you hear “Upper ribbon.” To move between the tabs, use the Left and Right arrow keys. When you reach a tab, a tab-specific ribbon appears below it. To learn how to browse the selected ribbon, go to Navigate the ribbon.
Here’s a list of the most common tabs and some examples of what you can do on each tab:
- Home: Format and align text and numbers, and add new rows and columns.
- Insert: Insert tables, pictures, shapes, and charts into your worksheet.
- Page Layout: Set the margins, orientation, and size of the worksheet page.
- Formulas: Add various functions and formulas to your worksheet.
- Data: Import data from various sources, sort and filter it, and use data tools such as removing duplicate rows.
- Review: Check the spelling and accessibility of your worksheet, and collaborate with others using comments and notes.
- View: Select a view such as Normal View or Page Layout view, and set the page zoom level.
- Help: Open the Microsoft Excel Help, contact support, and leave feedback.
In addition to the ribbon tabs, you need to access the File menu for some important commands. To open it, press Alt+F. The File menu opens in a new pane. To navigate the main commands, use the Up and Down arrow keys, then use the Tab key and Up and Down arrow keys to navigate the options for that command.
In the File menu, you can start a new workbook, open an existing workbook, save, share, or print the file you’re currently working with, and access Excel options. To close the File menu and return to your worksheet, press Esc.
Navigate the ribbon
After navigating to the right ribbon tab as described in Navigate the ribbon tabs, press the Tab key to move to the ribbon and browse its commands and options. You can press Shift+Tab to move backwards. Press Enter to make a selection or press Esc to leave the ribbon and return to your worksheet.
Tip: It is often faster to use keyboard shortcuts to access the commands and options on each ribbon. For more information, go to Keyboard shortcuts in Excel for Windows.
Navigate the worksheet
When you open an Excel workbook, the focus is on the worksheet table grid. If you have moved the focus out of the worksheet, press F6 until your screen reader announces a table grid cell location. Here’s how you navigate inside the worksheet and between other sheets and workbooks:
To move between cells in the table grid, use the arrow keys. Your screen reader announces the column and row of each cell as well as its contents.
To open the context menu for the current cell, press Shift+F10. Use the Up and Down arrow keys to navigate the menu, and press Enter to make a selection or press Esc to return to the worksheet.
To move to the next or previous worksheet in your workbook, press F6 until you hear the name of the current sheet tab, use the Left and Right arrow keys to find the right sheet, and press Enter to select it.
To switch to the next workbook when more than one workbook is open, press Ctrl+F6. Your screen reader announces the name of the workbook.
SHORTCUT COMMANDS:
- Move to prior screen in spreadsheet:
ALT+PAGE UP
- Move to next screen in spreadsheet:
ALT+PAGE DOWN
- Next Sheet:
CTRL+PAGE DOWN
- Prior Sheet:
CTRL+PAGE UP
- Move down to the edge of current data region:
CTRL+DOWN ARROW
- Move up to the edge of current data region:
CTRL+UP ARROW
- Move left to the edge of current data region:
CTRL+LEFT ARROW
- Move right to the edge of current data region:
CTRL+RIGHT ARROW
Selection Keystrokes
- Select column line:
CTRL+SPACEBAR
- Select row line:
SHIFT+SPACEBAR
- Select Data Region:
CTRL+SHIFT+8
- to select data cells horizontally (starting cell to ending cell):
Keep the cursor at starting cell, then hold Control, shift and right arrow.
- to select data cells vertically (starting cell to ending cell):
Keep the cursor at starting cell, then hold Control, shift and down arrow.
- to select entire sheet:
Control and A.
- Remove Selection to current cell:
SHIFT+BACKSPACE
OTHER SHORTCUTS:
Formula input mode:
Place the cursor in blank cell and press = key.
AutoFilter:
To activate auto filter first select entire column line or row line, then press Alt A T and to remove auto filter, first select whole sheet then press Alt A and T or Control Shift and L.
AutoSum:
ALT+= and give enter.
Insert Date:
CTRL+; (SEMICOLON)
Insert Time:
CTRL+SHIFT+; (SEMICOLON).
To insert new blank column, press Alt I and C.
To insert new blank row, press Alt I and R.
To change to ascending order (a to z), first select the data cells starting cell to ending cell, then press Alt H, S and S.
To change to descending order (z to a), first select the data cells starting cell to ending cell, then press Alt H, S and O.
To go to number page directly in format cells dialogue box, press Alt H, F and M.
To go to alignment page directly in format cells dialogue box, press Alt H, F and A.
To go to font page directly in format cells dialogue box, press Control Shift and F.
To go to format cells dialogue box where different tabs available like number page, alignment page, font page, border page, fill page and protection page, press Control and 1. (After giving Control 1 command, to move one page tab to next page tab press control and tab to check all page tabs).
To delete columns and rows, place the cursor at column line or row line, then press Alt H and D or Alt E and D and again choose the required option by pressing down arrow or up arrow and give enter.
To go to format menu options: Press Alt H and O
Data sheet formatting:
Alt H and O (After giving Alt H and O there are many options available. Press tab to check all options like (auto fit column width Alt H, O and I, auto fit row height Alt H, O and H, lock cells Alt H, O and L, protect sheet Alt H, O and P, rename sheet Alt H, O and R, move or copy sheet Alt H, O and M etc.).
To go to fill sub-menu (SERIES), press ALT H, F, I and S or press Alt E, I and S
The main purpose of series option is to enter the series of numbers automatically.
To go to Freeze pane option, press ALT W AND F i.e. is in view menu.
This option is used for displaying the headings of first row over the page. To activate auto filter directly press ALT A and T. Then press ALT DOWN ARROW to open autofilter dialogue box.
This option is used to view the data using different criteria. We can also specify our own criteria’s to get the data in various context.
To activate data validation dialogue box, press ALT A AND V.
To merge cells directly, first select the cells then press ALT H, M, M or to merge in center Alt H, M and C and to unmerge the cells press Alt H, M and U.
Move or copy sheet: Alt E and M or Alt H, O and M.
Inserting new worksheets: Alt I and W and Shift F11.
To delete entire column, entire row or to delete shift cells left radio button checked, press Alt E and D.
Protect the sheet, press Alt R, P and S or Alt H, O and P.
Rename the sheet, press Alt H, O and R.
To go to function library, where all excel functions available, go to formulas tab by pressing Alt M and F or press shift F3. (here we can find all formulas . search the formulas in search box and type the formula to find description of particular formula. Like that we can find many formulas from this option).
Important functions in Excel:
1. For adding numbers the SUM function is used in three methods.
The formula’s is written as follows:
Type 1:
Example: –
If the digits are written from a1 to e1 or e1 to m1 or a1 to a10 or b1 to b10. After entering the digits in the cells, place the cursor in the blank cell. Then type the formula as follows:
Syntax:
=sum(a1:e1) and give enter. Then press up arrow to know the answer. To check the given formula press F2 in function keys or press Control and Graph key.
Type 2:
Example: –
If the digits are written from a1 to a10 or a2 to g2, the formula is as follows:
Syntax:
=a1+a2+a3+a4+a5+a6+a7+a8+a9+a10 and give enter. Then press up arrow to know the answer.
=a2+b2+c2+d2+e2+f2+g2 and give enter. Then press up arrow to know the answer. To check the given formula press F2 in function keys or press Control and Graph key.
Type 3:
Example :-
If the digits are written from a1 to desired range like [a1 to a10 continuously] or a1 to desired range like [a1 to j1 continuously], the formula is as follows:
Syntax:
Press Alt equals directly and then give enter. Then press up arrow to know the answer. To check the given formula press F2 in function keys or press Control and Graph key.
2. For subtracting numbers in range of desired cells.
If the numbers are written in the cells, the formula is as follows:
Example:
Syntax:
Example: 1
=A1-b1-c1-d1 and give enter. Then press up arrow to know the answer. To check the given formula press F2 in function keys or press Control and Graph key.
Example: 2
=a1-a2-a3-a4 and give enter. Then press up arrow to know the answer. To check the given formula press F2 in function keys or press Control and Graph key.
3. Multiplication: –
If the numbers are written from range of cells, the formula is as follows:
Type 1:
Example: 1
Syntax:
=a1*b1*c1*d1*e1*f1and give enter. Then press up arrow to know the answer. To check the given formula press F2 in function keys or press Control and Graph key. [like that we can increase or decrease the range of cells].
Type 2:
Example: 2
Syntax:
=product (a1*b1*c1*d1 and give enter. Then press up arrow to know the answer. To check the given formula press F2 in function keys or press Control and Graph key.
Note: like that we can increase or decrease the range of cells.
Example: 3
=product(a1:a4) and give enter. Then press up arrow to know the answer. To check the given formula press F2 in function keys or press Control and Graph key.
Note: like that we can increase or decrease the range of cells
4. Division:
If the numbers are written from range of cells, the formula is as follows:
Example: –
Syntax:
=a1/b1/c1/d1 and give enter. Press up arrow to know the answer. To check the given formula press F2 in function keys or press Control and Graph key.
Note: Like that we can increase or decrease the range of cells.
5. For finding the square root:
First type the number in the cell and go to blank cell and type the formula as follows:
=sqrt(number cell) or directly give the formula in present cell with required number, =sqrt(type required number) and give enter. Press up arrow to know the answer. To check the given formula press F2 in function keys or press Control and Graph key.
6. For finding the average:
The average is calculated on total marks obtained by the student. The formula is as follows:
Syntax:
=Average(total marks cell/number of subjects) and give enter or =total marks cell/number of subjects and give enter. Press up arrow to know the answer. To check the given formula press F2 in function keys or press Control and Graph key.
7. For logical purposes like announcing the results whether student was passed or failed on the basis of percentage/total marks the IF function is used.
Using if condition:
Syntax:
=if(and(c9>=35,d9>=35,e9>=35,f9>=35,g9>=35),”pass”,”fail”) and press enter. Press up arrow to know the answer. To check the given formula press F2 in function keys or press Control and Graph key.
8. calculating the Grade formula:
For conforming the grades, divisions or classes to the students, the “IF” condition is used as follows:
Type 1:
Example:
Syntax:
=if(average cell>=90,”A”,if(average cell>=75,”B”,if(average cell>=50,”C”,if(average cell>=35, “D”)))) and give enter. press up arrow to know the answer. To check the given formula press F2 in function keys or press Control and Graph key.
Type 2:
Example:
Syntax:
=if(average cell>=85,”A”,if(average cell>=70,”B”, if(average cell>=60,”C”,if(average cell>=50,”D”,if(average cell>=35,”pass”,”fail”))))) and give enter. press up arrow to know the answer. To check the given formula press F2 in function keys or press Control and Graph key.
9. For calculating the future value or the maturity value of an investment:
Syntax:
=FV(Rate of interest,period of time,,principal amount) and give enter. press up arrow to know the answer. To check the given formula press F2 in function keys or press Control and Graph key.
10. Payments: (Formula to calculate on loan amount)
Today we will calculate the amount to be paid every month against a loan taken.
PMT function – full form is Payment.
PMT calculates the payments for a loan based on a constant rate of interest and constant monthly payments. ie. we can find out the amount we need to pay back every month for a loan taken, at a constant interest for a particular period of time (months).
The PMT function uses the following syntax:
=PMT(rate,nper,pv,fv)
The PMT function syntax has the following arguments:
Rate – The monthly interest rate for the loan in percentage
Nper – No of periods. The total number of months in which the payments of the loan will be made.
PV – Present Value or also called as the Principal.
FV – This is optional, you can either enter 0 or 1. This is the future value or cash balance that you want after the last payment is made. Future value is 0 for payments made at the end of the period or the value 1 for payments made at the beginning of the period.
Data to be entered
A1 – Rate of Interest, B1 – No of instalments or Nper, C1- Loan Amount of PV, D1 – Payment or PMT
A2 – 7%, B2 – 36, C2- 1,00,000, D2- =pmt(A2/12,B2,C2,0) and give enter. press up arrow to know the answer. To check the given formula press F2 in function keys or press Control and Graph key.
we write A2/12 because rate of interest is always for an year, if we want to calculate it for a month it has to be divided by 12. Two more examples have been given for you to understand the function.
You can also calculate the pmt function from the Formulas Tab.
Go to the Formulas tab (alt M+F) insert function dialog box will open, enter pmt and tab to go button. Then you can give the function arguments -Rate:, Nper:, PV: and tab to Ok button.
11. REMOVE DUPLICATE
If in your Excel spreadsheet contains duplicates and you need to remove them, this can be done using the feature ‘Remove Duplicates’ which is present in the Data Ribbon Tab. Data duplication can happen when records are entered into the spreadsheet more than once or you’re combining records from multiple sheets. You might find duplicates in fields such as a First Name or Last Name, ID No. etc.
Remember when you use the Remove Duplicates feature, the duplicate data that is the entire row will be permanently deleted. The steps below will help you find duplicates or even triplicates in your spreadsheet and remove them.
STEPS TO REMOVE DUPLICATES:
Place your cursor on any cell within the data range that you want to remove the duplicates from, and click on the Remove Duplicates button by pressing Alt A +M. The ‘Remove Duplicates’ dialog box will be displayed. It will announce ‘To delete duplicate values, select one or more columns that contain duplicates. The focus will be on ‘Select All’ button by default and all the column headings will be checked in the dialog box by default. Tab to ‘Unselect All’ button and press space. Then tab until you hear the Heading of the First Column, and then tab again and navigate to the column where the duplicate records or values are present by using down arrow.
Eg. If the duplicate records are present in the Name column. Use down arrow to navigate to the Name column and press space bar to select the column and tab to ‘Ok’ button. The duplicate values will get deleted and It will pop up a message Eg. “2 duplicate values found and remove; 9 unique values remain”.
Eg2. There could be a scenario where the First Name is the same but the last Name is different. In that case it will check both First & Last Name column and then remove only those records where the duplicate is found both in the First & Last Name. While selecting the column headings you should select both First & Last Name.
DATA TO BE CREATED:
A1.SL.NO. B1. FIRST NAME C1. LAST NAME D1. CLASS E1. SECTION
Enter at least 12 records. Make sure you enter duplicates in First Name & Second Name column. Screenshot attached for your reference.
12. For inserting current date and time
First place the cursor in blank cell then give =NOW() and give enter. press up arrow to know the answer. To check the given formula press F2 in function keys or press Control and Graph key.
13. For knowing the weekday “weekday” function is used.
Syntax =weekday(“mm/dd/yy”) and give enter. press up arrow to know the answer. To check the given formula press F2 in function keys or press Control and Graph key.
- For finding the maximum value MAX function is used. The formula is written as
=MAX(starting cell number data:ending cell number data) and give enter. press up arrow to know the answer. To check the given formula press F2 in function keys or press Control and Graph key. )
15. For finding minimum value in a column or table MIN function is used and it is written as:
=MIN(starting cell number data:ending cell number data) and give enter. press up arrow to know the answer. To check the given formula press F2 in function keys or press Control and Graph key.
16. To insert date:
Press Control and semi colon.
17. To insert time:
Press Control shift and semi colon.
18. THE FUNCTION OF RANK FORMULA:
The Rank formula is calculated on the basis of total marks. When the total marks column is in H1 column and the range of marks begins from H2 to h10 the syntax is as follows:
=rank(total marks cell h2,$h$2:$h10) and press enter key. To Know the answer press up arrow. To check the given formula press F2 in function keys or press Control and Graph key.
19. POWER FORMULA:
=Power(5,6) and press enter key. Press up arrow to know the answer. The above formula will give you the the answer of 5 to the power of 6.In the same manner you can calculate the power of any required values.
20. LEN FUNCTION: To find out the actual text length of a cell.
=len(cell reference) and give enter key. Press up arrow to know the answer.
21. CALCULATION OF ELECTRICITYY BILL:
Cell reference:
A1-METER NUMBER
B1-NAME OF THE CONSUMER
C1- PRESENT MONNTH READING (PMR)
D1- LAST MONTH READING (LMR)
E1- NUMBER OF UNITS
Here in E2 do the calculation PMR-LMR To get number of units consumed.
F1- NEW BILL
Here do the calculation on the basis of number of units using if condition the syntax is as follows:
The given example is only a rough estimation for our reference.
=if(E2>=500,E2*8,IF(E2>=300,E2*6,IF(E2>=100,E2*3,IF(E2<100,E2*1.50))))) and give enter key. Press up arrow to know the amount of new bill.
NOTE: The above given rates for the units are only temporary, we can specify the rates as per the requirement.
G1-SERVICE TAX
In G2 do the calculation as follows:
=new bill*percentage of service tax
For example G2*10% and give enter.
H1- FINAL BILL
In H2 do the calculation new bill+service tax
For example =F2+G2 and give enter. Press up arrow to know the final bill. To check the given formula press F2 in function keys or press Control and Graph key.
NEW DATE FUNCTIONS:
22. A). WORK EXPERIENCE FROM DATE OF JOINING: –
DESCRIPTION: – To calculate work experience of any employee from date of joining till date of retirement, the syntax is as follows:
=(DATE OF RETIREMENT-DATE OF JOINING)/365.25 and give Enter. To check the given formula press F2 in function keys or press Control and Graph key.
23. FUTURE DATE OR EXPECTED DATE:
DESCRIPTION: – The expected date function is used to findout the actual date on which a person attains the required age.
The syntax is as follows:DATE OF BIRTH CELL+(AGE CELL*365.25) and give Enter. To check the given formula press F2 in function keys or press Control and Graph key.
24. Calculating the exact age in years, months and days:
Description: – The function of calculating actual age of a person in years, months and days is as follows:
- To calculate the actual age of a person in years, the syntax is:
=DATEDIF(DATE OF BIRTH CELL,TODAY(),”Y”) and give enter. Press up arrow to know the answer. To check the given formula press F2 in function keys or press Control and Graph key.
- To calculate the actual age of a person in months, the syntax is as follows:
=DATEDIF(DATE OF BIRTH CELL,TODAY(),”YM”) and give enter. Press up arrow to know the answer. To check the given formula press F2 in function keys or press Control and Graph key.
- To calculate the actual age of a person in days, the syntax is as follows:
=DATEDIF(DATE OF BIRTH CELL,TODAY(),”MD”) and give enter. Press up arrow to know the answer. To check the given formula press F2 in function keys or press Control and Graph key.
- To calculate the exact age of a person taking in count of years, months and days, the syntax is as follows:
=(TODAY()-DATE OF BIRTH CELL) and give enter. Press up arrow to know the answer. To check the given formula press F2 in function keys or press Control and Graph key.
25. NUMBER OF WORKING DAYS INCLUDING OTHER HOLIDAYS FROM DATE OF JOINING:
Description: – To calculate number of working days from date of joining, the syntax is as follows:
=NETWORKDAYS(STARTING DAY,ENDING DAY,HOLIDAY) and give enter. Press up arrow to know the answer. To check the given formula press F2 in function keys or press Control and Graph key.
Important links:
subscribe our YouTube channel for various tutorials