Category: msexcel

MS Excel Shortcuts Keys
MS Excel Shortcuts Keys

MS Excel Shortcuts Keys

MS Excel Shortcuts Keys: – MS Excel(Microsoft Excel) is a spreadsheet which allows users to create, organize and calculate data on a formula basis.It Is developed by Microsoft company for Windows, macOS, Android, and iOS. It has unique features calculation, graphing tools, pivot tables, and a macro programming language, which We use for a Programming language like Visual Basic.Here we start a topic on MS Excel Shortcuts Keys

Developer(s) Company Microsoft CO
Initial release September 30, 1985
Stable release
2016 (16.0.6769.2017)
OS Working Microsoft Windows, MAC
Type Spreadsheet
License Trialware(Commercial)
Website office.microsoft.com/en-us/excel

 

MS Excel  Shortcuts Keys Commands

We have explained all the commands for MS Excel  Shortcuts Keys and their explanation

 

MS Excel shortcuts  keys MS Excel shortcuts Description
F2 This MS Excel shortcuts Edit the selected cell in Spreadsheet.
F3 When a user creates names, F3 will paste names.
F4 It Repeats the last action the of the user. For example, if you changed the value of the text in another cell, pressing F4 will change the text in a cell to the same value .
F5 This will Go to a specific cell. For example, C9.
F7 It checks the spelling of selected text or all document
F11 Using this you can create chart on the basis of selected text
Ctrl + Shift + ; It  Enter the current time in cell.
Ctrl + ; It enters the current date in the cell.
Alt + Shift + F1 This will insert the new worksheet in the current worksheet
Alt + Enter While typing text in a cell, pressing Alt + Enter will move to the next line, allowing for multiple lines of text in one cell.
Shift + F3 Open the Excel formula window.
Shift + F5 Bring up the search box.
Ctrl + 1 Open the Format Cells window in a spreadsheet.
Ctrl + A Select all contents of the spreadsheet.
Ctrl + B It Bold highlighted a selection of text.
Ctrl + I It Italic highlighted a selection of text.
Ctrl + K This MS Excel shortcuts will  Insert link in documents.
Ctrl + S This MS Excel shortcuts will Save the open worksheet.
Ctrl + U This MS Excel shortcuts will Underline highlighted the selection.
Ctrl + 1 This MS Excel shortcuts will  Change the format of selected cells.
Ctrl + 5 This MS Excel shortcuts will  Strikethrough highlighted the selection.
Ctrl + P This MS Excel shortcuts will  Bring up the print dialog box to begin the printing process.
Ctrl + Z This MS Excel shortcuts will Undo the last action.
Ctrl + F3 Open Excel Name Manager.
Ctrl + F9 Minimize current window.
Ctrl + F10 Maximize currently selected window.
Ctrl + F6 Switch between open workbooks or windows.
Ctrl + Page up Move between worksheets in the same document.
Ctrl + Page down Move between worksheets in the same document.
Ctrl + Tab Move between Two or more open Excel files.
Alt + = Create a formula to sum all of the above cells.
Ctrl + ‘ Insert the value of the above cell into the cell currently selected.
Ctrl + Shift + 1  This MS Excel shortcuts will Format number in comma format.
Ctrl + Shift + 4 This MS Excel shortcuts will Format number in currency format.
Ctrl + Shift + 3 This MS Excel shortcuts will Format number in date format.
Ctrl + Shift + 5 This MS Excel shortcuts will Format number in a percentage format.
Ctrl + Shift + 6 This MS Excel shortcut will Format number in scientific format.
Ctrl + Shift + 2 This MS Excel shortcut will Format number in time format.
Ctrl + Arrow key This MS Excel shortcut will  Move to next section of text in a worksheet.
Ctrl + Space This MS Excel shortcut will  Select all column in the worksheet.
Shift + Space This MS Excel shortcut will  Select all row in the worksheet.
Ctrl + – This MS Excel shortcut will Delete the selected column or row in the worksheet.
Ctrl + Shift + = This MS Excel shortcut will  Insert a new column or row in the worksheet.
Ctrl + Home This MS Excel shortcut will  Move to cell B1.
Ctrl + ~ This MS Excel shortcut will  Switch between showing Excel formulas or their values in cells.

I hope you would like this topic on MS Excel  Shortcuts Keys

MS Excel Shortcuts Keys

MS Excel Shortcuts Keys: Microsoft Excel is a spreadsheet which allows users to create, organize and calculate data on the formula basis. It Is developed by Microsoft company for Windows, macOS, Android, and iOS. It has unique features calculation, graphing tools, pivot tables, and a macro programming language, which We use for the programming language like Visual Basic.

Developer(s) Company Microsoft CO
Initial release September 30, 1985
Stable release
2016 (16.0.6769.2017)
OS Working Microsoft Windows, MAC
Type Spreadsheet
License Trialware(Commercial)
Website office.microsoft.com/en-us/excel

MS Excel Shortcuts Keys with Short Description is listed here:

