Rank

The RANK function returns the rank of a number in a list of numbers.

1. If the third argument is omitted (or 0), Excel ranks the largest number first, second largest number second, etc

Note: when we drag the RANK function down, the absolute reference ($A$1:$A$9) stays the same, while the relative reference (A1) changes to A2, A3, A4, etc.

Product Range with the help of Pivot Option

To know the product range as per the below steps:

If you have different MRP of  product then you can easily get the details different price range with the help of "pivot group"

Step 1

Drag MRP into "Rows Field", Product to Columns and QTY to "Value Field".

Step 2

Right click on the MRP field and click Group as shown below.

Step 3

In the Grouping give starting and ending values along with the by value as shown below

Step 4 ( result)

VLOOKUP/HLOOKUP Link break

You can Break the link as per the below :

Step 1:

Go to Data>Edit Link

Step 2:

In the Edit Links POP-UP click Break Link

Step 3:

Clink Break Link

Upper/Lower & Proper Case

Upper Case:

Step 1

Step 2 (result)

Lower Case:

Step 1

Step 2 (result)









Proper Case:

Step 1














Step 2 (result)

Date Format

Changing the date format : 

=date(right(A2,4),mid(A2,4,2),mid(A2,1,2))

Step 1

Step 2

Step 3

How to select only Month in a date format:

=TEXT(A2,"mmm")

Step 1











Step 2












Note: If you add YYY then you can get year (example: Mar 2015) =TEXT(A2,"mmm yyy")

Shortcut Keys

Windows system key combinations

• F1: Help
• CTRL+ESC: Open Start menu
• ALT+TAB: Switch between open programs
• ALT+F4: Quit program
• SHIFT+DELETE: Delete item permanently
• Windows Logo+L: Lock the computer (without using CTRL+ALT+DELETE)

Windows program key combinations

• CTRL+C: Copy
• CTRL+X: Cut
• CTRL+V: Paste
• CTRL+Z: Undo
• CTRL+B: Bold
• CTRL+U: Underline
• CTRL+I: Italic

Mouse click/keyboard modifier combinations for shell objects

• SHIFT+right click: Displays a shortcut menu containing alternative commands
• SHIFT+double click: Runs the alternate default command (the second item on the menu)
• ALT+double click: Displays properties
• SHIFT+DELETE: Deletes an item immediately without placing it in the Recycle Bin

General keyboard-only commands

• F1: Starts Windows Help
• F10: Activates menu bar options
• SHIFT+F10 Opens a shortcut menu for the selected item (this is the same as right-clicking an object
• CTRL+ESC: Opens the Start menu (use the ARROW keys to select an item)
• CTRL+ESC or ESC: Selects the Start button (press TAB to select the taskbar, or press SHIFT+F10 for a context menu)
• CTRL+SHIFT+ESC: Opens Windows Task Manager
• ALT+DOWN ARROW: Opens a drop-down list box
• ALT+TAB: Switch to another running program (hold down the ALT key and then press the TAB key to view the task-switching window)
• SHIFT: Press and hold down the SHIFT key while you insert a CD-ROM to bypass the automatic-run feature
• ALT+SPACE: Displays the main window's System menu (from the System menu, you can restore, move, resize, minimize, maximize, or close the window)
• ALT+- (ALT+hyphen): Displays the Multiple Document Interface (MDI) child window's System menu (from the MDI child window's System menu, you can restore, move, resize, minimize, maximize, or close the child window)
• CTRL+TAB: Switch to the next child window of a Multiple Document Interface (MDI) program
• ALT+underlined letter in menu: Opens the menu
• ALT+F4: Closes the current window
• CTRL+F4: Closes the current Multiple Document Interface (MDI) window
• ALT+F6: Switch between multiple windows in the same program (for example, when the Notepad Find dialog box is displayed, ALT+F6 switches between the Find dialog box and the main Notepad window)

Shell objects and general folder/Windows Explorer shortcuts

For a selected object:

• F2: Rename object
• F3: Find all files
• CTRL+X: Cut
• CTRL+C: Copy
• CTRL+V: Paste
• SHIFT+DELETE: Delete selection immediately, without moving the item to the Recycle Bin
• ALT+ENTER: Open the properties for the selected object

To copy a file

Press and hold down the CTRL key while you drag the file to another folder.

