Faysal Easy Excel

Subtotal() is basically used to get instant summary of a filtered data table. It is used with the support of AutoFilter tool or can be used alone. If you use AutoFilter for Subtotal then all hidden rows become visible. But if you do not use AutoFilter then hidden rows becomes hidden. Subtotal Function: The Subtotal function structure is: =SUBTOTAL(function_number,ref1) Function Details: There are 2 main parts of this function. These are: function_number: Subtotal function can calculate many things like, SUM, COUNT, AVERAGE etc. Each of these function are stored with a number in Subtotal(). What you need to do is mention the Number first from below list: Image 1: SUBTOTAL() number chart ref1: This is nothing but a Range. You can Select a single cell or few cells selected randomly, or a range of cells. Example: Based on below data table assume that, you need to know the Salary of Mr. A in D4 cell. Image 2: Sample data table for Subtotal In D4 ce

While saving MS Excel file, sometimes an warning message appears: "Be Careful! parts of your document may include personal information that can't be removed by the Document Inspector" Image 1: Warning Message It's totally annoying.  Here's how you can fix it: Step 1: Go to File menu in the upper left hand corner, then Options | Trust Center | Trust Center Settings | Privacy Options . Step 2: Uncheck the check box that says " Remove personal information from file properties on save ", then hit OK .

Sometimes while I open any MS Excel file, it shows a message regarding "update the link". These dialog box will come while the excel file contains any external links in formula. It is very much disturbing for working on this file. To find the all external links, just do the followings: 1. Press Ctrl+F to open "Find" dialog box. 2. Type "/" or ".x" or "[" in the find what text area. 3. search it for whole sheet or workbook. After found delete or replace the formula using the Replace tab of "Find" dialog box. Note: The external link can also be found in "Conditional Formatting" dialog box. If you see no result after finding the whole workbook or worksheets then check the conditional formatting dialog box of each sheets by going  "Style" tab - "Conditional Formatting" menu - "Manage Rules" menu.

Whenever You need to Index with multiple criteria (2, 3 or more match criteria) just follow the below example: Problem: From Table A (Cell range D4:F13), I need to find out the Unit Price of Plant in every Month for summary in Table B (cell range H4:J6). Image 1: Table A and Table B Solution: This can be done in various ways, but due to this article is based on Index() and Match(). I will go for it. But wait a minute, I will use an Array Formula here. So how to do it? Simple: Step 1: In J2 Cell of Table B under Unit_Price, enter the below formula and Press Ctrl+Shift+Enter: =INDEX($E$5:$E$13,MATCH($H5&$J5,$D$5:$D$13&$F$5:$F$13,0) Image 2: Table B - Result Step 2: After pressing the Ctrl+Shift+Enter the formula automatically add two curly braces {} in front and end of the formula. So that, computer will read it as an Array Formula. Step 3: This will returns the value 52 for Jan-Mint. Now drug it. Then it will returns 41 and 71 for Feb-Cilantro a