Generated by All in One SEO v4.9.0, this is an llms.txt file, used by LLMs to index the site. # Ashish Mathur's Blog ## Sitemaps - [XML Sitemap](https://www.ashishmathur.com/sitemap.xml): Contains all public & indexable URLs for this website. ## Posts - [Knowledge Base](https://www.ashishmathur.com/knowledge-base/) - [Unpivot data with a formula](https://www.ashishmathur.com/unpivot-data-with-a-formula/) - Many a times, one may want to convert a column expanding data to a row expanding one (also referred to as "Unpivoting/flattening a dataset". The easiest way to do so is with the usage of Power Query (referred to as the Query Editor in some versions). The technique to unpivot a dataset with the help # - [After filtering a dataset, allow the user to display only specific columns in the result](https://www.ashishmathur.com/after-filtering-a-dataset-allow-the-user-to-display-only-specific-columns-in-the-result/) - [Determine latest condition of each equipment and show a month wise count](https://www.ashishmathur.com/determine-latest-condition-of-each-equipment-and-show-a-month-wise-count/) - [Show Balance outstanding everyday even if data for everyday is not available](https://www.ashishmathur.com/show-balance-outstanding-everyday-even-if-data-for-everyday-is-not-available/) - [Performing an iterative lookup to return closest match](https://www.ashishmathur.com/performing-an-iterative-lookup-to-return-closest-match/) - If the lookup_value of is not found in the first column of the lookup_table, then reduce the length of the lookup_value by one each time and then perform the lookup. - [Worksheet formulas in a newly copied worksheet should point to the previous worksheet](https://www.ashishmathur.com/worksheet-formulas-in-a-newly-copied-worksheet-should-point-to-the-previous-worksheet/) - Refer formulas to previous sheet - [Analyse membership changes from year to year](https://www.ashishmathur.com/analyse-membership-changes-from-year-to-year/) - [Segregating data appearing in a single column into multiple columns where there is blank row between records](https://www.ashishmathur.com/segregating-data-appearing-in-a-single-column-into-multiple-columns-where-there-is-blank-row-between-records/) - [Show text entries in the value area section of a Pivot Table after meeting certain conditions](https://www.ashishmathur.com/show-text-entries-in-the-value-area-section-of-a-pivot-table-after-meeting-certain-conditions/) - [Count tasks by status](https://www.ashishmathur.com/count-tasks-by-status/) - [Segment towns according to volume contribution and market share with a slicer](https://www.ashishmathur.com/segment-towns-according-to-volume-contribution-and-market-share-with-a-slicer/) - [Segment towns according to volume contribution and market share](https://www.ashishmathur.com/segment-towns-according-to-volume-contribution-and-market-share/) - [Tabulating data from multiple unstructured Excel files](https://www.ashishmathur.com/tabulating-data-from-multiple-unstructured-excel-files/) - [Calculate rolling sum for the past week by ignoring blank cells](https://www.ashishmathur.com/calculate-rolling-sum-for-the-past-week-by-ignoring-blank-cells/) - [Split data into multiple tabs](https://www.ashishmathur.com/split-data-into-multiple-tabs/) - [Append data from multiple worksheets of multiple workbooks where each worksheet has a different heading](https://www.ashishmathur.com/append-data-from-multiple-worksheets-of-multiple-workbooks-where-each-worksheet-has-a-different-heading/) - [Visualising data flows using Custom Visuals](https://www.ashishmathur.com/visualising-data-flows-using-custom-visuals/) - Visualising data flows using Custom Visuals - [Rearrange a multi heading dataset into a single heading one which is Pivot ready](https://www.ashishmathur.com/rearrange-a-multi-heading-dataset-into-a-single-heading-one-which-is-pivot-ready/) - [Segment customers into dynamic buckets](https://www.ashishmathur.com/segment-customers-into-dynamic-buckets/) - [Summarise data by most recent status](https://www.ashishmathur.com/summarise-data-by-most-recent-status/) - [Compute gross annual salary from a know net annual salary figure](https://www.ashishmathur.com/compute-gross-annual-salary-from-a-know-net-annual-salary-figure/) - [Sales data modelling and interactive visualisations of an E-Commerce Company](https://www.ashishmathur.com/sales-data-modelling-and-interactive-visualisations-of-an-e-commerce-company/) - Sales data modelling and interactive visualisations of an E-Commerce Company - [Average a range of numbers with blanks appearing at random intervals](https://www.ashishmathur.com/average-a-range-of-numbers-with-blanks-appearing-at-random-intervals/) - Sum and/or average a range of cells with blanks appearing at random intervals - [Compute hours spent on projects given resource allocation](https://www.ashishmathur.com/compute-hours-spent-on-projects-given-resource-allocation/) - [Generate a list of missing invoice numbers](https://www.ashishmathur.com/generate-a-list-of-missing-invoice-numbers/) - Generate a list of missing invoice numbers - [Customer analysis by Country and time period](https://www.ashishmathur.com/customer-analysis-by-country-and-time-period/) - [Compute Relative Size Factor per vendor](https://www.ashishmathur.com/compute-relative-size-factor-per-vendor/) - [Extract tab name in cell](https://www.ashishmathur.com/extract-tab-name-in-cell/) - Extract worksheet or tab name in a cell - [Analyse free flowing text data or user entered remarks from multiple perspectives](https://www.ashishmathur.com/analyse-free-flowing-text-data-or-user-entered-remarks-from-multiple-perspectives/) - [Determine the top selling location for each product](https://www.ashishmathur.com/determine-the-top-selling-location-for-each-product/) - [Remove duplicates from each cell of a dataset](https://www.ashishmathur.com/remove-duplicates-from-each-cell-of-a-dataset/) - [Flex a Pivot Table to show data for x months ended a certain user defined month](https://www.ashishmathur.com/flex-a-pivot-table-to-show-data-for-x-months-ended-a-certain-user-defined-month/) - [Apply the SUMPRODUCT() function on the visible cells of a filtered range](https://www.ashishmathur.com/apply-sumproduct-on-visible-cells-of-a-filtered-range/) - Apply SUMPRODUCT on visible cells of a filtered range - [Merge 2 work schedules](https://www.ashishmathur.com/merge-2-work-schedules/) - [Show Project wise status in a Pivot Table](https://www.ashishmathur.com/show-project-wise-status-in-a-pivot-table/) - [Extract number from an alphanumeric string](https://www.ashishmathur.com/extract-phone-number-from-address-string/) - [Rearrange travel data to clearly show travel from and travel to locations](https://www.ashishmathur.com/rearrange-travel-data-to-clearly-show-travel-from-and-travel-to-locations/) - [Identify Customers that Organisations can upsell or cross sell their products to](https://www.ashishmathur.com/identify-customers-that-organisations-can-upsell-or-cross-sell-their-products-to/) - [Determine the lowest bidding vendor(s) for each product in a Pivot Table](https://www.ashishmathur.com/determine-the-lowest-bidding-vendors-for-each-product-in-a-pivot-table/) - Determine the lowest biddding vendor(s) for each product in a Pivot Table - [Sort, comma separated entries appearing in a cell, in ascending order](https://www.ashishmathur.com/sort-comma-separated-entries-appearing-in-a-cell-in-ascending-order/) - Sort, comma separated entries appearing in a cell, in ascending order - [Search for multiple phrases within a cell and extract all those phrases in another column](https://www.ashishmathur.com/search-for-multiple-phrases-within-a-cell-and-extract-all-those-phrases-in-another-column/) - Search for multiple phrases within a cell and extract all those phrases in another column - [Show sales only for corresponding months in prior years](https://www.ashishmathur.com/show-sales-only-for-corresponding-months-in-prior-years/) - Show only commensurate Sales of prior years - [Filtering on 2 date fields within one Table](https://www.ashishmathur.com/filtering-on-2-date-fields-within-one-table/) - Filtering on 2 date fields within one Table - [Compute transaction fee based on a tiered pricing model](https://www.ashishmathur.com/compute-transaction-fee-based-on-a-tiered-pricing-model/) - [Determine the most recent status after satisfying certain conditions](https://www.ashishmathur.com/determine-the-most-recent-status-after-satisfying-certain-conditions/) - [Perform an aggregation on Top x items after satisfying certain conditions](https://www.ashishmathur.com/perform-an-aggregation-on-top-x-items-after-satisfying-certain-conditions/) - Perform an aggregation on Top x items after satisfying certain conditions - [Compute the average of values against the 5 most recent dates of each Category](https://www.ashishmathur.com/compute-the-average-of-values-against-the-5-most-recent-dates-of-each-category/) - Compute the average of values against the 5 most recent dates of each Category - [Combine unique entries from a range of cells after satisfying a condition](https://www.ashishmathur.com/combine-unique-entries-from-a-range-of-cells-after-satisfying-a-condition/) - Combine unique entries from a range of cells after satisfying a condition - [Restructure the layout of datasets](https://www.ashishmathur.com/restructure-the-layout-of-datasets/) - [Prepare an invigilation schedule for each teacher by different time periods](https://www.ashishmathur.com/prepare-an-invigilation-schedule-for-each-teacher-by-different-time-periods/) - Prepare an invigilation schedule for each teacher by different time periods - [Split total patient hospitalisation days into multiple months](https://www.ashishmathur.com/split-total-patient-hospitalisation-days-into-multiple-months/) - Split total patient hospitalisation days into multiple months - [Determine the total number of projects by Status](https://www.ashishmathur.com/determine-the-total-number-of-projects-by-status/) - Determine the total number of projects by Status - [In a Pivot Table, compute highest revenue earned on any day from each customer and the date thereof](https://www.ashishmathur.com/in-a-pivot-table-compute-highest-revenue-earned-on-any-day-from-each-customer-and-the-date-thereof/) - In a Pivot Table, compute highest revenue earned on any day from each customer and the date thereof - [In a Pivot Table, show the most frequently appearing text entry by a certain parameter](https://www.ashishmathur.com/in-a-pivot-table-show-the-most-frequently-appearing-text-entry-by-a-certain-parameter/) - In a Pivot Table, show the most frequently appearing text entry by a certain parameter - [Filter a column of a Pivot Table on a certain condition but also show other items from that column](https://www.ashishmathur.com/filter-a-column-of-a-pivot-table-on-a-certain-condition-but-also-show-other-items-from-that-column/) - Filter a column of a Pivot Table on a certain condition but also show other items from that column - [Show months with no data which fall within a certain date range of a Pivot Table](https://www.ashishmathur.com/show-months-with-no-data-which-fall-within-a-certain-date-range-of-a-pivot-table/) - Show months with no data which fall within a certain date range of a Pivot Table - [Distribute projected revenue annually](https://www.ashishmathur.com/distribute-projected-revenue-annually/) - Distribute projected revenue annually - [Alter the behaviour of a filter/slicer from OR to AND](https://www.ashishmathur.com/alter-the-behaviour-of-a-filterslicer-from-or-to-and/) - Alter the behaviour of a filter/slicer from OR to AND - [Return the specific product which satisfies the user defined feature combination](https://www.ashishmathur.com/return-the-specific-product-which-satisfies-the-user-defined-feature-combination/) - Return the specific product which satisfies the user defined feature combination - [Fill out a matrix with a user defined value which has variable start and end points](https://www.ashishmathur.com/fill-out-a-matrix-with-a-user-defined-value-which-has-variable-start-and-end-points/) - Fill out a matrix with a user defined value which has variable start and end points - [Merge data from 2 data sources in a Pivot Table to get a Consolidated Project view](https://www.ashishmathur.com/merge-data-from-2-data-sources-in-a-pivot-table-to-get-a-consolidated-project-view/) - Merge data from 2 data sources in a Pivot Table to get a Consolidated Project view - [Compute standard hours spent on weekdays by Tier, Week, Month and Country](https://www.ashishmathur.com/compute-standard-hours-spent-on-weekdays-by-tier-week-month-and-country/) - Compute standard hours spent on weekdays by Tier, Week, Month and Country - [Determine cumulative interest payable on an annuity with varying time periods](https://www.ashishmathur.com/determine-cumulative-interest-payable-on-an-annuity-with-varying-time-periods/) - Determine cumulative interest payable on an annuity with varying time periods - [Determine number of learners who have completed different stages of multiple online courses](https://www.ashishmathur.com/determine-number-of-learners-who-have-completed-different-stages-of-multiple-online-courses/) - Determine number of learners who have completed different stages of multiple online courses - [Return best possible fit, to manually entered dimensions, with the intent to minimise wastage](https://www.ashishmathur.com/return-best-possible-fit-to-manually-entered-dimensions-with-the-intent-to-minimise-wastage/) - Return best possible fit, to manually entered dimensions with the intent to minimise wastage - [Sort individual columns of a Pivot Table based on a slicer selection](https://www.ashishmathur.com/sort-individual-columns-of-a-pivot-table-based-on-a-slicer-selection/) - [Generate a list of assignees for different projects based on a competency matrix](https://www.ashishmathur.com/generate-a-list-of-assignees-for-different-projects-based-on-a-competency-matrix/) - Generate a list of assignees for different projects based on a competency matrix - [Sales data modelling and interactive visualisations](https://www.ashishmathur.com/sales-data-modelling-and-interactive-visualisations/) - Sales data modelling and interactive visualisations - [Sum the largest 5 of the last 10 numbers in a row ignoring blanks](https://www.ashishmathur.com/sum-the-largest-5-of-the-last-10-numbers-in-a-row-ignoring-blanks/) - Sum the largest 5 of the last 10 numbers in a row ignoring blanks - [Discover insights with the "Sand Dance" visual, query your dataset with Natural Language Queries and Cortana integration](https://www.ashishmathur.com/discover-insights-with-the-sand-dance-visual-query-your-dataset-with-natural-language-queries-and-cortana-integration/) - Discover data using the "Sand Dance" custom visualization, Use natural language queries in Powerbi.com and leverage Cortana to fetch data/visuals from powerbi.com - [Convert a text entry into its number equivalent](https://www.ashishmathur.com/convert-a-text-entry-into-its-number-equivalent/) - Convert a text entry into its number equivalent - [Compute an average for the same day in the past 3 years](https://www.ashishmathur.com/compute-an-average-for-the-same-day-in-the-past-3-years/) - Compute an average for the same day in the past 3 years - [Show multiple text entries in one cell of a Pivot Table](https://www.ashishmathur.com/show-multiple-text-entries-in-one-cell-of-a-pivot-table/) - Show multiple text entries in one cell of a Pivot Table - [Summarise data with multiple wildcard OR conditions](https://www.ashishmathur.com/summarise-data-with-multiple-wildcard-or-conditions/) - Summarise data with multiple wildcard OR conditions - [Transpose data column wise](https://www.ashishmathur.com/transpose-data-column-wise/) - Transpose data column wise - [Compute product wise YTD Revenue from a matrix like/Cross tabular dataset](https://www.ashishmathur.com/compute-product-wise-ytd-revenue-from-a-matrix-likecross-tabular-dataset/) - Compute product wise YTD Revenue from a matrix like/Cross tabular dataset - [Workaround to the problem of creating a Pivot chart after using "% of row total" calculation in a Pivot Table](https://www.ashishmathur.com/workaround-to-the-problem-of-creating-a-pivot-chart-after-using-of-row-total-calculation-in-a-pivot-table/) - Create a Pivot chart after using "% of row" calculation - [Create a daily work schedule](https://www.ashishmathur.com/create-a-daily-work-schedule/) - Create a daily work schedule - [Perform an "Affinity analysis" to identify co-selling products](https://www.ashishmathur.com/perform-an-affinity-analysis-to-identify-co-selling-products/) - Perform an "Affinity analysis" to identify co-selling products - [Remove special characters from a string](https://www.ashishmathur.com/remove-special-characters-from-a-string/) - Remove special characters from a string - [Analyse all possible combinations of cheques received and identify the combination which gives maximum benefit to the Customer](https://www.ashishmathur.com/analyse-all-possible-combinations-of-cheques-received-and-identify-the-combination-which-gives-maximum-benefit-to-the-customer/) - Analyse all possible combinations of cheques received and identify the combination which gives maximum benefit to the Customer - [Remove special characters and numbers from an alphanumeric string](https://www.ashishmathur.com/remove-special-characters-and-numbers-from-an-alphanumeric-string/) - [Story telling with Excel Power BI](https://www.ashishmathur.com/story-telling-with-excel-power-bi/) - Story telling with Excel Power BI - [Filter the Rank Field in a Pivot Table](https://www.ashishmathur.com/filter-the-rank-field-in-a-pivot-table/) - Filter the Rank Field in a Pivot Table - [Remove duplicates from rows](https://www.ashishmathur.com/remove-duplicates-from-rows/) - Remove duplicates from rows - [Flip a string with conditions](https://www.ashishmathur.com/flip-a-string-with-conditions/) - Flip a string with conditions - [Consolidate multiple rows of data and remove blank rows](https://www.ashishmathur.com/consolidate-multiple-rows-of-data-and-remove-blank-rows/) - Consolidate multiple rows of data and remove blank rows - [Compute potential Sales of a retail outlet](https://www.ashishmathur.com/compute-potential-sales-of-a-retail-outlet/) - Compute potential Sales of a retail outlet - [Quantify combination courses opted by students](https://www.ashishmathur.com/quantify-combination-courses-opted-by-students/) - Quantify complimentary courses opted by students - [Performing a text to rows operation](https://www.ashishmathur.com/performing-a-text-to-rows-operation/) - Performing a text to rows operation - [Merge data from multiple cells into a single cell](https://www.ashishmathur.com/merge-data-from-multiple-cells-into-a-single-cell/) - Merge data from multiple cells into a single cell - [Remove duplicates after satisfying additional conditions](https://www.ashishmathur.com/remove-duplicates-after-satisfying-additional-conditions/) - Remove duplicates after satisfying additional conditions - [Identify buy and sell break points](https://www.ashishmathur.com/identify-buy-and-sell-break-points/) - Identify buy and sell break points - [Customise Row/Column appearances in Pivot Tables](https://www.ashishmathur.com/customise-rowcolumn-appearances-in-pivot-tables/) - Customise Row/Column appearances in Pivot Tables - [Perform a Competitor, Feature and Customer Analysis with the PowerPivot](https://www.ashishmathur.com/perform-a-competitor-feature-and-customer-analysis-with-the-powerpivot/) - Perform a Competitor, Feature and Customer Analysis with the PowerPivot - [Display text entries in the data area of a pivot table](https://www.ashishmathur.com/display-text-values-in-the-data-area-of-a-pivot-table/) - Instead of using mathematical functions in the data area of a pivot table, display text values in the data area. - [Dynamically filter data from one worksheet to another](https://www.ashishmathur.com/dynamically-filter-data-from-one-worksheet-to-another/) - Transfer filtered data to another worksheet with links - [Auto detect sum range when copying and pasting](https://www.ashishmathur.com/auto-detect-sum-range-when-copying-and-pasting/) - Auto detect sum range when copying and pasting - [Rank numbers in a range after satisfying conditions](https://www.ashishmathur.com/rank-numbers-in-a-range-after-satisfying-conditions/) - Rank numbers in a range after satisfying conditions - [Perform different calculations in the Subtotal/Grand Total column of a Pivot Table](https://www.ashishmathur.com/perform-different-calculations-in-the-subtotalgrand-total-column-of-a-pivot-table/) - Perform different calculations in the Subtotal/Grand Total column of a Pivot Table - [Extract City, State and Pin code from an address string](https://www.ashishmathur.com/extract-city-state-and-pin-code-from-an-address-string/) - Extract City, State and Pin code from an address string - [Compute year on year growth in a Pivot Table](https://www.ashishmathur.com/compute-year-on-year-growth-in-a-pivot-table/) - Compute year on year growth in a Pivot Table - [Blanks appearing in source data not to appear in Data validation list](https://www.ashishmathur.com/blanks-appearing-in-source-data-not-to-appear-in-data-validation-list/) - Blanks appearing in source data not to appear in Data validation list - [Speeding up a lookup task on a large database](https://www.ashishmathur.com/speeding-up-a-lookup-task-on-a-large-database/) - Speeding up a lookup task on a large database - [Compute month wise pending audits](https://www.ashishmathur.com/compute-month-wise-pending-audits/) - Compute month wise pending audits - [Append data from two worksheets with different structures](https://www.ashishmathur.com/append-data-from-two-worksheets-with-different-structures/) - Append data from two worksheets with different structures - [Append data from alternate columns of the same table](https://www.ashishmathur.com/append-data-from-alternate-columns-of-the-same-table/) - Append data from alternate columns of the same table - [Compute attrition rate from two different data sources](https://www.ashishmathur.com/compute-attrition-rate-from-two-different-data-sources/) - Compute attrition rate from two different data sources - [Prioritise investment liquidation to minimise Capital Gains](https://www.ashishmathur.com/prioritise-investment-liquidation-to-minimise-capital-gains/) - Prioritise investment liquidation to minimise Capital Gains - [Create a Pivot Table from multiple worksheets in different workbooks](https://www.ashishmathur.com/create-a-pivot-table-from-multiple-worksheets-in-different-workbooks/) - Pivot from multiple workbooks - [Create a Pivot Table from multiple worksheets in the same workbook](https://www.ashishmathur.com/create-a-pivot-table-from-multiple-worksheets-in-the-same-workbook/) - Create pivot table from multiple worksheets - [Customise Pivot Table reports](https://www.ashishmathur.com/customise-pivot-table-reports/) - Customise Pivot Table reports - [Count entries in a range which exclude certain user defined words](https://www.ashishmathur.com/count-entries-in-a-range-which-exclude-certain-user-defined-words/) - Count of exclusions - [Compute configuration count using Set Theory and Venn Diagrams](https://www.ashishmathur.com/compute-configuration-count-using-set-theory-and-venn-diagrams/) - Compute configuration count using Set Theory and Venn Diagrams - [Consider a Pivot Table Value field column as a criteria for computing another Value Field column](https://www.ashishmathur.com/consider-a-pivot-table-value-field-column-as-a-criteria-for-computing-another-value-field-column/) - Consider a Pivot Table Value field column as a criteria for computing another Value Field column - [Data slicing and analysis with the Power Pivot](https://www.ashishmathur.com/data-slicing-and-analysis-with-the-power-pivot/) - Data slicing and analysis with Power Pivot - [Display data from the Grand Total column of a Pivot Table on a Stacked Pivot Chart](https://www.ashishmathur.com/display-data-from-the-grand-total-column-of-a-pivot-table-on-a-stacked-pivot-chart/) - Display data from the Grand Total column of a Pivot Table on a Stacked Pivot Chart - [Show Slicer selection on a Graph](https://www.ashishmathur.com/show-slicer-selection-on-a-graph/) - Show Slicer selection on a Graph - [Dynamically transpose data after ignoring blank cells](https://www.ashishmathur.com/dynamically-transpose-data-after-ignoring-blank-cells/) - Dynamically transpose data after ignoring blank cells - [Align data from two columns](https://www.ashishmathur.com/align-data-from-two-columns/) - Align data from two columns - [Ignore errors while adding non contiguous cells of a range](https://www.ashishmathur.com/ignore-errors-while-adding-non-contiguous-cells-of-a-range/) - Ignore errors while adding non contiguous cells of a range - [Converting a tabular data layout to a matrix layout](https://www.ashishmathur.com/converting-a-tabular-data-layout-to-a-matrix-layout/) - Converting a tabular data layout to a matrix layout - [Converting a matrix data layout to a tabular layout](https://www.ashishmathur.com/converting-a-matrix-data-layout-to-a-tabular-layout/) - Convert a matrix like data layout to a tabular data format to ultimately create a pivot table. - [Dynamically extract unique values with no conditions](https://www.ashishmathur.com/dynamically-extract-unique-values-with-no-conditions/) - Dynamically extract unique values with no conditions - [LOOKUP where search string appears multiple times](https://www.ashishmathur.com/lookup-where-search-string-appears-multiple-times/) - Look up such that if a lookup variable appears multiple times in a lookup array, all values appearing against the look up variable appear in different cells - [Merge and append data from two worksheets](https://www.ashishmathur.com/merge-and-append-data-from-two-worksheets/) - Merge and append data from two worksheets - [Recompute figures in the Value area section of a Pivot Table after receiving a user input](https://www.ashishmathur.com/recompute-figures-in-the-value-area-section-of-a-pivot-table-after-receiving-a-user-input/) - Recompute figures in the Value area section of a Pivot Table after receiving a user input - [Extract numeric data and dates from string](https://www.ashishmathur.com/extract-numeric-data-and-dates-from-string/) - Extract numeric data and dates from string - [Extract e-mail address from string](https://www.ashishmathur.com/extract-e-mail-address-from-string/) - Extract e-mail address from string - [Drop additional fields in the Row area of a Pivot Table without affecting the already computed "conditional maximum" in the Value Area section](https://www.ashishmathur.com/drop-additional-fields-in-the-row-area-of-a-pivot-table-without-affecting-the-already-computed-conditional-maximum-in-the-value-area-section/) - Drop additional fields in the Row area of a Pivot Table without affecting the already computed "conditional maximum" in the Value Area section - [Compute year wise weighted average on a large dataset](https://www.ashishmathur.com/compute-year-wise-weighted-average-on-a-large-dataset/) - Compute year wise weighted average on a large dataset - [Generate a list of all Excel files from a specific folder without using VBA](https://www.ashishmathur.com/generate-a-list-of-all-excel-files-from-a-specific-folder-without-using-vba/) - Create a list of all MS Excel files lying in a specific folder without using VBA. - [Remove abbreviations appearing before a name](https://www.ashishmathur.com/remove-abbreviations-appearing-before-a-name/) - Remove abbreviations appearing before a name - [Compute "running total in" across years in a Pivot Table](https://www.ashishmathur.com/compute-running-total-in-across-years-in-a-pivot-table/) - Compute "running total in" across years in a Pivot Table - [Create a Pivot Table from multiple individual ranges without using ancillary columns](https://www.ashishmathur.com/create-a-pivot-table-from-multiple-individual-ranges-without-using-ancillary-columns/) - Create a Pivot Table from multiple individual ranges without using ancillary columns - [Derive end date and time from start date and time, office working hours and lunch breaks](https://www.ashishmathur.com/derive-end-date-and-time-from-start-date-and-time-office-working-hours-and-lunch-breaks/) - Derive end date and time from start date and time, office working hours and lunch breaks - [Perform a lookup with inexact text strings and/or spelling mistakes](https://www.ashishmathur.com/perform-a-lookup-with-inexact-text-strings-andor-spelling-mistakes/) - Perform a lookup with inexact text strings and/or spelling mistakes - [Filter on a column of Date and time values](https://www.ashishmathur.com/filter-on-a-column-of-date-and-time-values/) - Filter on a column of Date and time values - [Extract farthest/latest date based on multiple conditions](https://www.ashishmathur.com/extract-farthestlatest-date-based-on-multiple-conditions/) - Given a multi column databse, extract farthest/latest date based on multiple conditions - [Sequencing using advanced filters](https://www.ashishmathur.com/sequencing-using-advanced-filters/) - Sequencing using advanced filters - [Dynamically extract unique values with multiple conditions](https://www.ashishmathur.com/dynamically-extract-unique-values-with-multiple-conditions/) - Dynamically extract unique values with multiple conditions - [Analysing customer walkin data by date and service taken](https://www.ashishmathur.com/analysing-customer-walkin-data-by-date-and-service-taken/) - Determine total and unique number of customers who availed of services on different days - [Compute MODE of all numbers split across multiple worksheets](https://www.ashishmathur.com/compute-mode-of-all-numbers-split-across-multiple-worksheets/) - Compute MODE of all numbers split across multiple worksheets - [Creating Exception Reports](https://www.ashishmathur.com/creating-exception-reports/) - Creating Exception Reports - [Perform a Variance Analysis within a Pivot Table](https://www.ashishmathur.com/perform-a-variance-analysis-within-a-pivot-table/) - Perform a Variance Analysis within a Pivot Table - [Count unique values with conditions](https://www.ashishmathur.com/count-uniques-with-conditions/) - Given multiple conditions, count unique values from one column. - [Sum highest n numbers based on conditions](https://www.ashishmathur.com/sum-highest-n-numbers-based-on-conditions/) - On a given range of numbers, sum highest n numbers based on conditions - [Extract n th. most frequently occurring item from a database](https://www.ashishmathur.com/extract-n-th-most-frequently-occurring-item-from-a-database/) - From a databse of numebrs or text values, extract the nth most frequently occuring item - [Summarise data from different cells of multiple worksheets](https://www.ashishmathur.com/summarise-data-from-different-cells-of-multiple-worksheets/) - Summarise data from different cells of multiple worksheets - [Secondary validation cell entry to update when primary validation cell changes](https://www.ashishmathur.com/secondary-validation-cell-entry-to-update-when-primary-validation-cell-changes/) - Secondary validation cell entry to update when primary validation cell changes - [Sum visible cells of a filtered range ignoring errors](https://www.ashishmathur.com/sum-visible-cells-of-a-filtered-range-ignoring-errors/) - In a filtered range, ignore error values when summing values - [Conditional testing without lengthy nested IF functions](https://www.ashishmathur.com/conditional-testing-without-lengthy-nested-if-functions/) - Conditional testing without lengthy nested IF functions - [Computing growth % inside a pivot table](https://www.ashishmathur.com/computing-growth-inside-a-pivot-table/) - Writing calculated items formulas in a pivot table to compute growth give incorrect % figures in the total column - [Compute Day Sales Outstanding (DSO)](https://www.ashishmathur.com/compute-day-sales-outstanding-dso/) - Given a debtor balance and revenue per month, compute the day sales outstanding - [Goal seek multiple cells](https://www.ashishmathur.com/goal-seek-multiple-cells/) - [Compute rent payable over the contact period after factoring in escalations](https://www.ashishmathur.com/compute-rent-payable-over-the-contact-period-after-factoring-in-escalations/) - Compute capitalised value of rent payable over the contract term after factoring in escalations - [Valuing Closing Stock using FIFO method of Accounting](https://www.ashishmathur.com/valuing-closing-stock-using-fifo-method-of-accounting/) - Stock valuation on FIFO basis - [Extract text from a custom formatted cell](https://www.ashishmathur.com/extract-text-from-a-custom-formatted-cell/) - Extract custom formatting of numbers into another column - [Compute Pro rata growth rate within a Pivot Table](https://www.ashishmathur.com/compute-pro-rata-growth-rate-within-a-pivot-table/) - Compute Pro rata growth rate within a Pivot Table - [Calculate a unique count with conditions in a Pivot Table](https://www.ashishmathur.com/calculate-a-unique-count-with-conditions-in-a-pivot-table/) - Calculate a unique count with conditions in a Pivot Table - [Show granular as well as total figures on the Summary sheet](https://www.ashishmathur.com/show-granular-as-well-as-total-figures-on-the-summary-sheet/) - Show granular as well as total figures on the Summary sheet - [Force Excel tables to work across workbooks](https://www.ashishmathur.com/force-excel-tables-to-work-across-workbooks/) - Remove named ranges from Excel tables so that they can be used when the source workbook is closed. - [Return closest numeric match](https://www.ashishmathur.com/return-closest-numeric-match/) - Given a range of numbers, return the closes numeric match - [Extract data from unknown lookup range](https://www.ashishmathur.com/extract-data-from-unknown-lookup-range/) - [VLOOKUP() function to work only on visible cells of filtered range](https://www.ashishmathur.com/vlookup-function-to-work-only-on-visible-cells-of-filtered-range/) - VLOOKUP() function to work only on visible cells of filtered range - [Return an exact value via the LOOKUP() function](https://www.ashishmathur.com/return-an-exact-value-via-the-lookup-function/) - The LOOKUP() function searches for the largest value less than equal to the lookup value. However, it can also be made to perform an exact match. - [LOOKUP unique data from multiple columns where search string appears multiple times](https://www.ashishmathur.com/lookup-unique-data-from-multiple-columns-where-search-string-appears-multiple-times/) - LOOKUP unique data from multiple columns where search string appears multiple times - [Create a master list of unique account codes from two data sources](https://www.ashishmathur.com/create-a-master-list-of-unique-account-codes-from-two-data-sources/) - Create a master list of unique account codes from two data sources - [Ensure that "Show Value as" feature of the Pivot Table works even when some Pivot Table columns are unfiltered/hidden](https://www.ashishmathur.com/ensure-that-show-value-as-feature-of-the-pivot-table-works-even-when-some-pivot-table-columns-are-unfilteredhidden/) - Ensure that "Show Value as" feature of the Pivot Table works even when some Pivot Table columns are unfiltered/hidden - [Create all possible combinations from different ranges without using VBA](https://www.ashishmathur.com/create-all-possible-combinations-from-different-ranges-without-using-vba/) - Create all possible combinations from different ranges without using VBA - [Extract data from multiple cells of closed Excel files](https://www.ashishmathur.com/extract-data-from-multiple-cells-of-closed-excel-files/) - Given some MS Excel files stored in a folder, one may want to lookup data from specific cells of these closed Excel files. - [The DATEDIF() bug](https://www.ashishmathur.com/the-datedif-bug/) - DATEDIF - [Consolidate data from multiple closed Excel files with different number of columns](https://www.ashishmathur.com/consolidate-data-from-multiple-closed-excel-files-with-different-number-of-columns/) - Given different number of headings on multiple excel files, one may want to consolidate data from these closed Excel files. - [Perform an iterative sum of Top n values across multiple columns](https://www.ashishmathur.com/perform-an-iterative-sum-of-top-n-values-across-multiple-columns/) - Perform an iterative sum of Top n values across multiple columns - [Select a large data set which has blank rows and columns without using the Shift and arrow keys](https://www.ashishmathur.com/select-a-large-data-set-which-has-blank-rows-and-columns-without-using-the-shift-and-arrow-keys/) - Select a large data set which has blank rows and columns without using the Shift and arrow keys - [Computing penalties by Employee, Group and Labour type using a PowerPivot](https://www.ashishmathur.com/computing-penalties-by-employee-group-and-labour-type-using-a-powerpivot/) - Computing penalties by Employee, Group and Labour type using a PowerPivot - [Determine cumulative expenses per employee when per diem rates vary by block of dates](https://www.ashishmathur.com/determine-cumulative-expenses-per-employee-when-per-diem-rates-vary-by-block-of-dates/) - Determine cumulative expenses per employee when per diem rates vary by block of dates - [Summarise data from multiple sheets with multiple conditions - Part II](https://www.ashishmathur.com/summarise-data-from-multiple-sheets-with-multiple-conditions-part-ii/) - Sum across worksheets with conditions - [Count unique values with conditions on large databases](https://www.ashishmathur.com/count-unique-values-with-conditions-on-large-databases/) - Count unique values with conditions on large databases - [Sum data from a particular cell of last n sheets only](https://www.ashishmathur.com/sum-data-from-a-particular-cell-of-last-n-sheets-only/) - Sum data from a particular cell of last n sheets only - [Count text values in a range](https://www.ashishmathur.com/count-text-values-in-a-range/) - Count text values in a range with numbers, text, formulas returning blanks, errors and empty cells - [Generate a list of all tabs names without using VBA](https://www.ashishmathur.com/generate-a-list-of-all-tabs-names-without-using-vba/) - Generate a list of all tab names in a workbook without using VBA/macros - [Sum a range of alphanumeric entries ignoring errors](https://www.ashishmathur.com/sum-a-range-of-alphanumeric-entries-ignoring-errors/) - Sum a range ignoring error values. - [Validation source list to resize automatically](https://www.ashishmathur.com/validation-source-list-to-resize-automatically/) - [Validate based on multiple conditions](https://www.ashishmathur.com/validate-based-on-multiple-conditions/) - [Split name into three columns](https://www.ashishmathur.com/split-name-into-three-columns/) - Split name into First name, Middle name and Surname - [Summarise data based on an unknown combination of conditions](https://www.ashishmathur.com/summarise-data-based-on-an-unknown-combination-of-conditions/) - Summarise data based on an unknown combination of conditions - [Applying conditional formatting to column charts](https://www.ashishmathur.com/applying-conditional-formatting-to-column-charts/) - Applying conditional formatting to column charts - [Dynamically extract unique values from a filtered range](https://www.ashishmathur.com/dynamically-extract-unique-values-from-a-filtered-range/) - Dynamically extract unique values from a filtered range - [Create employee wise Effort Utilisation Report](https://www.ashishmathur.com/create-employee-wise-effort-utilisation-report/) - Create employee wise Effort Utilisation Report - [Display auto filter criteria in a cell](https://www.ashishmathur.com/display-auto-filter-criteria-in-a-cell/) - [Determine weekday after factoring in user specified holidays](https://www.ashishmathur.com/determine-weekday-after-factoring-in-user-specified-holidays/) - Determine workday with Sunday as working - [Filtering a database by both rows and columns](https://www.ashishmathur.com/ffiltering-a-database-by-both-rows-and-columns/) - Filtering a database by both rows and columns - [Compare two sheets and prepare a Discrepancy Report](https://www.ashishmathur.com/compare-two-sheets-and-prepare-a-discrepancy-report/) - Compare two sheets and prepare a Discrepancy Report - [Flip minus sign from right to left in a multi column range](https://www.ashishmathur.com/flip-minus-sign-from-right-to-left-in-a-multi-column-range/) - Flip minus sign in multiple columns - [Calculate turn around time excluding Sundays and public holidays](https://www.ashishmathur.com/calculate-turn-around-time-excluding-sundays-and-public-holidays/) - Given start date/time and end date/time, calculate turnaround time excluding Sundays and public holidays. - [Extract specific number of characters from an alphanumeric string without breaking any word](https://www.ashishmathur.com/extract-specific-number-of-characters-from-an-alphanumeric-string-without-breaking-any-word/) - Extract characters from a string without breaking any word - [Compare value of one cell with value of next visible cell of a filtered range](https://www.ashishmathur.com/compare-value-of-one-cell-with-value-of-next-visible-cell-of-a-filtered-range/) - Compare value of one cell with value of next visible cell of a filtered range - [Determine stock transfer from multiple locations](https://www.ashishmathur.com/determine-stock-transfer-from-multiple-locations/) - [Count blanks cells in a filtered range](https://www.ashishmathur.com/count-blanks-cells-in-a-filtered-range/) - Count blank cells in a filtered range - [Count dates in a range which are within 365 days of each other](https://www.ashishmathur.com/count-dates-in-a-range-which-are-with-365-days-of-each-other/) - Count days which are 365 days of one another - [Compute monthly asset amortisation expense](https://www.ashishmathur.com/compute-monthly-asset-amortisation-expense/) - [Count word combinations in individual columns of a multi column database](https://www.ashishmathur.com/count-word-combinations-in-individual-columns-of-a-multi-column-database/) - In a multi column database, count how many times the word combinations occur in individual columns - [Consolidate data from a specific worksheet of multiple workbooks to multiple worksheets of one workbook](https://www.ashishmathur.com/consolidate-data-from-a-specific-worksheet-of-multiple-workbooks-to-multiple-worksheets-of-one-workbook/) - Consolidate data from multiple Excel workbooks to one Excel workbook - [Sum up diagonal cells in a range](https://www.ashishmathur.com/sum-up-diagonal-cells-in-a-range/) - Sum diagonal cells - [Summarise data from multiple worksheets](https://www.ashishmathur.com/summarise-data-from-multiple-worksheets/) - Summarise data from multiple worksheets - [Data labels on floating bar charts](https://www.ashishmathur.com/data-labels-on-floating-bar-charts/) - Data labels on floating bar charts - [Reduce scrolling in data validation](https://www.ashishmathur.com/reduce-scrolling-in-data-validation/) - Reduce scrolling in data validation - [Extract information based on background colour](https://www.ashishmathur.com/extract-information-based-on-background-colour/) - [Transfer specific columns to another workbook based on conditions](https://www.ashishmathur.com/transfer-specific-columns-to-another-workbook-based-on-conditions/) - [Extract data based on customer specific dimensions](https://www.ashishmathur.com/extract-data-based-on-customer-specific-dimensions/) - [Extract a report showing parameters beyond tolerance limits](https://www.ashishmathur.com/extract-a-report-showing-parameters-beyond-tolerance-limits/) - [Generate a time table based on periodicity of tasks](https://www.ashishmathur.com/generate-a-time-table-based-on-periodicity-of-tasks/) - [Control graphs with check boxes and scroll bars](https://www.ashishmathur.com/control-graphs-with-check-boxes-and-scroll-bars/) - [Automatically change validated entries when source of validation list changes](https://www.ashishmathur.com/automatically-change-validated-entries-when-source-of-validation-list-changes/) - [Lookup for a cell value in multiple sheets](https://www.ashishmathur.com/lookup-for-a-cell-value-in-multiple-sheets/) - [Summarise data from multiple sheets with one condition](https://www.ashishmathur.com/summarise-data-from-multiple-sheets-with-one-condition/) - [Summarise data from multiple sheets with multiple conditions](https://www.ashishmathur.com/summarise-data-from-multiple-sheets-with-multiple-conditions/) - [Updating charts for columns added to source data in Excel 2003](https://www.ashishmathur.com/updating-charts-for-columns-added-to-source-data-in-excel-2003/) - [Create a log sheet of all array formulas in a workbook](https://www.ashishmathur.com/create-a-log-sheet-of-all-array-formulas-in-a-workbook/) - [Assign different colours to different duplicate values in a range](https://www.ashishmathur.com/assign-different-colours-to-different-duplicate-values-in-a-range/) - [SUMPRODUCT function to work on a range with interspersed text values](https://www.ashishmathur.com/sumproduct-function-to-work-on-a-range-with-interspersed-text-values/) - [Create a column-column graph on two axis](https://www.ashishmathur.com/create-a-column-column-graph-on-two-axis/) - [List down most frequently appearing names in descending order of frequency](https://www.ashishmathur.com/list-down-most-frequently-appearing-names-in-descending-order-of-frequency/) - [Extract uncoloured cells per article to another worksheet](https://www.ashishmathur.com/extract-uncoloured-cells-per-article-to-another-worksheet/) - [Compute revenue with progressive discounting](https://www.ashishmathur.com/compute-revenue-with-progressive-discounting/) - [Programmatically transfer data from master sheet to sub sheets with conditions](https://www.ashishmathur.com/programmatically-transfer-data-from-master-sheet-to-sub-sheets-with-con/) - [Summarise data from multiple sheets with one condition - PartII](https://www.ashishmathur.com/summarise-data-from-multiple-sheets-with-one-condition-partii/) - Sumamrise data from multiple sheets or tabs with the same structure - [Create charts on different sheets by clicking a button](https://www.ashishmathur.com/create-charts-on-different-sheets-by-clicking-a-button/) - Create charts on different sheets by clicking a button. - [Apportion a number over empty cells](https://www.ashishmathur.com/apportion-a-number-over-empty-cells/) - Apportion electricity bills over months for which bills have not been received. - [Removing dependent validation list from cell for one case](https://www.ashishmathur.com/removing-dependent-validation-list-from-cell-for-one-case/) - Create dependent validation only for a few primary validation entries. For the others, allow typing in the secondary validation cell. - [Transfer data from one Excel file to multiple Excel files](https://www.ashishmathur.com/transfer-data-from-one-excel-file-to-multiple-excel-files/) - Create different workbooks for as many unique entries as there are in a column of another workbook - [Split data from a master document into various worksheets based on a template sheet](https://www.ashishmathur.com/split-data-from-a-master-document-into-various-worksheets-based-on-a-template-sheet/) - Given vendor information on one sheet and a vendor reconciliation template sheet, create individual vendor sheets from this template sheet. - [Conditional data validation](https://www.ashishmathur.com/conditional-data-validation/) - Validation in once cell to determine what a person enters in another cell. - [Validate cell to accept current time](https://www.ashishmathur.com/validate-cell-to-accept-current-time/) - Validate cell to accept current time - [Numbers in Indian currency format](https://www.ashishmathur.com/numbers-in-indian-currency-format/) - Display numbers in Indian Currency format - [Convert multiple columns of numbers into text at once](https://www.ashishmathur.com/convert-multiple-columns-of-numbers-into-text-at-once/) - Convert multiple columns of numbers into text without using a macro or spare columns. - [Minimise the total number of inverters to be used for different electricity load factors](https://www.ashishmathur.com/minimise-the-total-number-of-inverters-to-be-used-for-different-electricity-load-factors/) - Given two inverter capacities, the objective is to minimise the number of inverters to be used for different electricity load factors - [Shade alternate band of rows in a filtered range](https://www.ashishmathur.com/shade-alternate-band-of-rows-in-a-filtered-range/) - In a filtered range, colour rows based on change in values. - [Determine the maximum number of consecutive 1's appearing in a range](https://www.ashishmathur.com/determine-the-maximum-number-of-consecutive-1s-appearing-in-a-range/) - Count the maximum number of consecutive entries appearing in a range. - [Split multiple lines of data in one cell to multiple columns](https://www.ashishmathur.com/split-multiple-lines-of-data-in-one-cell-to-multiple-columns/) ## Pages - [Home](https://www.ashishmathur.com/) - [Corporate Interventions](https://www.ashishmathur.com/corporate-interventions/) - This brochure details out Ashish's profile, two day agenda of the MS Excel session and methodology - [A "Business Intelligence (BI)" session](https://www.ashishmathur.com/corporate-interventions/bi/) - [Contact Me](https://www.ashishmathur.com/contact-me/) ## Testimonials - [Santosh Kumar Peshkar](https://www.ashishmathur.com/testimonial/santosh-kumar-peshkar/) - [CA.Amandeep Singh Sabharwal](https://www.ashishmathur.com/testimonial/ca-amandeep-singh-sabharwal/) - [Vishal Goel](https://www.ashishmathur.com/testimonial/vishal-goel/) - [Gunjan Gupta, ACA](https://www.ashishmathur.com/testimonial/gunjan-gupta-aca/) - [Shruti Gupta](https://www.ashishmathur.com/testimonial/shruti-gupta/) ## Categories - [DATA EXTRACTION](https://www.ashishmathur.com/category/data-extraction/) - [DUPLICATES AND UNIQUES](https://www.ashishmathur.com/category/duplicates-and-uniques/) - [DATA SUMMARISING](https://www.ashishmathur.com/category/summarise-data/) - [DATA FORMATTING](https://www.ashishmathur.com/category/display/) - [DATE AND TIME](https://www.ashishmathur.com/category/date-and-time/) - [DATA VALIDATION](https://www.ashishmathur.com/category/data-validation/) - [GRAPHS AND CHARTS](https://www.ashishmathur.com/category/graphs-and-charts/) - [FILTERS](https://www.ashishmathur.com/category/filters/) - [PIVOT TABLES](https://www.ashishmathur.com/category/pivot-tables/) - [DATA CONSOLIDATION](https://www.ashishmathur.com/category/data-consolidation/) - [CALCULATION](https://www.ashishmathur.com/category/calculation/) - [DATA CONVERSION](https://www.ashishmathur.com/category/data-conversion/) - [POWERPIVOT](https://www.ashishmathur.com/category/powerpivot/) - [RANGE SELECTION](https://www.ashishmathur.com/category/range-selection/) - [POWER QUERY](https://www.ashishmathur.com/category/power-query/) - [DATA STRUCTURING](https://www.ashishmathur.com/category/data-structuring/) - [DATA APPEND](https://www.ashishmathur.com/category/data-append/) - [POWER QUERY + POWERPIVOT](https://www.ashishmathur.com/category/power-query-powerpivot/) - [POWER BI](https://www.ashishmathur.com/category/power-bi/) - [POWER VIEW](https://www.ashishmathur.com/category/power-view/) - [PowerBI desktop](https://www.ashishmathur.com/category/powerbi-desktop/) - [CUBE FORMULAS](https://www.ashishmathur.com/category/cube-formulas/) ## Tags - [SEARCH](https://www.ashishmathur.com/tag/search/) - [LOOKUP](https://www.ashishmathur.com/tag/lookup/) - [INDEX](https://www.ashishmathur.com/tag/index/) - [MATCH](https://www.ashishmathur.com/tag/match/) - [SUMPRODUCT](https://www.ashishmathur.com/tag/sumproduct/) - [VLOOKUP](https://www.ashishmathur.com/tag/vlookup/) - [ARRAY FORMULA](https://www.ashishmathur.com/tag/array-formula/) - [ISERROR](https://www.ashishmathur.com/tag/iserror/) - [SMALL](https://www.ashishmathur.com/tag/small/) - [MID](https://www.ashishmathur.com/tag/mid/) - [CELL](https://www.ashishmathur.com/tag/cell/) - [FIND](https://www.ashishmathur.com/tag/find/) - [COUNTIF](https://www.ashishmathur.com/tag/countif/) - [IF](https://www.ashishmathur.com/tag/if/) - [AND](https://www.ashishmathur.com/tag/and/) - [MIN](https://www.ashishmathur.com/tag/min/) - [MAX](https://www.ashishmathur.com/tag/max/) - [ISNUMBER](https://www.ashishmathur.com/tag/isnumber/) - [LARGE](https://www.ashishmathur.com/tag/large/) - [FORMATTING](https://www.ashishmathur.com/tag/formatting/) - [ABS](https://www.ashishmathur.com/tag/abs/) - [NOW](https://www.ashishmathur.com/tag/now/) - [INT](https://www.ashishmathur.com/tag/int/) - [TRIM](https://www.ashishmathur.com/tag/trim/) - [SUBSTITUTE](https://www.ashishmathur.com/tag/substitute/) - [RIGHT](https://www.ashishmathur.com/tag/right/) - [LEFT](https://www.ashishmathur.com/tag/left/) - [LEN](https://www.ashishmathur.com/tag/len/) - [REPT](https://www.ashishmathur.com/tag/rept/) - [ROW](https://www.ashishmathur.com/tag/row/) - [INDIRECT](https://www.ashishmathur.com/tag/indirect/) - [ADDRESS](https://www.ashishmathur.com/tag/address/) - [NAMED RANGES](https://www.ashishmathur.com/tag/named-ranges/) - [SUM](https://www.ashishmathur.com/tag/sum/) - [CODE](https://www.ashishmathur.com/tag/code/) - [CHAR](https://www.ashishmathur.com/tag/char/) - [CHOOSE](https://www.ashishmathur.com/tag/choose/) - [COUNTA](https://www.ashishmathur.com/tag/counta/) - [COLUMNS](https://www.ashishmathur.com/tag/columns/) - [FREQUENCY](https://www.ashishmathur.com/tag/frequency/) - [ADVANCED FILTERS](https://www.ashishmathur.com/tag/advanced-filters/) - [TEXT](https://www.ashishmathur.com/tag/text/) - [WEEKDAY](https://www.ashishmathur.com/tag/weekday/) - [GOAL SEEK](https://www.ashishmathur.com/tag/goal-seek/) - [MACRO](https://www.ashishmathur.com/tag/macro/) - [SQL QUERY](https://www.ashishmathur.com/tag/sql-query/) - [SUBTOTAL](https://www.ashishmathur.com/tag/subtotal/) - [OFFSET](https://www.ashishmathur.com/tag/offset/) - [COLUMN](https://www.ashishmathur.com/tag/column/) - [MCONCAT](https://www.ashishmathur.com/tag/mconcat/) - [ISTEXT](https://www.ashishmathur.com/tag/istext/) - [MOREFUNC](https://www.ashishmathur.com/tag/morefunc/) - [EVALUATE](https://www.ashishmathur.com/tag/evaluate/) - [UNIQUEVALUES](https://www.ashishmathur.com/tag/uniquevalues/) - [TABLE](https://www.ashishmathur.com/tag/table/) - [OR](https://www.ashishmathur.com/tag/or/) - [CHECK BOX](https://www.ashishmathur.com/tag/check-box/) - [CONDITIONAL FORMATTING](https://www.ashishmathur.com/tag/conditional-formatting/) - [SUMIF](https://www.ashishmathur.com/tag/sumif/) - [SERIES](https://www.ashishmathur.com/tag/series/) - [COUNTBLANK](https://www.ashishmathur.com/tag/countblank/) - [IFERROR](https://www.ashishmathur.com/tag/iferror/) - [NOT](https://www.ashishmathur.com/tag/not/) - [ISBLANK](https://www.ashishmathur.com/tag/isblank/) - [INDRECT](https://www.ashishmathur.com/tag/indrect/) - [ROWS](https://www.ashishmathur.com/tag/rows/) - [TEXT TO COLUMNS](https://www.ashishmathur.com/tag/text-to-columns/) - [AGGREGATE](https://www.ashishmathur.com/tag/aggregate/) - [REGEX.SUBSTITUTE](https://www.ashishmathur.com/tag/regex-substitute/) - [XLM 4.0 MACRO](https://www.ashishmathur.com/tag/xlm-4-0-macro/) - [MOD](https://www.ashishmathur.com/tag/mod/) - [ISODD](https://www.ashishmathur.com/tag/isodd/) - [COUNT](https://www.ashishmathur.com/tag/count/) - [INDIRECT.EXT](https://www.ashishmathur.com/tag/indirect-ext/) - [CONCATENATE](https://www.ashishmathur.com/tag/concatenate/) - [MULTIPLE CONSOLIDATION RANGES](https://www.ashishmathur.com/tag/multiple-consolidation-ranges/) - [ALT+D+P](https://www.ashishmathur.com/tag/altdp/) - [CALCULATED ITEM FORMULA](https://www.ashishmathur.com/tag/calculated-item-formula/) - [SOLVE ORDER](https://www.ashishmathur.com/tag/solve-order/) - [WILDCARDS](https://www.ashishmathur.com/tag/wildcards/) - [QUOTIENT](https://www.ashishmathur.com/tag/quotient/) - [MMULT](https://www.ashishmathur.com/tag/mmult/) - [FIND AND REPLACE](https://www.ashishmathur.com/tag/find-and-replace/) - [PASTE SPECIAL](https://www.ashishmathur.com/tag/paste-special/) - [MS QUERY](https://www.ashishmathur.com/tag/ms-query/) - [FILTER](https://www.ashishmathur.com/tag/filter/) - [GET.CELL](https://www.ashishmathur.com/tag/get-cell/) - [LIST BOX](https://www.ashishmathur.com/tag/list-box/) - [TRANSPOSE](https://www.ashishmathur.com/tag/transpose/) - [DCOUNTA](https://www.ashishmathur.com/tag/dcounta/) - [DATA TABLE](https://www.ashishmathur.com/tag/data-table/) - [WORKDAY](https://www.ashishmathur.com/tag/workday/) - [TIME](https://www.ashishmathur.com/tag/time/) - [ROUND](https://www.ashishmathur.com/tag/round/) - [ROUNDUP](https://www.ashishmathur.com/tag/roundup/) - [NETWORKDAYS](https://www.ashishmathur.com/tag/networkdays/) - [CALCULATED FIELD FORMULA](https://www.ashishmathur.com/tag/calculated-field-formula/) - [CTRL+G](https://www.ashishmathur.com/tag/ctrlg/) - [GET.WORKBOOK](https://www.ashishmathur.com/tag/get-workbook/) - [DSUM](https://www.ashishmathur.com/tag/dsum/) - [DISTINCTCOUNT](https://www.ashishmathur.com/tag/distinctcount/) - [COUNTROWS](https://www.ashishmathur.com/tag/countrows/) - [CALCULATETABLE](https://www.ashishmathur.com/tag/calculatetable/) - [GENERATE](https://www.ashishmathur.com/tag/generate/) - [SUMMARIZE](https://www.ashishmathur.com/tag/summarize/) - [COUNTAX](https://www.ashishmathur.com/tag/countax/) - [SUMX](https://www.ashishmathur.com/tag/sumx/) - [VALUE](https://www.ashishmathur.com/tag/value/) - [CALCULATE](https://www.ashishmathur.com/tag/calculate/) - [N](https://www.ashishmathur.com/tag/n/) - [RANK](https://www.ashishmathur.com/tag/rank/) - [RELATED](https://www.ashishmathur.com/tag/related/) - [TOPN](https://www.ashishmathur.com/tag/topn/) - [VALUES](https://www.ashishmathur.com/tag/values/) - [ALL](https://www.ashishmathur.com/tag/all/) - [DATEDIF](https://www.ashishmathur.com/tag/datedif/) - [EDATE](https://www.ashishmathur.com/tag/edate/) - [SHOW DATA AS](https://www.ashishmathur.com/tag/show-data-as/) - [SAMEPERIODLASTYEAR](https://www.ashishmathur.com/tag/sameperiodlastyear/) - [BLANK](https://www.ashishmathur.com/tag/blank/) - [HASONEVALUE](https://www.ashishmathur.com/tag/hasonevalue/) - [RANKX](https://www.ashishmathur.com/tag/rankx/) - [EARLIER](https://www.ashishmathur.com/tag/earlier/) - [RAND](https://www.ashishmathur.com/tag/rand/) - [ALLEXCEPT](https://www.ashishmathur.com/tag/allexcept/) - [MINX](https://www.ashishmathur.com/tag/minx/) - [ENDOFYEAR](https://www.ashishmathur.com/tag/endofyear/) - [LASTDATE](https://www.ashishmathur.com/tag/lastdate/) - [RUNNING TOTAL IN](https://www.ashishmathur.com/tag/running-total-in/) - [DATESBETWEEN](https://www.ashishmathur.com/tag/datesbetween/) - [FIRSTDATE](https://www.ashishmathur.com/tag/firstdate/) - [YEAR](https://www.ashishmathur.com/tag/year/) - [MONTH](https://www.ashishmathur.com/tag/month/) - [FORMAT](https://www.ashishmathur.com/tag/format/) - [ADDCOLUMNS](https://www.ashishmathur.com/tag/addcolumns/) - [HASONEFILTER](https://www.ashishmathur.com/tag/hasonefilter/) - [MAXX](https://www.ashishmathur.com/tag/maxx/) - [AVERAGEX](https://www.ashishmathur.com/tag/averagex/) - [AVERAGE](https://www.ashishmathur.com/tag/average/) - [CUBESET](https://www.ashishmathur.com/tag/cubeset/) - [CUBERANKEDMEMBER](https://www.ashishmathur.com/tag/cuberankedmember/) - [CUBEVALUE](https://www.ashishmathur.com/tag/cubevalue/) - [CUBEMEMBER](https://www.ashishmathur.com/tag/cubemember/) - [SUMMARISE](https://www.ashishmathur.com/tag/summarise/) - [COUNTX](https://www.ashishmathur.com/tag/countx/) - [EOMONTH](https://www.ashishmathur.com/tag/eomonth/) - [LOOKUPVALUE](https://www.ashishmathur.com/tag/lookupvalue/) - [TOTALQTD](https://www.ashishmathur.com/tag/totalqtd/) - [FIRSTNONBLANK](https://www.ashishmathur.com/tag/firstnonblank/) - [ENDOFMONTH](https://www.ashishmathur.com/tag/endofmonth/) - [ALLSELECTED](https://www.ashishmathur.com/tag/allselected/) - [PERMUTATIONA](https://www.ashishmathur.com/tag/permutationa/) - [MANAGE SETS](https://www.ashishmathur.com/tag/manage-sets/) - [USERELATIONSHIP](https://www.ashishmathur.com/tag/userelationship/) - [DISJOINTED TABLES](https://www.ashishmathur.com/tag/disjointed-tables/) - [TOTALTYD](https://www.ashishmathur.com/tag/totaltyd/) - [SUMIFS](https://www.ashishmathur.com/tag/sumifs/) - [UNION](https://www.ashishmathur.com/tag/union/) - [Cortana](https://www.ashishmathur.com/tag/cortana/) - [Natural Language queries](https://www.ashishmathur.com/tag/natural-language-queries/) - [Powerbi.com](https://www.ashishmathur.com/tag/powerbi-com/) - [Sand Dance](https://www.ashishmathur.com/tag/sand-dance/) - [TOPCOUNT.](https://www.ashishmathur.com/tag/topcount/) - [HASCONEVALUE](https://www.ashishmathur.com/tag/hasconevalue/) - [WEEKNUM](https://www.ashishmathur.com/tag/weeknum/) - [DATEDIFF](https://www.ashishmathur.com/tag/datediff/) - [CONCATENATEX](https://www.ashishmathur.com/tag/concatenatex/) - [TEXTJOIN](https://www.ashishmathur.com/tag/textjoin/) - [COUNTIFS](https://www.ashishmathur.com/tag/countifs/) - [MAXIFS](https://www.ashishmathur.com/tag/maxifs/) - [PIVOT TABLES](https://www.ashishmathur.com/tag/pivot-tables/) - [DATA CONSOLIDATION](https://www.ashishmathur.com/tag/data-consolidation/) - [HSTACK](https://www.ashishmathur.com/tag/hstack/) - [TOCOL](https://www.ashishmathur.com/tag/tocol/) - [CHOOSECOLS](https://www.ashishmathur.com/tag/choosecols/) - [SEQUENCE](https://www.ashishmathur.com/tag/sequence/) - [CHOOSEROWS](https://www.ashishmathur.com/tag/chooserows/)