KEMBAR78
Excel Tips | PDF | Array Data Structure | Microsoft Excel
Scribd
Upload
43 views6 pages

Excel Tips

This document provides shortcuts and functions in Excel. Some key shortcuts include Ctrl+T to create a table, Ctrl+Home/End to go to the top/bottom of the worksheet, and Ctrl+PageUp/Down to move between worksheets. Functions summarized include INDEX and MATCH for lookup, ROWS and COLUMNS to return the number of rows/columns in an array or reference, and using INDEX, MATCH, ROWS and COLUMNS together for advanced lookup and transposing data.

Uploaded by

Krishna Prasad Phelu
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as XLSX, PDF, TXT or read online on Scribd
43 views6 pages

Excel Tips

This document provides shortcuts and functions in Excel. Some key shortcuts include Ctrl+T to create a table, Ctrl+Home/End to go to the top/bottom of the worksheet, and Ctrl+PageUp/Down to move between worksheets. Functions summarized include INDEX and MATCH for lookup, ROWS and COLUMNS to return the number of rows/columns in an array or reference, and using INDEX, MATCH, ROWS and COLUMNS together for advanced lookup and transposing data.

Uploaded by

Krishna Prasad Phelu
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as XLSX, PDF, TXT or read online on Scribd
You are on page 1/ 6

S.N.

Shortcut Function
1 Ctrl T Create Table
2 Ctrl Home Go to top of worksheet
3 Ctrl End Go to end of worksheet
4 Ctrl PageDown Go to next worksheet (Right)
5 Ctrl PageUp Go to previous worksheet (Left)
6 Ctrl Tab Flip between excel workbooks
7 Ctrl 1 Format Cells
8 Ctrl D Fill Down
9 Ctrl R Fill Right
10 Ctrl A Select a range
11 Ctrl Spacebar Select an entire column
12 Ctrl - Delete highlighted cells or rows or columns
13 F2 Enter into formula edit mode in a cell
14 F4 make a cell constant in excel formul
15 F5 Go to
16 F8 Turns on/off Extend mode for selection
17 F10 Shows the key tips
18 F11 Creates a chart sheet of a selected range.
19 F12 Brings up the Save As dialog box.
20 Alt E S Paste Special
21 Alt H O Organize options
22 Alt H O R Rename sheet
23 Alt H B Border options
24 Shift F10 Right Click
25 Shift Spacebar Select an entire row
Comment

Flip between open files of the same program

Same as pressing ALT


S.N. Fromula Syntax

1 INDEX() INDEX(array, row_num, [column_num])

2 MATCH() MATCH(lookup_value, lookup_array, [match_type])

3 ROWS() ROWS(array)

4 COLUMNS() COLUMNS(array)
Description

Returns the value of an element in a table or an array,


selected by the row and column number indexes.

MATCH returns the position of the matched value within


lookup_array, not the value itself.
For example, MATCH("b",{"a","b","c"},0) returns 2, which
is the relative position of "b" within the array
{"a","b","c"}.

Returns the number of rows in a reference or array.

Returns the number of columns in an array or reference.


S.N. Fromula

1 INDEX(range, MATCH(lookup_value, lookup_range, match_type))

2 INDEX(array, ROWS(array), COLUMNS(array))


Description

We combine INDEX and MATCH to replace VLOOKUP.


First, we call INDEX on a range of numbers from which we
would like to look up a given value.
Then, we use MATCH to tell Excel how many cells it should
count into INDEX's range, based on a given value matched
against a separate array.

Transpose Range (Reshape excel sheet data) with INDEX,


COLUMNS and ROWS functions
First call INDEX on the range from which we would like to get
value from.
Then we use ROWS() and COLUMNS() to number of rows
and columns to be offset to return value for particular cell.

You might also like