To create a shortcut

Press and hold down CTRL+SHIFT while you drag a file to the desktop or a folder.

General folder/shortcut control

• F4: Selects the Go To A Different Folder box and moves down the entries in the box (if the toolbar is active in Windows Explorer)
• F5: Refreshes the current window.
• F6: Moves among panes in Windows Explorer
• CTRL+G: Opens the Go To Folder tool (in Windows 95 Windows Explorer only)
• CTRL+Z: Undo the last command
• CTRL+A: Select all the items in the current window
• BACKSPACE: Switch to the parent folder
• SHIFT+click+Close button: For folders, close the current folder plus all parent folders

Windows Explorer tree control

• Numeric Keypad *: Expands everything under the current selection
• Numeric Keypad +: Expands the current selection
• Numeric Keypad -: Collapses the current selection.
• RIGHT ARROW: Expands the current selection if it is not expanded, otherwise goes to the first child
• LEFT ARROW: Collapses the current selection if it is expanded, otherwise goes to the parent

Properties control

• CTRL+TAB/CTRL+SHIFT+TAB: Move through the property tabs

Accessibility shortcuts

• Press SHIFT five times: Toggles StickyKeys on and off
• Press down and hold the right SHIFT key for eight seconds: Toggles FilterKeys on and off
• Press down and hold the NUM LOCK key for five seconds: Toggles ToggleKeys on and off
• Left ALT+left SHIFT+NUM LOCK: Toggles MouseKeys on and off
• Left ALT+left SHIFT+PRINT SCREEN: Toggles high contrast on and off

Microsoft Natural Keyboard keys

• Windows Logo: Start menu
• Windows Logo+R: Run dialog box
• Windows Logo+M: Minimize all
• SHIFT+Windows Logo+M: Undo minimize all
• Windows Logo+F1: Help
• Windows Logo+E: Windows Explorer
• Windows Logo+F: Find files or folders
• Windows Logo+D: Minimizes all open windows and displays the desktop
• CTRL+Windows Logo+F: Find computer
• CTRL+Windows Logo+TAB: Moves focus from Start, to the Quick Launch toolbar, to the system tray (use RIGHT ARROW or LEFT ARROW to move focus to items on the Quick Launch toolbar and the system tray)
• Windows Logo+TAB: Cycle through taskbar buttons
• Windows Logo+Break: System Properties dialog box
• Application key: Displays a shortcut menu for the selected item

Microsoft Natural Keyboard with IntelliType software installed

• Windows Logo+L: Log off Windows
• Windows Logo+P: Starts Print Manager
• Windows Logo+C: Opens Control Panel
• Windows Logo+V: Starts Clipboard
• Windows Logo+K: Opens Keyboard Properties dialog box
• Windows Logo+I: Opens Mouse Properties dialog box
• Windows Logo+A: Starts Accessibility Options (if installed)
• Windows Logo+SPACEBAR: Displays the list of Microsoft IntelliType shortcut keys
• Windows Logo+S: Toggles CAPS LOCK on and off

Dialog box keyboard commands

• TAB: Move to the next control in the dialog box
• SHIFT+TAB: Move to the previous control in the dialog box
• SPACEBAR: If the current control is a button, this clicks the button. If the current control is a check box, this toggles the check box. If the current control is an option, this selects the option.
• ENTER: Equivalent to clicking the selected button (the button with the outline)
• ESC: Equivalent to clicking the Cancel button
• ALT+underlined letter in dialog box item: Move to the corresponding item

Shortcut Keys	Special Character
Alt + 0224	à
Alt + 0232	è
Alt + 0236	ì
Alt + 0242	ò
Alt + 0241	ñ
Alt + 0228	ä
Alt + 0246	ö
Alt + 0252	ü
Alt + 0248	ø
Alt + 0223	ß
Alt + 0198	Æ
Alt + 0231	ç
Alt + 0191	¿
Alt + 0176	°  (degree symbol)
Alt + 0177	±  (plus/minus symbol)
Alt + 0153	™
Alt + 0169	©
Alt + 0174	®
Alt + 0128	€  (Euro currency)
Alt + 0162	¢  (Cent symbol)
Alt + 0163	£  (British Pound currency)
Alt + 0165	¥  (Japanese Yen currency)