Excel_Samadhaan: Shortcuts

Showing posts with label Shortcuts. Show all posts

Friday, 26 February 2021

Extract Unique Data from Duplicate | Without Copy Paste | AdvancedSortFi...

हेलो दोस्तों , स्वागत है आपका एक नए धमाकेदार जानकारी में जो एक्सेल में आपको काम करने में बहुत ही मदद करेगा।

दोस्तों हमारे पास डाटा एनालिसिस करते समय बड़े पैमाने में डाटा होता है , जिसमे काफी सारा डाटा डुप्लीकेट होता है या फिर चेक करना पड़ता है डुप्लीकेट है या नहीं।

या फिर डुप्लीकेट डाटा में से हमें यूनिक लिस्ट बनानी पड़ती है।

दोस्तों मैंने इसी विषय का youtube में विडिओ बनाया हुआ है आप इसे भी चेक कर सकते है।

ऐसे ही Video देखने के लिखे मेरे YouTube Channel को Subscribe कीजिये।

आगे के YouTube लिंक पे क्लिक करें #Excel_Samadhaan

या फिर निचे दिया हुआ यूट्यूब वीडियो देखे. या फिर क्लिक करे Watch Video to better understand

तो दोस्तों चले हम स्टार्ट करते है अपना सबसे आसान तरीका जो हमें बिना कॉपी पेस्ट के यूनिक डाटा की अलग से लिस्ट बना देगा।

दोस्तों आप को में बता रहा हु Advanced sort filter के option के बारे में।

दोस्तों ये ऑप्शन आपको एक्सेल में Menu Bar में Sort & Filter के Tab में Advance Filter में मिल जायेगा।

निचे दिखाया हुआ पिक्चर देखिये।

इस फोटो में दिखाया हुआ विंडो ओपन हो जायेगा। अब हम इसके बारे में पहले बात करते है इसे जान लेते है ;

इसमें दिखाया हुआ पहला रेडियो बटन आपको ऑप्शन दे रहा है की यूनिक डाटा या फिर रिकार्ड्स आपको ओरिजिनल डाटा को रेप्लस करने देगा।

और जो दूसरा रेडियो बटन है वो आपको आपका जो यूनिक डाटा है उसे अलग लोकेशन पे पेस्ट करने का ऑप्शन दे रहा है।

Important: अलग लोकेशन same sheet का होना चाहिए। उसी शीट में कोई भी सेल सेलेक्ट कर सकते है।

List Range: मतलब वो डाटा रेंज जिसको आप यूनिक लिस्ट में शामिल करना चाहते है उससे सेलेक्ट कर लीजिये , हैडर के साथ में।

Criteria Range: इसमें वो सेल सेलेक्ट करेंगे जिसके आधार में आप डाटा को यूनिक बनाना चाहते है। उसी रेंज का सिर्फ Header को सेलेक्ट करना है। सिर्फ Header वाले सेल को।

Copy to : इसमें आपको वो सेल सेलेक्ट करना है जहा पर आप अपनी यूनिक को पेस्ट करना चाहते हो, सिर्फ एक ही सेल सेलेक्ट करना है।

Unique Records Only इस ऑप्शन को टिक करना बहुत ही जरुरी है ये आपको आपके डुप्लीकेट डाटा में से यूनिक डाटा ही निकल कर देगा इसलिए इस चेकबॉक्स को चेक करना बहुत ही जरुरी है।

ऐसे ही Video और एक्सेल की जानकारी देखने के लिखे मेरे YouTube Channel को Subscribe कीजिये।

आगे के YouTube लिंक पे क्लिक करें #Excel_Samadhaan और एक्सेल सिखने का आनंद लीजिये.

Friday, 21 February 2020

OFFSET function And the Sum formula

OFFSET function And the Sum formula

About : To calculate the total of a specific group of months the =OFFSET() function has been used.

Category : Lookup
Location : Built-In

Sometimes it is necessary to base a calculation on a set of cells in different locations.
An example would be when a total is required from certain months of the year, such as the last 3 months in relation to the current date.

One solution would be to retype the calculation each time new data is entered, but this would be time consuming and open to human error.

A better way is to indicate the start and end point of the range to be calculated by using the =OFFSET() function.

The =OFFSET() picks out a cell a certain number of cells away from another cell.
By giving the =OFFSET() the address of the first cell in the range which needs to be totaled, we can then indicate how far away the end cell should be and the =OFFSET() will give us the address of cell which will be the end of the range to be totaled.

The =OFFSET() needs to know three things;