MS Excel shortcuts  keys MS Excel shortcuts Description
F2 This MS Excel shortcuts Edit the selected cell in the spreadsheet.
F3 When the user creates names, F3 will paste names.
F4 It Repeats the last action of the user. For example, if you changed the value of the text in another cell, pressing F4 will change the text in the cell to the same value.
F5 This will Go to a specific cell. For example, C9.
F7 It checks the spelling of selected text or all document
F11 Using this you can create chart on the basis of selected text
Ctrl + Shift + ; It  Enter the current time in cell.
Ctrl +; It Enters the current date in the cell.
Alt + Shift + F1 This will insert the new worksheet in the current worksheet
Alt + Enter While typing text in a cell, pressing Alt + Enter will move to the next line, allowing for multiple lines of text in one cell.
Shift + F3 Open the Excel formula window.
Shift + F5 Bring up the search box.
Ctrl + 1 Open the Format Cells window in the spreadsheet.
Ctrl + A Select all contents of the spreadsheet.
Ctrl + B It Bold highlighted the selection of text.
Ctrl + I It Italic highlighted the selection of text.
Ctrl + K This MS Excel shortcuts will  Insert link in documents.
Ctrl + S This MS Excel shortcuts will Save the open worksheet.
Ctrl + U This MS Excel shortcuts will Underline highlighted the selection.
Ctrl + 1 This MS Excel shortcuts will  Change the format of selected cells.
Ctrl + 5 This MS Excel shortcuts will  Strikethrough highlighted the selection.
Ctrl + P This MS Excel shortcuts will  Bring up the print dialog box to begin the printing process.
Ctrl + Z This MS Excel shortcuts will Undo the last action.
Ctrl + F3 Open Excel Name Manager.
Ctrl + F9 Minimize current window.
Ctrl + F10 Maximize currently selected window.
Ctrl + F6 Switch between open workbooks or windows.
Ctrl + Page up Move between worksheets in the same document.
Ctrl + Page down Move between worksheets in the same document.
Ctrl + Tab Move between Two or more open Excel files.
Alt + = Create a formula to sum all of the above cells.
Ctrl + ‘ Insert the value of the above cell into the cell currently selected.
Ctrl + Shift + 1  This MS Excel shortcuts will Format number in comma format.
Ctrl + Shift + 4 This MS Excel shortcuts will Format number in currency format.
Ctrl + Shift + 3 This MS Excel shortcuts will Format number in date format.
Ctrl + Shift + 5 This MS Excel shortcuts will Format number in the percentage format.
Ctrl + Shift + 6 This MS Excel shortcut will Format number in scientific format.
Ctrl + Shift + 2 This MS Excel shortcut will Format number in time format.
Ctrl + Arrow key This MS Excel shortcut will  Move to next section of text in the worksheet.
Ctrl + Space This MS Excel shortcut will  Select all column in the worksheet.
Shift + Space This MS Excel shortcut will  Select all row in the worksheet.
Ctrl + – This MS Excel shortcut will Delete the selected column or row in the worksheet.
Ctrl + Shift + = This MS Excel shortcut will  Insert a new column or row in the worksheet.
Ctrl + Home This MS Excel shortcut will  Move to cell B1.
Ctrl + ~ This MS Excel shortcut will  Switch between showing Excel formulas or their values in cells.

Arithmetical functions

Arithmetical functions in excel:- Hi Readers, In this blog you will get to know arithmetical functions in excel.We have discussed here all count functions like count(), counta(), countblank(). These all are important function of arithmetical functions in excel

Count(Arithmetical functions)

What Does It Do?

This function counts the number of numeric entries in a list, it will ignore blanks, text, and errors.

Syntax

=COUNT (Range1, Range2, and Range3… through to Range30)

Formatting

No special formatting is needed.

Example

The following table was used by a builder’s merchant to calculate the number of sales

for various products in each month.

A B C D
ITEM JAN FEB MAR
Bricks $1000
Wood $5000
Glass $2000 $1000
Metal $1000
Count 3 2 0

=Count(B3:B5)

COUNTA(Arithmetical functions)

What Does It Do?

This function counts the number of numeric or text entries in a list.

It will ignore blanks.

Syntax

=COUNTA(Range1,Range2,Range3… through to Range30)

Formatting

No special formatting is needed.

Example

See the below table…

In the above table, Formula counts only number and words, NO blanks.

COUNTBLANK(Arithmetical functions)

 

What Does It Do?

This function counts the number of blank cells in a range.

Syntax

=COUNTBLANK(RangeToTest)

Formatting

No special formatting is needed.

Example

The following table was used by a company which was balloting its workers on whether
The company should have a no smoking policy.
Each of the departments in the various factories was questioned.
The response to the question could be Y or N.
As the results of the vote were collated they were entered into the table.
The =COUNTBLANK() function has been used to calculate the number of departments which have not yet registered a vote.

  

