Posted on

MS Excel Selection Shortcuts

MS Excel Shortcuts

Selection

Select entire row

To select all the cells in a specific row you can use the command Shift + Spacebar.

Select entire column

To select a particular column, use the command Ctrl + Spacebar.

Select entire worksheet

To select all the cells in a worksheet, use the Ctrl + A command.

Add adjacent cells

You can add cells adjacent to an existing selection by holding down the Shift key and clicking on them.

Extend selection one cell to the right

To select cells to the right of the current cell you can use the Shift + right arrow key commands. This will select the cell immediately to the right of the current cell.

Extend selection by one cell to the left

Just as you can select a cell to the right you can also select one to the left of your current cell. The command Shift + left arrow will allow you to select a cell to the left of the cell you are currently on.

Extend by one cell up

To select a cell that is directly above the current cell, use the command Shift + up arrow.

Extend by one cell down

Similarly, use the command Shift + down arrow to select a cell below the current cell.

Extend to the last cell on the right

Ctrl + Shift + right arrow is used to select the last cell to the right of the current cell.

Extend to the last cell on the left

To select the cell on the extreme left of the current cell, use the command Ctrl + Shift + left arrow.

Extend to the last cell up

Similarly, Ctrl + Shift + up arrow can be used to select up to the last cell on the top.

Extend to the last cell down

Ctrl + Shift + down arrow will allow you to select all the cells till the last cell below the current cell.

Extend selection to start of row

Shift + Home is the command used to extend the selection up to the beginning of a particular row.

Extend selection to first cell

To extend the selection from the current cell to the very first cell, use the command Ctrl + Shift + Home.

Extend selection to last cell

Ctrl + Shift + End is the command used to select cells up to the very last cell of the worksheet.

Select cells with comments

To select specific cells, such as those that have comments, use the command Ctrl + Shift + O. Only the cells that have comments attached to them will get highlighted.

Select one character right

Shift + right arrow is used to select a single character to the right of the current character.

Select one character left

To select a single character to the left of the current character use the command Shift + left arrow key.Select one word to the right

Use Ctrl + Shift + right arrow to select a word to the right of the present word.

Select one word to the left

Use Ctrl + Shift + left arrow to select a word to the left of the present word.

Posted on

Navigation Tutorial MS Excel

Navigation

Use the right arrow key to move to the right and the left arrow key to move to the cell on the left. Similarly, to move up use the up arrow and to move to the cell below use the down arrow.

Move from one screen to the next

If you want to move one screen up, press the PgUp key; if you want to move one screen down, then press the PgDn button. To move to the right, you have to press Alt + PgDn, and to move one screen left you need to use the command Alt + PgUp.

Switch between workbooks

To switch to another open workbook, press Ctrl + Tab to move to the next workbook, and Ctrl + Shift + Tab to move to the previous one.

Move to beginning of row

Press the Home button on your keyboard to move to the start of the row on which your cursor is.

Move to last cell in worksheet

Ctrl + End is the command to use when you want to go to the last cell in the worksheet.

Move to first cell in worksheet

To go to the very first cell in your worksheet, press Ctrl + Home.

Move one word right

When you want to move to a word that is to the immediate right of the current word, use the command Ctrl + right arrow.

Move one word left

Similarly, to move to a word to the left of the current word, use the command Ctrl + left arrow key.

Posted on

Visual Basic Editor

The Visual Basic Editor is the tool used to create, modify and maintain Visual Basic for Applications procedures in Excel. The easiest way to access VBE is to press Alt + F11. When you press this combination, a VBE window will open.

VBE has a lot of options or windows that can be accessed from the View menu at the VBA editor window. Each one is explained below.

The Project window

The Project window opens on the left of the Editor window. It provides you with an index of all the VB files that are linked to open Excel workbooks. It shows you files linked to each worksheet as well as additional files (also called modules), which contain user-defined code. Modules are not specifically linked to any worksheet.

If you want a macro to be linked to a specific worksheet then you should choose the file option, but if you want your macro to be general then it will be better if you create a new module. You can create a new module by clicking on the option “Insert” and choosing the “Module” option.

The Code window

The Code window shows the VBA code for each individual worksheet or module. Once you open a module or worksheet, you can enter your code directly in the Code window and the VBA editor will help you create valid code.

The Properties window

This window lists the various properties of the object selected in the Project window. These properties may vary depending on the type of object selected. An object can be a worksheet or a workbook or a module.

The Immediate window

You can open the Immediate window either by selecting it in the View menu of the VBE or by pressing Ctrl + G. The Immediate window helps with the debugging of the code, either by acting as the output for the debug statement or by allowing the user to evaluate expression of lines of code.

The Locals window

The Locals window can be viewed by selecting it from the View menu in the VBE window. This window will display all the local variables that are declared. It is separated into columns, which show the name, value and type of variable. It also updates these values automatically as the program continues to run.

The Watch window


