Transform the Way
You Work with Excel

Excel is our Water and Oxygen!

We love Data. And there is no better tool than Excel to fulfil our passion for data crunching. There is NOTHING in Excel that an organisation would require which we cannot do.

We are a team of Excel Enthusiasts, Software Engineers, and Data Analysts. We are practitioners. The core team has a combined experience of 100+ years on Excel Training and Projects.

Excel 2016 totally transformed the way Excel works and analyses data. We call this the first, full-fledged version with Modern Excel features. Corporate delegates are still using Excel the way spreadsheets were used in the late 1990s. We founded TheModernExcel.com with the primary focus of creating awareness about these highly powerful features.

We are based out of Dubai, United Arab Emirates.

Headaches start when DIY (do it yourself) fails. We provide a painkiller called DFU (Done For You).

DFU is a "small tasks service". You get one-on-one access to an Excel Expert — like visiting your doctor. Start with a 15-minute session using Teams/Zoom. State what you want. Small tasks are done instantly, directly on your computer, on your file. No sharing of files containing sensitive data.

An indicative list of "small tasks":

1. Clean downloaded data
2. Hand-hold a new Data Model
3. Write Formulas
4. Pivot Reports
5. Charts
6. Dashboards
7. Fix errors in existing Excel solutions

Looking for Excel or Power BI talent? Or are you a skilled Excel/BI professional looking for opportunities?

We maintain a network of trained professionals and connect organisations with the right talent. Reach out to discuss employment leads and opportunities.

Let's work together.

Refer and be rewarded. We partner with business influencers. If you think you would be a good fit, we would like to converse with you.

The information contained in this site is for general guidance on matters of interest only. Given the changing nature of technology and the inherent hazards of electronic communication, there may be delays, omissions or inaccuracies in information contained in this site.

While we have made every attempt to ensure that the information contained in this site has been obtained from reliable sources, TheModernExcel.com is not responsible for any errors or omissions, or for the results obtained from the use of this information. All information is provided "as is", with no guarantee of completeness, accuracy, timeliness, or of the results obtained.

Certain links in this site connect to other websites maintained by third parties over whom TheModernExcel.com has no control. TheModernExcel.com makes no representations as to the accuracy or any other aspect of information contained in other websites.

Tables were introduced in Excel 2007 and are the backbone of the next six components of Modern Excel. Slicers, Data Model, Measures, Power Pivot, Power Query, and Power BI all work best when data is in a Table.

Why switch to Tables?

1. Pivot Tables become permanent — add new rows or columns and just refresh. No need to readjust the data range.
2. Slicers work only on Tables. Filter thousands of rows visually in seconds.
3. Formulas fill up and down automatically — no more double-clicking to copy.
4. Formatting, including Conditional Formatting, fills automatically as data grows.

A few limitations to know

1. You cannot copy or move multiple sheets if any sheet contains a Table.
2. Tables do not expand on protected sheets, even with unlocked cells below.
3. Custom Views are not allowed in workbooks with Tables.
4. Duplicate column headers are not allowed — every column must have a unique name.

Slicers were introduced in Excel 2010 for Pivot Tables and extended to Tables in Excel 2013. They provide a visual, user-friendly way to filter data without writing formulas.

Key features of Slicers

• User-friendly filtering: Click on items to filter PivotTables, PivotCharts, or Excel Tables instantly.
• Independent filters: Create multiple slicers for different fields — filter on several dimensions at once.
• Connected to multiple reports: One slicer can control filters across multiple PivotTables or Tables in the same workbook.
• Clear selections: A built-in "Clear Filter" button removes all applied filters with one click.
• Customisable: Resize, recolour, and style Slicers to match your workbook's design.

Power Query is Excel's built-in ETL (Extract, Transform, Load) engine. It connects to almost any data source and cleans, shapes, and loads data automatically — no manual copy-paste ever again.

What Power Query does

• Data import: Connect to databases, Excel files, CSVs, web pages, SharePoint, and more.
• Data transformation: Filter, sort, group, pivot, unpivot, merge, and aggregate — all via a visual interface.
• Data cleaning: Handle missing values, duplicates, errors, and inconsistent formats.
• M Language: Every transformation is recorded as M code — fully customisable for advanced users.
• Reusable queries: Define a query once; refresh with one click whenever source data changes.
• Power BI integration: Power Query is the data preparation engine inside Power BI as well.

The Data Model (available since Excel 2013) lets you import multiple tables, define relationships between them, and build powerful PivotTables and PivotCharts that span more than one table — without VLOOKUP.