1   B C D E F
2 FACTORY1 Y N Y    
3 FACTORY2   Y   Y N
4 FACTORY3 N       N
5 FACTORY4 Y N Y    
6 FACTORY5   N   N Y
7 FACTORY6   N Y    
8 FACTORY7 Y   N   Y
9 FACTORY8 N N     Y
10 FACTORY9 N Y   N Y
11 FACTORY10 Y   N    

The Result will be- vote not yet registered =Countblank(B2:F11) and answer will be (25)

COUNTIF(Arithmetical functions)

What Does It Do?

This function counts the number of items which match criteria set by the user.

Syntax

=COUNTIF (RangeOfThingsToBeCounted, CriteriaToBeMatched)

The criteria can be typed in any of the following ways.

  • To match a specific number type the number, such as =COUNTIF(A1:A5, 100)
  • To match a piece of text type the text in quotes, such as =COUNTIF(A1:A5,“Hello”)
  • To match using operators surround the expression with quotes, such as =COUNTIF(A1:A5, “>100”)

Formatting

No special formatting is needed.

Example

The following table was used by a company which was balloting its workers on whether

The company should have a no smoking policy,

Each of the departments in the various factories was questioned.

The response to the question could be Y or N.

As the results of the vote were collated they were entered into the table.

The =COUNTIF    () function has been used to calculate the number of departments which

have registered a vote , Either which YES or NO.

1   B C D E F
2 FACTORY1 Y N Y    
3 FACTORY2   Y   Y N
4 FACTORY3 N       N
5 FACTORY4 Y N Y    
6 FACTORY5   N   N Y
7 FACTORY6   N Y    
8 FACTORY7 Y   N   Y
9 FACTORY8 N N     Y
10 FACTORY9 N Y   N Y
11 FACTORY10 Y   N    

Votes for Yes: =COUNTIF(B2:F11,”Y”)

Votes for No:=COUNTIF(B2:F11,”N”)

COUNTIFS (Arithmetical functions)

What Does It Do?

This function counts the number of cells in a range that matches particular criteria, which has been set by the user.

Syntax

=COUNTIFS( criteria_range1, criteria1, [criteria_range2, criteria2, … criteria_range_n, criteria_n] )

Formatting

No special formatting is needed.

arithmetical functions

Performing Basic Calculations

To process data by performing basic calculations in a worksheet, we can use formulas or some functions, such as sum and if count-if, count ifs while performing calculations, you may need to refer to a single cell or multiple cells using the cell referencing feature. You can also assign the meaningful name to a cell or range of cells.

Cells referencing

The formulas and functions can be applied to the data stored at different locations in the worksheet or in the different worksheets. To refer to this data from different locations, in Excel gives us a feature known as cell references. A reference is the address of a cell or a range of cells in a worksheet. By using this feature called cell referencing, you can refer to the data of various cells in a formula. You can also refer to the values in the worksheets of the same workbooks or of some other worksheets. Reference to the other workbooks are known as links:

Some most commonly used referencing types in the Excel are:

  • Relative Referencing
  • Absolute Referencing
  • Mixed Referencing
  • 3-D Referencing

Performing Basic Calculations -Relative Referencing:-

In this type of reference, when the position of the cell containing the formula changes, the reference also changes, For Example, You have C3, with a formula that reference A3 and B3. When you can copy the formula C3 to C4, Excel updates the cells from A3 to A4 and B3 to B4, inside the formula, respectively as shown in the following figure.

Performing Basic Calculations -Absolute Referencing:-

In absolute referencing, the cell reference contained in the formula does not change even if you copy the formulas in the other locations, you can use absolute referencing in a formula by prefixing the column name and the row number by the dollar ($) sign. For example, to refer to the cells A4 and B4, absolutely in C4, specify the cell names as $A$3 and $B$3. Thereafter, copy the formula from C3 to C4. The reference used in the formula used in the formula does not get changed, as shown in the following figure.

A B C D
1
2
3 25 35 60 =$A$3+$B$3
4 30 65 =$A$4+$B$3
5
6
7

Performing Basic Calculations -Mixed Referencing:-

With the help of this kind of referencing we can use both referencing, relative referencing as well as absolute referencing, it has either an absolute column and a relative row or an absolute row and a relative column, for example, you have the cell C3, with the formula that references A3 relatively and B3 absolutely.

Now when you copy the formula from C3 to C4, the relative reference will change but the absolute will remain the same, as shown in the following figure,

A B C D
1
2
3 25 40 65 =$A3+$B3
4 80 =$A4+$B3
5
6
7

Performing Basic Calculations -3-D Referencing:-

This kind of reference, refers to the same cell or range of cells in multiple worksheets. To understand 3-D reference better, consider a scenario where you have created a workbook that contains worksheets for sales of every month. The B4 cell contains the total sales of a month in each worksheet.

Now, you want to find the total sales first quarter in the new worksheet, Quarter_1, For this, you need all the total sales of the April, May, and June, You can use 3-D referencing to add all the cell references of three sheets. To refer the cell reference of three sheets, you need to click the April worksheet tab, hold then click the June worksheet tab.

Performing Basic Calculations