Gyaanleikh

Excel is a powerful tool, and knowing a few mouse tricks can make your daily tasks much easier and more efficient. In this blog, we’ll explore some essential double-click tricks that can help you work smarter in Excel. 1. Double Click Pivot Table Item to Drill Down Pivot Tables are a fantastic way to summarize and analyze data. But did you know you can drill down into your data with just a double click? How to Use It: Create a Pivot Table: Insert a Pivot Table from your data range. Double Click on a Value: In the Pivot Table, find a summarized value that you want to explore in detail. Double-click on that cell. View Detailed Data: Excel will automatically create a new worksheet showing the detailed data that makes up that value. Why It’s Useful: This trick is incredibly helpful for quickly getting insights into the underlying data without manually filtering or searching through the original dataset. 2. Double Click Splitter to Activate Split Screen Working with large spreadsheets can b...

CTRL+A   Select the entire worksheet CTRL+B   Apply or remove Bold formatting to font CTRL+C   Copy CTRL+D   Copy the contents and format of the topmost cell of a selected range into the cells below CTRL+E   Flash Fill CTRL+F   Find CTRL+G   Go To CTRL+H   Find and Replace CTRL+I   Apply or remove Italics formatting to font CTRL+J   Not assigned. Still available. CTRL+K   Open the Insert hyperlink dialog CTRL+L   Display the Create Table dialog (Alternative is CTRL+T) CTRL+M  Not assigned. Still available. CTRL+N   Create a new Workbook CTRL+O   Display File Open dialog box CTRL+P   Invokes Print CTRL+Q   Display the Quick Analysis options for selected cells that contain data CTRL+R   Copy the contents and format of the leftmost cell of a selected range into the cells to the right CTRL+S    Save the workbook CTRL+T    Display the Create T...

CTRL              Action Shortcut CTRL+F1 Displays or hides the ribbon CTRL+F2 Displays the print preview area on the Print tab in the Backstage view CTRL+F3 Opens Name manager CTRL+F4 Closes the selected workbook window CTRL+F5 Restores the window size of the selected workbook window CTRL+F6 Switches between 2 Excel windows CTRL+F7 Performs the Move command on the workbook window when it is not maximized. Use                            the arrow keys to move the window, and when finished press Enter, or Esc to cancel CTRL+F8 Performs the Size command when a workbook is not maximized CTRL+F9 Minimizes a workbook window to an icon CTRL+F10 Maximizes or restores the selected workbook window CTRL+F11 Insert a new sheet named Macro1,2….so on CTRL+F12 Display File Open dialog box 

These formulas and explanations may change according to the updates of the excel application, please check for the same.  1. SUM of Digits when cell Contains all Numbers If you cell contains only numbers like A1:= 7654045, then following formula can be used to find sum of digits =SUM(--MID(A1,SEQUENCE(LEN(A1)),1)) =SUMPRODUCT(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)) =SUM(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1)) If A1 is blank, then to handle error, you can enclose above formulas into an IFERROR block. 2. SUM of Digits when cell Contains Numbers and non Numbers both If your cell contains non numbers apart from numbers like A1:= 76$5a4b045%d, then following formulas can be used to find sum of digits =SUM(IFERROR(--MID(A1,SEQUENCE(LEN(A1)),1),0)) =SUMPRODUCT((LEN(A1)-LEN(SUBSTITUTE(A1,ROW($1:$9),"")))*ROW($1:$9)) =SUM(IFERROR(--MID(A1,ROW(INDIRECT("1:"&LEN(A1))),1),0)) 3. A List is Unique or Not (Whether it has duplicates) Assuming...

Ctrl+Shift Ctrl+Shift, then scroll your mouse wheel up to go left, down to go right Ctrl+Shift+A Insert argument names and parentheses when the insertion point is to the right of a function name in a formula Ctrl+Shift+F (or P) Format fonts in the Format Cells dialog Ctrl+Shift+G Open the Workbook Statistics dialog Ctrl+Shift+L Apply / Remove Auto-filter Ctrl+Shift+O Select all cells containing Notes (earlier called comments) Ctrl+Shift+U Expand/Collapse Formula bar Ctrl+Shift++ Open the Insert dialog to insert blank cells Ctrl+Shift+: Enter the current time Ctrl+Shift+& Apply an outline border to the selected cells Ctrl+Shift+_ Remove the outline border from the selected cells Ctrl+Shift+~ Apply the General number format Ctrl+Shift+$ Apply the Currency format with two decimal places (negative numbers in parentheses) Ctrl+Shift+% Apply the Percentage format with no decimal places Ctrl+Shift+^ Apply the Scientific number format with two decimal places Ctrl+Shift+# Apply the Date for...

Workbook  CTRL+O Opens a workbook CTRL+N Create a new workbook CTRL+P Displays the print dialog box CTRL+W Closes the selected workbook  CTRL+F2 Displays print view  F9 Calculates all open workbooks  SHIFT+F9 Calculates the active worksheet  CTRL+F10 Maximize / restore workbook  SHIFT+F11 Inserts a new worksheet  F12 Displays the Save As dialog box  EDITING  CTRL+SHIFT+ Enters the current time  CTRL+              Enters the current date  CTRL+C         Copies the selected cells  CTRL+S         Save the workbook CTRL+D         Copies topmost cell of a range into the cells below  CTRL+R         Copies leftmost cell of a  range into cells to right CTRL+F         Find dialog box  CTRL+H         Replace dialog box...

1. Financial Function - Calculate EMI  You want to take a loan and you want to calculate EMI OR you want to build an EMI calculator in Excel. It is a fairly easy job to do -    You will need to use PMT function for this. It has following structure -    PMT(rate, nper, pv, [fv], [type])    rate : You rate of interest    nper : No. of payments. Your nper and rate should be on the same scale. i.e if you are  planning to pay up monthly, the rate in your formula should be monthly only. Generally,  interest rate is specified yearly i.e. 10.5% per year. This you should divide by 12 to arrive  at monthly rate. Hence, if you wanted 3 years loan, it means nper would 3x12=36 months.  If it is quarterly, rate = 10.5%/4 = 2.625% and nper would be 3x4 = 12  If it is annual, rate = 10.5% and nper = 3    pv : Your loan amount. You will need to put negative value of this in your formula. If you  don't put negative value...

SUMIFS is an Excel function designed for summing values based on multiple criteria. It allows users to specify different conditions in various ranges, summing only the values that meet all specified criteria. The syntax provides a sum range, multiple criteria ranges, and their corresponding conditions. SYNTAX =sumifs(sum_range, criteria_range1, criteria1, (criteria_range2, criteria2),....) sum_range: The range of cells to sum. criteria_range1: The range to apply the first condition. criteria1 : The condition to be met in criteria_range1 [criteria_range2, criteria2]:Additional ranges and their respective conditions EXAMPLES 1. Basic Sumifs  Result: $800 – Sum of Sales where Product is A 2. Mutiple Condition Result: $800 - Sum of Revenue where Region is South and Quarter is 2 3. Dynamic Data range  Result: 300 - Sum of Values where Category is B, Type is Y, and Date is within the last 30 days Why SUMIFS instead of SUMIF? SUMIFS Allows for multiple criteria ranges....