Key components

• Multiple tables & relationships: Link tables on common columns, just like a database — no helper columns needed.
• Calculated columns: Add formula-driven columns directly inside the model.
• Measures (DAX): Create dynamic aggregations — sums, averages, ratios — that respond to any filter.
• Advanced PivotTables: Build cross-table reports with a single drag-and-drop.
• In-memory engine: Handles millions of rows far faster than a standard worksheet.

DAX is the formula language of the Data Model, Power Pivot, and Power BI. It lets you create Measures and Calculated Columns that perform calculations impossible with standard Excel formulas.

What makes DAX powerful

• Measures: Aggregations (SUM, AVERAGE, DISTINCTCOUNT) that update automatically based on any filter or slicer.
• Calculated columns: New columns derived from other columns, stored inside the model.
• Filter context & row context: DAX understands where it is being evaluated and adjusts calculations accordingly.
• Time intelligence: Built-in functions for Year-to-Date, Month-to-Date, Prior Year comparisons, and rolling averages.
• Variables: Assign intermediate results to variables for cleaner, faster, and more readable formulas.

Power Pivot is Excel's in-built analytical engine. It brings the Data Model and DAX directly into Excel, enabling you to analyse millions of rows with the familiar PivotTable interface.

Why use Power Pivot?

• Large datasets: Handles millions of rows using a columnar in-memory engine — no worksheet row limits.
• Data relationships: Link multiple tables without VLOOKUP or helper columns.
• DAX measures: Write custom KPIs, ratios, and time-intelligence calculations.
• Compression: Data is stored compressed, so large models use surprisingly little memory.
• Power BI bridge: Models built in Power Pivot can be imported directly into Power BI.

Power BI is Microsoft's enterprise business intelligence platform. It takes the skills you build in Excel (Power Query, Data Model, DAX) and extends them into shareable, interactive reports and dashboards.

Power BI highlights

• Rich visualisations: Charts, maps, tables, KPI cards, matrix reports, and more — all interactive.
• Same skill set: Power Query and DAX work identically in Power BI and Excel.
• Publish & share: Publish reports to the Power BI Service for organisation-wide access on any device.
• Real-time dashboards: Stream live data for operations and monitoring use cases.
• Mobile apps: Access and interact with reports on iOS and Android.
• Row-level security: Control who sees which data within the same report.

Excel Online is the browser-based version of Excel — no installation required. Access, edit, and collaborate on spreadsheets from any device via a web browser.

Key features

• Online access: Open any Excel file directly in Chrome, Edge, Firefox, or Safari.
• Real-time collaboration: Multiple users can edit the same file simultaneously — changes are visible instantly.
• AutoSave: All changes save to OneDrive or SharePoint automatically.
• Core Excel functionality: Formulas, functions, formatting, charts — all available in the browser.
• Desktop compatibility: Files are fully compatible with the desktop version of Excel.
• OneDrive integration: Share files securely with colleagues inside or outside your organisation.

These are our Ten Commandments for writing VBA Macros in Excel. They help us deliver efficient, readable, and maintainable solutions.

1. Comment. Comment. Comment. Detailed, non-technical documentation satisfies the customer's curiosity and helps when memory fails.
2. Use variables effectively. Declare variables at the start of each method with meaningful names. Round off calculations only at the final output stage.
3. Minimise hard-coding. Use variables or parameters so code is flexible and easy to maintain.
4. Utilise built-in functions. No need to reinvent the wheel.
5. Disable Screen Updating and set Calculations to Manual while VBA runs.
6. Plan the code structure before you start — clean and efficient code starts with a plan.
7. Minimise unnecessary loops — they slow down execution significantly.
8. Optimise memory usage by releasing and closing resources at the end of each procedure.
9. Avoid Select and Activate statements — they slow down your code.
10. Use Arrays for large ranges. This significantly speeds up reading and writing data.

Power Query's M Language is case-sensitive and functional in nature. Our ten commandments ensure our ETL models are readable, reusable, and performant.

1. Document every step. We add comments to complex transformations, explaining our thought process and reasoning.
2. Use meaningful variable names for intermediate calculations (e.g., InventoryAfterPlannedShipments).
3. Minimise hard-coding. Use parameters so queries adapt to changing inputs.
4. Consistent naming convention. Power Query is case-sensitive — stick to one style throughout.
5. Rename columns clearly to reflect their content. Clear names improve readability and reduce confusion.
6. Create reusable custom functions for common logic, stored in separate queries.
7. Split complex transformations into smaller steps using let-in blocks for readability.
8. Optimise performance — minimise unnecessary steps and transformations on large datasets.
9. Use error handling (try…otherwise) to avoid a single bad record breaking the whole query.
10. Test step by step — verify each transformation produces expected results before adding the next.