A cell address to use as the fixed point from where it should base the offset.
How many rows it should look up or down from the starting point.
How many columns it should look left or right from the starting point.

=SUM(D3:OFFSET(D3,0,0))
This example uses D3 as the starting point and offsets no rows or columns which
results in the range being summed as D3:D3.

=SUM(D3:OFFSET(D3,0,1))
This example uses D3 as the starting point and offsets 1 col to pick out cell D3 resulting in a the range D3:D3 being summed.

=SUM(D3:OFFSET(D3,0,2))
This example uses D3 as the starting point and offsets 2 cols to pick out cell D3 resulting in a the range D3:D3 being summed.

Using =OFFSET() Twice In A Formula

The following examples use =OFFSET() to pick both the start and end of the range which needs to be totaled.

=SUM(OFFSET(E45,0,1):OFFSET(E45,0,1))

The cell D3 has been used as the starting point for both offsets and each has been offset by just 1 column. The result is that just cell E3 is used as the range E3:F3 for the sum function to calculate.

=SUM(OFFSET(D3,0,1):OFFSET(D3,0,2))

The cell D3 has been used as the starting point of both offsets, the first offset is offset by 1 column, the second by 2 columns. The result is the range E3:F3 which is then totaled.

=SUM(OFFSET(D3,0,1):OFFSET(D3,0,3))

The cell D3 has been used as the starting point for both offsets, the first offset is offset by 1 column, the second by 3 columns. The result is the range E3:G3 which is then totaled.

Lets understand with some more examples

The following table shows five months of data.
To calculate the total of a specific group of months the =OFFSET() function has been used.
The Start and End dates entered in cells E2 and E3 are used as the offset to produce a range which can be totaled.

=SUM(OFFSET(C10,0,MONTH($E$2)):OFFSET(C10,0,MONTH($E$3)))

Explanation

The following formula represent a breakdown of what the =OFFSET function does.
The formula displayed below are only dummies, but they will update
as you enter dates into cells E2 and E3.

Formula 1.

=SUM( OFFSET(C10,0,MONTH(E2)) : OFFSET(C10,0,MONTH(E3)) )
This is the actual formula entered by the user.

Formula 2.

=SUM(OFFSET(C10,0,MONTH(2)) : OFFSET(C10,0,MONTH(3)))
="=SUM( OFFSET(C10,0,MONTH("&TEXT(MONTH($E$2),"0")&")) : OFFSET(C10,0,MONTH("&TEXT(MONTH($E$3),"0")&")) )"

This shows how the =MONTH function calculates the month number.
In this example the values of the months are 2 and 3 for Feb and Mar.
These values are the 'offsets' relative to cell C10.

Formula 3.

=SUM(OFFSET(C10,0,2) : OFFSET(C10,0,33))
="=SUM( OFFSET(C10,0,"&TEXT(MONTH($E$2),"0")&") : OFFSET(C10,0,"&TEXT(MONTH($E$3),"0")&") )"

This shows where the month numbers are used in the =OFFSET function.

Formula 4.

=SUM(E10:F10)
=SUBSTITUTE("=SUM( "&@CELL("address",OFFSET(C10,0,MONTH($E$2)))&":"&@CELL("address",OFFSET(C10,0,MONTH($E$3)))&" )","$","")

This shows how the =OFFSET eventually equates to cell addresses to be used as a range for the =SUM function.

Share This On:

Thursday, 20 February 2020

TRANSPOSE ARRAY FORMULA

TRANSPOSE

About : Returns the transpose of an array

Category : Lookup
Location : Built-In

Transpose Formula

=Transpose(A1:C5) + Ctrl+Shift+Enter

Check out the Excel Shortcut Keys

What Does It Do ?

This function copies data from a range, and places in it in a new range, turning it so that the data originally in columns is now in rows, and the data originally in rows is in columns.

The transpose range must be the same size as the original range.

The function needs to be entered as an array formula.

To enter an array formula you must first highlight all the cells where the formula is required.
Next type the formula, such as =TRANSPOSE(A1:A5).
Finally press Ctrl+Shift+Enter to confirm it.

If changes need to be made to the formula, the entire array has to be highlighted, the edits can then be made and the Ctrl+Shift+Enter used to confirm it.

Syntax

=TRANSPOSE(Range)

Formatting

No special formatting is needed.

Check out the Excel Shortcut Keys

Share This on :

Wednesday, 29 January 2020

Top 79 microsoft -Excel- Shortcuts (Advanced)