The Watch window can be accessed by selecting it from the View menu in the VBE window. The Watch window is useful in debugging VBA code. It shows the value, type and context of watch expressions defined by the user.

Posted on

Excel Macro VBA. How to create a macro!

Excel Macro VBA. How to create a macro.

What is a Excel VBA Macro?

A macro is a tool in Microsoft Excel that allows you to perform a number of operations just by opening a workbook, clicking a button or changing a cell value. A macro is basically a recording of a series of tasks in Excel. It increases the efficiency of Excel by reducing manual work and saving time.

In Excel, you have the option to record or write a macro.

How to record a excel VBA macro

You can record a macro by following these steps:

  1. Open a new workbook.
  2. Go the “Developer” tab and click on the “Record Macro” button that is found in the Menu bar.
  3. In the Record Macro dialog box that opens, enter the name for this macro. You can also add a shortcut for this particular macro so that it will run when you press the shortcut.
  4. You can also enter the location where you want to store the macro.
  5. Then click OK and perform some operations and functions on the worksheet.
  6. After you are done, click on the “Stop” button, which will be in the same place where you clicked “Record Macro”.

To view this macro, right click on the sheet name and select View Code. In the pane, under VBA Project, expand the Module and you will see all the recorded macros for this worksheet with their respective names.

How to run a macro

There are two ways to run a recorded macro in Excel. The first method is by pressing the Run button and the other is by pressing Alt + F8.

When you click on the Run button or press Alt + F8, a dialog box will open. In this box you have to choose the relevant macro, select it and click Run. Then the recorded macro will play. Once the macro finishes running, you will see that any formatting that was deleted after the recording will be redone.

Posted on

Data Entry & Management in Excel

One of the main uses of Microsoft Excel is for data entry, maintenance and monitoring. Data in Excel can be organized in columns, as a table, or in a pie chart or graph.

Right now, we’ll be focusing on the basics of data entry and management in a worksheet. Data here is entered in a table that consists of rows and columns.

The top rows of tables have headings that go down to the left and are used to identify the data being entered in the tables. This lets Excel do any necessary calculations through the use of formulas. The program definitely makes it easier to manage and process data.

To make it even easier, Excel also provides a number of ways to formatting the data. If you want to locate and review some information, you’ll appreciate these data formatting features. They can be used for data formatting in individual cells, different columns and rows, and for formatting entire tables.

In previous versions of Excel each worksheet contained a billion cells, while the most recent updates released by Microsoft have almost seventeen billion cells.

To make it easier to access and reference these multitudinous cells, each of them has been given an address. This is called a cell reference.

Posted on

Top 10 Excel Shortcuts

It is very important to learn Excel shortcuts. It can save you lots of time.

Below is the list of most popular shortcuts:

10 Most popular shortcuts:

  1. Cut, copy, paste – Ctrl X, C, V (Mac: you can also use Command). These are shortcuts you’ll use every day.
  2. Paste Special – Control + Alt + V (Mac: Control + Command + V). Paste Special is very powerful. You can paste values, paste formulas, paste formatting, and even paste column widths!
  3. Fill down – Control D. An excellent way to copy values from the cell above without copy paste. Several people also mentioned Fill right, Control + R
  4. Autosum – Alt = (Mac: Command + T). A classic “magic” shortcut to automatically insert a sum function. You can use autosum to sum rows, columns, or even an entire table in one step (more details here;  autosum demo here).
  5. Current date and time – Control + ; (date) Control + Shift + : (time).  If you need a date or time stamp, it’s magic.
  6. Select all – Control A (Mac: you can also use Command). This shortcut will select all data in the “same region”. Use it whenever you want to select an entire table.
  7. Insert / delete columns and rows – To insert: with an entire row or column selected, use Control+ Shift ++ (Mac: Control + I, but in 2016, same as Win) . To delete: with an entire row or column selected, use Control + – .
  8. Edit cell – F2 (Mac: control + U) to enter “edit mode” for the active cell without taking your hands off the keyboard.
  9. Enter multiple cells – Control + Enter. Whenever you want to enter the same value or formula in more than one cell at a time. You’ll be surprised how often you use it once you understand how it works.
  10. Format cells – Control + 1 (Command + 1). Most people know this as the shortcut for the Format Cells dialog, but you can also use it to format almost anything in Excel, without care about the state of the ribbon. Try it.

Posted on

What is Excel Reference?

Cell references in Excel are very important. Understand the difference between relative, absolute and mixed reference, and you are on your way to success.

Relative Reference

By default, Excel uses relative reference. See the formula in cell D2 below. Cell D2 references (points to) cell B2 and cell C2. Both references are relative.

Relative Reference Example

1. Select cell D2, click on the lower right corner of cell D2 and drag it down to cell D5.

Relative Reference in Excel

Cell D3 references cell B3 and cell C3. Cell D4 references cell B4 and cell C4. Cell D5 references cell B5 and cell C5. In other words: each cell references its two neighbors on the left.