Our ten DAX commandments ensure our data models are fast, accurate, and easy to maintain across projects.

1. Document every measure. Comments explain the thought process behind complex formulas.
2. Always create a "sort by" column for all major dimensions.
3. Use one-to-many relationships only — 99% of our data model relationships follow this rule.
4. Build tightly constructed models that are easier to work with and use less memory.
5. Leverage the in-memory analytics engine — the Data Model uses powerful compression to reduce storage.
6. Use only native DAX functions — avoid mixing Excel formulas into the model.
7. Avoid ALL in calculated columns, nested IFs, unnecessary CALCULATE calls, EARLIER, and DISTINCT — they reduce speed.
8. Use calculated columns for static values; use measures for dynamic aggregations.
9. Use variables (VAR…RETURN) to avoid redundant calculations and improve readability.
10. Format your DAX code. Properly indented and spaced DAX is essential for long-term maintainability.

We follow the "8-10 rule" — a maximum of 8 visuals per report page and 10 tiles on a dashboard — to keep reports uncluttered and insights immediately digestible.

1. Most important data goes top-left. Users scan left to right, top to bottom.
2. Accentuate the most important metric — make it visually different and immediately noticeable.
3. Use the right visual for the story — don't use a bar chart where a line chart tells the story better.
4. Only certified third-party visuals — and even then, sparingly.
5. Filters over slicers — use filters more and slicers only where truly needed for usability.
6. Import only necessary fields and tables to keep the model lean.
7. Consistent formatting — one colour scheme, one font size, one style throughout the report.
8. Labels and annotations — they provide context and reduce the time users spend interpreting visuals.
9. Drill-through and drill-down — let viewers explore the detail without cluttering the summary view.

VBA Macro projects break for several common reasons. We diagnose and fix them — and improve the code so they don't break again.

Common causes we fix

• Errors caused by changes in the Windows or Office version — changes in memory allocation and data types.
• .xls vs .xlsx file type: Old macros may not accept .xlsx files and need updating.
• Hard-coded values for dimensions, file locations, or sheet names that have since changed.
• Overflow errors — variable types or calculations that no longer hold the data they receive.
• Broken file paths to external workbooks, folders, or network drives.

We also enhance your existing macros — improve speed, add error handling, and modernise code structure so it is easier to maintain.

Power Query models fail when source data or structure changes. We locate the root cause and rebuild the query so it handles future changes gracefully.

Common causes we fix

• Changed column names: Power Query references column names by exact text — a renamed column breaks the query.
• Case sensitivity errors: M Language is case-sensitive; a single incorrect case causes failures.
• Formula errors: Issues with custom columns where a formula can no longer compute.
• Unavailable data source: The source file has moved, been renamed, or the connection credentials have changed.
• Date formatting errors: Regional date formats (DD/MM vs MM/DD) that Power Query cannot recognise automatically.
• Data processing errors: Problems with the data itself — unexpected nulls, type mismatches, or corrupt records.

DAX measures and Data Model relationships break when underlying data or structure changes. We diagnose the issue, fix it, and make the model more robust going forward.

Common causes we fix

• Broken relationships: Complex or ambiguous relationships between tables causing incorrect results.
• Changed column names: DAX formulas require exact column names — even an extra space causes an error.
• Data reference errors: Measures referencing columns or tables that have been renamed or removed.
• DAX function argument errors: Incorrect filter expressions, wrong data types passed to functions like CALCULATE.
• Data type conversion errors: Invalid implicit type conversions causing evaluation-time failures.
• Software update compatibility: Changes in Excel or Windows that affect how the model evaluates.

Power BI reports can break or slow down for a variety of reasons. We identify the bottleneck, fix the errors, and optimise the report for reliable performance.

Common causes we fix

• Large datasets overloading memory: Unoptimised models consume too much memory, causing slow refresh or crashes.
• Non-optimised queries: Queries that take too long to run, causing Power BI to time out or produce errors.
• Corrupt PBIX files: We attempt recovery and rebuild affected sections of the file.
• Memory-intensive calculations: Complex measures consuming more memory than Power BI's configured limit.
• Discontinued third-party visuals: Custom visuals that are no longer available or supported.
• Data source errors: Errors in the underlying data source file causing refresh failures.