Excel shortcuts keys which helps you to perform action faster and easily.

Below are the few most useful shortcuts.

Ctrl+ N: Create a new workbook

Ctrl+O: Open an existing workbook

Ctrl+S: Save a Excel workbook

F12: Open the Save As dialog box

Ctrl+W: Close a workbook

Ctrl+F4: Close Excel

F4: Repeat the last command or action. For example, if the last thing you typed in a cell is “hello,” or if you change the font color, clicking another cell and pressing F4 repeats that action in the new cell.

Shift+F11: Insert a new worksheet

Ctrl+Z: Undo an action

Ctrl+Y: Redo an action

Ctrl+F2: Switch to Print Preview

F1: Open the Help pane

Alt+Q: Go to the “Tell me what you want to do” box

F7: Check spelling

F9: Calculate all worksheets in all open Excel workbooks

Shift+F9: Calculate active worksheets

Alt or F10: Turn key tips on or off

Ctrl+F1: Show or hide the ribbon

Ctrl+Shift+U: Expand or collapse the Excel formula bar

Ctrl+F9: Minimize the workbook window

F11: Create a bar chart based on selected data (on a separate Excel sheet)

Alt+F1: Create an embedded bar chart based on select data (same sheet)

Ctrl+F: Search in a spreadsheet, or use Find and Replace

Alt+F: Open the File tab menu

Alt+H: Go to the Home tab

Alt+N: Open the Insert tab

Alt+P: Go to the Page Layout tab

Alt+M: Go to the Formulas tab

Alt+A: Go to the Data tab

Alt+R: Go to the Review tab

Alt+W: Go to the View tab

Alt+X: Go to the Add-ins tab

Alt+Y: Go to the Help tab

Ctrl+Tab: Switch between open workbooks

Shift+F3: Insert a function

Alt+F8: Create, run, edit, or delete a Excel macro

Alt+F11: Open the Microsoft Excel Visual Basic For Applications Editor

Left/Right Arrow: Move one cell to the left or right

Ctrl+Left/Right Arrow: Move to the farthest cell left or right in the row

Up/Down Arrow: Move one cell up or down

Ctrl+Up/Down Arrow: Move to the top or bottom cell in the column

Tab: Go to the next cell

Shift+Tab: Go to the previous cell

Ctrl+End: Go to the most bottom right used cell

F5: Go to any cell by pressing F5 and typing the cell coordinate or cell name.

Home: Go to the leftmost cell in the current row (or go to the beginning of the cell if editing a cell)

Ctrl+Home: Move to the beginning of a worksheet

Page Up/Down: Move one screen up or down in a worksheet

Alt+Page Up/Down: Move one screen to the right or left in a Excel worksheet

Ctrl+Page Up/Down: Move to the previous or next worksheet

Shift+Left/Right Arrow: Extend the cell selection to the left or right

Shift+Space: Select the entire row

Ctrl+Space: Select the entire column
Ctrl+Shift+Space: Select the entire worksheet

F2: Edit a cell

Shift+F2: Add or edit a cell comment

Ctrl+X: Cut contents of a cell, selected data, or selected cell range

Ctrl+C or Ctrl+Insert: Copy contents of a cell, selected data, or selected cell range

Ctrl+V or Shift+Insert: Paste contents of a cell, selected data, or selected cell range

Ctrl+Alt+V: Open the Paste Special dialog box

Delete: Remove the contents of a cell, selected data, or selected cell range

Alt+Enter: Insert a hard return within a cell (while editing a cell)

F3: Paste a cell name (if cells are named in the Excel worksheet)

Alt+H+D+C: This Shortcut Delete column

Esc: Cancel an entry in a cell or the formula bar

Enter: Complete an entry in a cell or the formula bar

Ctrl+B: Add or remove bold to the contents of a cell, selected data, or selected cell range

Ctrl+I: Add or remove italics to the contents of a cell, selected data, or selected cell range

Ctrl+U: Add or remove underline to the contents of a cell, selected data, or selected cell range

Shortcut Keys

Alt+H+H: Select a fill color

Alt+H+B: Add a border

Ctrl+Shift+&: Apply outline border

Ctrl+Shift+_ (Underline): Remove outline border

Ctrl+9: Hide the selected rows

Ctrl+0: Hide the selected columns

Ctrl+1: Open the Format Cells dialog box

Ctrl+5: Apply or remove strikethrough

Ctrl+Shift+$: Apply currency format

Ctrl+Shift+%: Apply percent format

Excel_Samadhaan

Home