Absolute Reference

See the formula in cell E3 below.

1. To create an absolute reference to cell H3, place a $ symbol in front of the column letter and row number of cell H3 ($H$3) in the formula of cell E3.

Absolute Reference Example

2. Now we can quickly drag this formula to the other cells.

Absolute Reference in Excel

The reference to cell H3 is fixed (when we drag the formula down and across). As a result, the correct lengths and widths in inches are calculated.

Mixed Reference

Sometimes we need a combination of relative and absolute reference (mixed reference).

1. See the formula in cell F2 below.

Mixed Reference Example

2. We want to copy this formula to the other cells quickly. Drag cell F2 across one cell, and look at the formula in cell G2.

Mixed Reference Example

Do you see what happens? The reference to the price should be a fixed reference to column B. Solution: place a $ symbol in front of the column letter of cell B2 ($B2) in the formula of cell F2. In a similar way, when we drag cell F2 down, the reference to the reduction should be a fixed reference to row 6. Solution: place a $ symbol in front of the row number of cell B6 (B$6) in the formula of cell F2.

Result:

Mixed Reference Example

Note: we don’t place a $ symbol in front of the row number of B2 (this way we allow the reference to change from B2 (Jeans) to B3 (Shirts) when we drag the formula down). In a similar way, we don’t place a $ symbol in front of the column letter of B6 (this way we allow the reference to change from B6 (Jan) to C6 (Feb) and D6 (Mar) when we drag the formula across).

3. Now we can quickly drag this formula to the other cells.

Mixed Reference in Excel

Posted on

What is Array?

An array is a data structure that contains a group of elements. Typically these elements are all of the same data type. Arrays are commonly used in computer programs to organize data so that a related set of values can be easily sorted or searched.

Arrays are also used in a number of MS Excel spreadsheet functions.

For example, a search engine may use an array to store Web pages found in a search performed by the user. When displaying the results, the program will output one element of the array at a time. This may be done for a specified number of values or until all the values stored in the array have been output. While the program could create a new variable for each result found, storing the results in an array is much more efficient way to manage memory.

Arrays are also used in Java programming:

The syntax for storing and displaying the values in an array typically looks something like this:

arrayname[0] = "This ";
arrayname[1] = "is ";
arrayname[2] = "pretty simple.";

print arrayname[0];
print arrayname[1];
print arrayname[2];

The above commands would print the first three values of the array, or “This is pretty simple.” By using a “while” or “for” loop, the programmer can tell the program to output each value in the array until the last value has been reached. So not only do arrays help manage memory more efficiently, they make the programmer’s job more efficient as well.

Posted on

MS Excel Index Function

The Excel INDEX function returns the value at a given position in a range or array. You can use index to retrieve individual values or entire rows and columns. INDEX is often used with the MATCH function, where MATCH locates and feeds a position to INDEX.

Syntax 

=INDEX (array, row_num, [col_num], [area_num])

=INDEX (reference, row_num, [col_num], [area_num])

Arguments 

  • array – A range of cells, or an array constant.
  • row_num – The row position in the reference or array.
  • col_num – [optional] The column position in the reference or array.
  • area_num – [optional] The range in reference that should be used.

Explanation:

1.We want to know how much student 5 got In physics. So we first type index and select array (all the numbers).

2.Forrow_num we can give 5 or use match function (it is easier to use match function when the list is long).

3. For column_num we can give 4 or use match function (it is easier to use match function when there are many column).

Usage notes

Use the INDEX function to get a value from a list or table based on its location. For example, the formula =INDEX(A1:B5,2,2) will return the value at the address B2.

The INDEX function has two forms: array and reference.

Posted on

MS Excel Sumproduct Function

You have to install MS Office first. Please click the link below:

Suppose you bought 4 products in many quantities. This is time consuming to multiply unit with price and the add them together. Excel has a built in formula for that.

The SUMPRODUCT function multiplies ranges or arrays together and returns the sum of products.SUMPRODUCT is an incredibly versatile function that can be used to count and sum like COUNTIFS or SUMIFS, but with more flexibility. Other functions can easily be used inside SUMPRODUCT to extend functionality even further.

Syntax
=SUMPRODUCT (array1, [array2], …)
Arguments
• array1 – The first array or range to multiply, then add.
• array2 – [optional] The second array or range to multiply, then add.

Select array 1 then multiply with selected array2 and press enter.

How to do this calculation manually:
(3*2) +(5*1.50) +(1*60) +(2*10.5) =94.5

Usage notes

The SUMPRODUCT function works with arrays, but it doesn’t require the normal array syntax (Ctrl + Shift + Enter) to enter. The purpose of the SUMPRODUCT function is to multiply, then sum, arrays. If only one array is supplied, SUMPRODUCT will simply sum the items in the array. Up to 30 arrays can be supplied.

For more MS Excel function please click the link: