MS EXCEL

MS Excel 2010 ke Common Shortcut Keys:

Basic Shortcuts:

Ctrl + N: Naya Workbook open karna.
Ctrl + O: Existing Workbook open karna.
Ctrl + S: Workbook save karna.
F12: "Save As" dialog box kholna.
Ctrl + P: Print dialog box kholna.
Ctrl + Z: Undo last action.
Ctrl + Y: Redo last action.
Ctrl + X: Cut.
Ctrl + C: Copy.
Ctrl + V: Paste.
Ctrl + A: Sab select karna.
Ctrl + F: Search ke liye Find dialog box kholna.
Ctrl + H: Find and Replace.

Navigation Shortcuts:

Arrow Keys: Ek cell se doosre cell par move karna.
Ctrl + Arrow Key: Last filled cell tak jump karna (same row ya column mein).
Ctrl + Home: Cell A1 tak jaana.
Ctrl + End: Last filled cell tak jaana.
Page Up/Page Down: Worksheet ke upar ya neeche scroll karna.
Alt + Page Up/Page Down: Screen ke daaye ya baaye scroll karna.

Cell Formatting Shortcuts:

Ctrl + B: Bold text.
Ctrl + I: Italic text.
Ctrl + U: Underline text.
Alt + H + O + I: Auto-fit column width.
Ctrl + Shift + L: Filters apply ya remove karna.
Ctrl + 1: Format Cells dialog box kholna.
Alt + E + S + V: Paste special with values only.

Selection and Editing Shortcuts:

Ctrl + Space: Puri column select karna.
Shift + Space: Puri row select karna.
Ctrl + Shift + Space: Pura table select karna.
Shift + Arrow Keys: Selection ko extend karna.
F2: Edit mode mein enter karna.
Ctrl + D: Upar wali cell ki value neeche wali cell mein copy karna.
Ctrl + R: Left cell ki value right mein copy karna.

Insert/Delete Shortcuts:

Ctrl + "+": New cell, row, ya column insert karna.
Ctrl + "-": Selected cell, row, ya column delete karna.

Formula Shortcuts:

Alt + =: SUM function insert karna.
Ctrl + ` (grave accent): Formula aur values toggle karna.
F4: Absolute reference ko toggle karna (A1 se $A$1).
Shift + F3: Formula insert karna.
Ctrl + Shift + Enter: Array formulas ko enter karna.
F9: Formulas ko calculate karna.

Excel Formatting aur Productivity Tips:

1. Hiding Rows, Columns, aur Sheets:

Row ko Hide karna: Row number par right-click karein aur "Hide" select karein.
Column ko Hide karna: Column letter par right-click karein aur "Hide" select karein.
Sheet ko Hide karna: Sheet tab par right-click karein aur "Hide" select karein.

2. Hiding Cells ke liye:

Cell ko Blank ya Invisible banane ke liye:
1. Cell ko select karein.
2. Right-click karein aur "Format Cells" select karein.
3. Custom Format me jaake type karein ;;; aur OK karein.

3. Excel Settings and Customizations:

Default Save Location Change:
- File > Options > Save mein jaake "Default file location" change kar sakte hain.
Quick Access Toolbar Customize karna:
1. Excel ke upar left corner me "Customize Quick Access Toolbar" button par click karein.
2. Frequently used commands jaise "Save", "Undo", "Redo", etc. ko add karein.
Status Bar Customize karna: Status bar ko right-click karein, aur un options ko tick karein jo aap dekhna chahte hain, jaise "Average", "Count", ya "Sum".

4. Conditional Formatting:

Conditional Formatting ka use karke aap specific conditions ke basis pe cells ko automatically highlight kar sakte hain. Example:

Home > Conditional Formatting > New Rule: Yahan aap different rules bana sakte hain jaise "Cell value is greater than 100", "Duplicate Values", etc.

5. Flash Fill (Excel 2013 se aage):

Agar aap Excel 2013 ya uske baad ka version use kar rahe hain toh Flash Fill ek kaafi helpful feature hai. Example: Agar aapko names ko "FirstName LastName" se "LastName, FirstName" mein convert karna hai, toh ek pattern dekar Flash Fill automatically baaki cells ko fill kar dega.

6. Data Validation:

Data Validation ka use karke aap specific rules laga sakte hain jisse sirf valid data enter ho sake.

Data > Data Validation: Aap list, number range, ya specific text validation set kar sakte hain.

7. Pivot Tables:

Pivot Table ka use karke aap large datasets ko easily summarize kar sakte hain.

Insert > PivotTable: Yahan aap apna data select karke drag-and-drop functionality se quick summary bana sakte hain.

8. Freeze Panes:

Freeze Panes ka use karke aap apne top row ya first column ko freeze kar sakte hain, taaki scrolling karte waqt wo visible rahein.

View > Freeze Panes: Top row ya first column ko freeze karne ka option choose karein.

9. Grouping and Ungrouping:

Aap cells ko group kar sakte hain taaki aapki worksheet organized rahe.

Select rows/columns > Data > Group: Group banane ke liye.
Ungroup karne ke liye unhi steps ko reverse karein.

0. Auto-Fill Handle:

Auto-fill Handle ka use karke aap serial numbers, dates, ya patterns ko quickly fill kar sakte hain. Excel automatically pattern ko detect karke fill karta hai.

11. Text to Columns:

Jab aapko ek column mein multiple data types ko split karna ho (jaise "FirstName LastName" ko alag alag karna), toh aap Text to Columns ka use kar sakte hain:

Data > Text to Columns: Delimited ya Fixed Width ko select karein aur wizard ko follow karein.

Excel ke Pro Tips:

Use Shortcuts Practice: Excel mein shortcut keys ka regularly use karna productivity ko increase kar deta hai.
Regular Backups: Apni files ka backup bana ke rakhein, taaki accidental data loss se bacha ja sake.
Keep it Organized: Rows, columns, aur sheets ko proper labels aur formatting ke saath use karein taaki data clear aur understandable rahe.

Excel 2010 ka use advanced level tak practice karne ke baad aap easily large data sets aur complex calculations ko handle kar sakte hain.

1. Formula Bar ko Hide/Unhide kaise karein:

Formula bar wo area hota hai jahan aap cells ke andar likha formula ya data dekh sakte hain aur usko edit kar sakte hain.

Formula Bar ko Hide karna:
1. View Tab mein jaayein.
2. Show/Hide group mein Formula Bar checkbox dhoondein.
3. Formula Bar ka checkbox untick kar dein.
- Formula bar hide ho jayegi aur aapko zyada screen space milega.
Formula Bar ko Unhide karna:
1. Wahi steps repeat karein.
2. Formula Bar ka checkbox dubara tick karein.
- Formula bar wapas aa jayegi.

2. Sheet Size (Zoom) ko Adjust Karna:

Aap Excel sheet ka size ya zoom level adjust kar sakte hain taaki aap apne data ko efficiently dekh sakein.

Zoom In/Out karna:
- Ctrl + Scroll: Aap mouse ke scroll wheel ka use karke easily sheet ko zoom in ya zoom out kar sakte hain.
- View Tab > Zoom: Aapko "Zoom" ka option milta hai jahan aap custom zoom percentage set kar sakte hain (25% se 400% tak).
- Zoom Slider: Excel ke bottom right corner mein ek zoom slider hota hai jisse aap left-right slide karke zoom level adjust kar sakte hain.
Page Layout View:
- Aap "Page Layout" view ka use karke dekhsakte hain ki sheet print hone par kaise dikhegi.
- View Tab > Page Layout: Aap apne data ko as a page layout view dekh sakte hain, jisme aap margins aur page breaks bhi adjust kar sakte hain.

3. Rows aur Columns ki Width aur Height ko Adjust Karna:

Aap rows aur columns ki size ko apni zarurat ke hisaab se chhota ya bada kar sakte hain.

Manually Column Width ya Row Height Adjust karna:
1. Jis column ya row ki width/height change karni ho, uske letter/number (Column letter ya Row number) par right-click karein.
2. Column Width ya Row Height ko select karein.
3. Ek dialog box khulega, jisme aap manually value enter kar ke width ya height ko set kar sakte hain.
Auto-Fit Column Width:
- Agar aap chahte hain ki column ki width automatically adjust ho jaye content ke hisaab se:
1. Column ya multiple columns ko select karein.
2. Alt + H + O + I: Yeh shortcut automatically column width ko adjust kar dega based on the content.
Auto-Fit Row Height:
- Similarly, rows ko auto-fit karne ke liye:
1. Row ya rows ko select karein.
2. Alt + H + O + A: Yeh shortcut row height ko content ke according adjust karega.

4. Gridlines ko Hide/Unhide karna:

Gridlines cells ke beech mein aati thin lines hoti hain jo data ko alag-alag dikhati hain. Kabhi-kabhi aapko presentation ke liye gridlines hide karna useful hota hai.

Gridlines Hide/Unhide karne ke liye:
1. View Tab mein jaayein.
2. Show/Hide Group mein Gridlines ka checkbox untick karein.
- Gridlines hide ho jayegi.
1. Wapas show karne ke liye checkbox ko tick kar dein.

5. Sheet Tabs ko Hide/Unhide karna:

Kabhi aapko kuch specific sheets ko hide karne ki zarurat padti hai.

Sheet Tabs ko Hide karna:
1. Jis sheet ko hide karna ho, uspe right-click karein.
2. Hide option choose karein.
- Sheet tab hide ho jayega.
Sheet Tabs ko Unhide karna:
1. Koi bhi sheet tab par right-click karein.
2. Unhide option ko select karein.
3. Ek dialog box khulega jisme hidden sheets dikhengi, usme se jo sheet aapko wapas lana hai, usse select karein aur OK karein.

6. Freeze Panes ka Use:

Freeze Panes ka use karke aap specific rows ya columns ko freeze kar sakte hain, taaki aap scroll karte waqt woh visible rahein.

Freeze Top Row:
- View Tab > Freeze Panes > Freeze Top Row: Isse top row scroll karte waqt hamesha visible rahega.
Freeze First Column:
- View Tab > Freeze Panes > Freeze First Column: First column hamesha visible rahega jab aap horizontal scrolling karte hain.
Custom Pane Freeze:
1. Jis row ya column ke neeche ya daaye aap freeze karna chahte hain, us row ya column ko select karein.
2. View Tab > Freeze Panes > Freeze Panes: Aap custom rows ya columns ko freeze kar sakte hain.

7. Split Screen View:

Aap apni worksheet ko split kar sakte hain taaki aap ek hi time par alag-alag sections dekh sakein.

Split View ko Enable karna:
- View Tab > Split: Yeh aapke current selection ke hisaab se screen ko split karega.
- Aap vertical ya horizontal splits bana sakte hain. Iska use karne se large datasets ko dekhna easy ho jata hai.
Split ko Disable karna:
- View Tab > Split: Wapas click karne se split view disable ho jayega.

8. Multiple Sheets ke saath Work Karna:

Agar aap ek workbook mein multiple sheets ke saath kaam kar rahe hain, toh kai tips helpful ho sakti hain:

Sheet Rename karna:
1. Sheet tab par double-click karein aur naya naam type karein.
Color Code Sheets:
1. Sheet tab par right-click karein.
2. Tab Color select karein aur alag-alag colors set karein taaki sheets ko easily identify kiya ja sake.
Multiple Sheets ko Select karna:
- Ctrl hold karke multiple sheets select karein. Agar aapko continuously sheets select karni hain, toh Shift hold karke first aur last sheet par click karein, aur beech ki sabhi sheets select ho jayengi.

9. Custom Views Create karna:

Excel mein Custom Views ka use karke aap different layouts ya zoom levels save kar sakte hain.

Custom View Save karna:
1. View Tab > Custom Views: Yahan se aap "Add" kar ke apna custom view save kar sakte hain.
2. Ek custom name dekar save karein.
- Aap future mein wapas apna saved view activate kar sakte hain.

10. Show Formulas/Hide Formulas:

Aap formulas ko as text dikhana ya hide karna chahte hain, toh is feature ka use kar sakte hain:

Formulas Show karna:
1. Ctrl + ` (grave accent): Yeh shortcut cell values aur formulas ke beech toggle karta hai.
- Aap directly cells mein likhe formulas dekh sakte hain.

11. Workbook aur Worksheet ko Protect karna:

Kabhi kabhi sensitive data ke liye workbook ya worksheet ko password-protect karna zaroori hota hai.

Worksheet ko Protect karna:
1. Review Tab > Protect Sheet: Yahan se aap password set kar sakte hain taaki koi aur person is sheet ko edit na kar sake.
Workbook ko Protect karna:
1. Review Tab > Protect Workbook: Isse aap poore workbook ko structure ya windows ke against protect kar sakte hain.

Yeh sab tips aur tricks MS Excel ko customize karne aur efficiently use karne mein help karenge. In features ka use kar ke aap Excel mein apni productivity ko next level tak le ja sakte hain.

Aditya Tripathi Portfolio

Search This Blog