Say Goodbye to Excel Errors : Ditch INDIRECT for These Smarter Methods
But here's the good news: Excel has evolved, and so should your approach. My Online Training Hub explains why INDIRECT is no longer the powerhouse it once was and introduce you to smarter, more robust alternatives like Power Query, FILTER, and LAMBDA. These modern tools not only eliminate the pitfalls of INDIRECT but also open the door to faster, more scalable, and error-resistant workflows. Whether you're managing massive datasets, creating dynamic reports, or collaborating across teams, these alternatives will transform the way you work with Excel. Ready to rethink how you approach dynamic referencing? Let's uncover what's possible when you leave INDIRECT behind. Modern Alternatives to INDIRECT What Makes INDIRECT Problematic?
The INDIRECT function is classified as a volatile function, meaning it recalculates every time any change occurs in the workbook. While this behavior might seem beneficial in certain scenarios, it can significantly slow down large or complex workbooks, especially those containing numerous formulas. This performance issue becomes even more pronounced as datasets grow in size and complexity.
Another drawback of INDIRECT is its fragility. If a referenced sheet or named range is renamed, the function breaks, resulting in errors that can be difficult to trace and resolve. This makes it less reliable in dynamic or collaborative environments where changes to workbook structure are common.
Additionally, INDIRECT cannot reference data from closed workbooks. This limitation reduces its utility in workflows that involve external data sources or require integration across multiple files. As modern data management increasingly relies on collaboration and scalability, these shortcomings make INDIRECT less practical for today's needs. Modern Alternatives to INDIRECT
Excel has introduced several powerful tools and functions that address the limitations of INDIRECT while offering greater flexibility, reliability, and performance. These alternatives are designed to handle dynamic referencing and data management tasks more effectively: Power Query: A versatile tool for consolidating, cleaning, and transforming data from multiple sources into a single structured table. Unlike INDIRECT, Power Query is non-volatile and independent of workbook states, making it ideal for scalable and dynamic reporting.
A versatile tool for consolidating, cleaning, and transforming data from multiple sources into a single structured table. Unlike INDIRECT, Power Query is non-volatile and independent of workbook states, making it ideal for scalable and dynamic reporting. SWITCH Function: SWITCH provides a clear and efficient way to toggle between predefined values, such as column names or categories. It eliminates the need for INDIRECT's dynamic referencing and is easier to audit, reducing the risk of errors.
SWITCH provides a clear and efficient way to toggle between predefined values, such as column names or categories. It eliminates the need for INDIRECT's dynamic referencing and is easier to audit, reducing the risk of errors. FILTER Function: FILTER is designed for extracting subsets of data or creating dependent dropdown lists based on specific criteria. It dynamically adjusts to changes in data without the performance issues associated with INDIRECT.
FILTER is designed for extracting subsets of data or creating dependent dropdown lists based on specific criteria. It dynamically adjusts to changes in data without the performance issues associated with INDIRECT. LAMBDA Function: LAMBDA allows users to create custom functions tailored to specific needs. This modern feature offers advanced flexibility and can replace complex INDIRECT formulas with reusable, maintainable solutions. INDIRECT The Excel Function You Should Stop Using
Watch this video on YouTube.
Take a look at other insightful guides from our broad collection that might capture your interest in Excel functions. How to Use These Alternatives
These modern tools can seamlessly replace INDIRECT in various scenarios, offering improved performance and reliability. Here are some practical examples: Power Query for Data Consolidation: Instead of using INDIRECT to reference multiple sheets, Power Query can combine data from different sheets or files into a single table. This approach simplifies reporting, ensures consistency, and eliminates the volatility associated with INDIRECT.
Instead of using INDIRECT to reference multiple sheets, Power Query can combine data from different sheets or files into a single table. This approach simplifies reporting, ensures consistency, and eliminates the volatility associated with INDIRECT. SWITCH for Dynamic References: SWITCH can replace INDIRECT in scenarios where dynamic column references are needed. By defining a set of predefined values, SWITCH provides a faster, more transparent, and easier-to-debug solution.
SWITCH can replace INDIRECT in scenarios where dynamic column references are needed. By defining a set of predefined values, SWITCH provides a faster, more transparent, and easier-to-debug solution. FILTER for Dropdown Lists: FILTER can be used to create dependent dropdown lists that automatically adjust to changes in your data. This eliminates INDIRECT's volatile behavior while improving reliability and performance.
FILTER can be used to create dependent dropdown lists that automatically adjust to changes in your data. This eliminates INDIRECT's volatile behavior while improving reliability and performance. LAMBDA for Custom Functions: For complex scenarios requiring reusable logic, LAMBDA enables you to create custom functions that replace INDIRECT. These functions are easier to maintain and adapt to evolving requirements. Why Transitioning Away from INDIRECT Is Beneficial
Adopting modern alternatives to INDIRECT offers several significant advantages: Improved Performance: Non-volatile tools like Power Query ensure faster recalculations and smoother operation, even with large datasets. This is particularly important in data-intensive workflows where performance is critical.
Non-volatile tools like Power Query ensure faster recalculations and smoother operation, even with large datasets. This is particularly important in data-intensive workflows where performance is critical. Enhanced Reliability: Modern functions are less prone to errors caused by changes in sheet names or external references. This reduces the risk of broken formulas and ensures greater consistency in your workbooks.
Modern functions are less prone to errors caused by changes in sheet names or external references. This reduces the risk of broken formulas and ensures greater consistency in your workbooks. Scalability: Tools like Power Query and LAMBDA are designed to handle growing data needs and evolving requirements. They provide a future-proof solution for managing complex datasets and workflows.
Tools like Power Query and LAMBDA are designed to handle growing data needs and evolving requirements. They provide a future-proof solution for managing complex datasets and workflows. Easier Debugging: Functions like SWITCH and FILTER are more transparent and straightforward, making it easier to identify and resolve issues. This simplifies the process of maintaining and auditing your workbooks. Adopting a Modern Approach to Excel
While the INDIRECT function once served as a valuable tool for dynamic referencing, its limitations make it less practical in today's data-centric workflows. Modern tools like Power Query, SWITCH, FILTER, and LAMBDA offer more efficient, reliable, and scalable solutions for managing data and creating dynamic references. By transitioning to these advanced features, you can enhance workbook performance, reduce errors, and streamline your processes. Embracing these modern alternatives is not just a technical improvement—it's a strategic step toward more effective and efficient data management.
Media Credit: MyOnlineTrainingHub Filed Under: Guides
Latest Geeky Gadgets Deals
Disclosure: Some of our articles include affiliate links. If you buy something through one of these links, Geeky Gadgets may earn an affiliate commission. Learn about our Disclosure Policy.
Hashtags
Try Our AI Features
Explore what Daily8 AI can do for you:
Comments
No comments yet...
Related Articles
Reuters
13 minutes ago
- Reuters
EU's $250 billion-per-year spending on US energy is unrealistic
BRUSSELS/HOUSTON, July 28 (Reuters) - The European Union's pledge to buy $250 billion of U.S. energy supplies per year is unrealistic because it would require the redirection of most U.S. energy exports towards Europe and the EU has little control over the energy its companies import. The U.S. and EU struck a framework trade deal on Sunday, which will impose 15% U.S. tariffs on most EU goods. The deal included a pledge for the EU to spend $250 billion annually on U.S. energy - imports of oil, liquefied natural gas and nuclear technology - for the next three years. Total U.S. energy exports to all buyers worldwide in 2024 amounted to $318 billion, U.S. Energy Information Administration data showed. Of that, the EU imported a combined $76 billion of U.S. petroleum, LNG and solid fuels such as coal in 2024, according to Reuters' calculations based on Eurostat data. More than tripling those imports was unrealistic, analysts said. Arturo Regalado, senior LNG analyst at Kpler, said the scope of the energy trade envisioned in the deal "exceeds market realities." "U.S. oil flows would need to fully redirect towards the EU to reach the target, or the value of LNG imports from the US would need to increase sixfold," Regalado said. There is strong competition for U.S. energy exports as other countries need the supplies - and have themselves pledged to buy more in trade deals. Japan agreed to a "major expansion of U.S. energy exports" in its U.S. trade deal last week, the White House said in a statement. South Korea has also indicated interest in investing and purchasing fuel from an Alaskan LNG project as it seeks a trade deal. Competition for U.S. energy could drive up benchmark U.S. oil and gas prices and encourage U.S. producers to favour exports over domestic supply. That could make fuel and power costs more expensive, which would be a political and economic headache for U.S. and EU leaders. Neither side has detailed what was included in the energy deal - or whether it covered items such as energy services or parts for power grids and plants. The EU estimates its member countries' plans to expand nuclear energy would require hundreds of billions of euros in investments by 2050. Its nuclear reactor-related imports, however, totalled just 53.3 billion euros in 2024, trade data shows. The energy pledge reflected the EU's analysis of how much U.S. energy supply it could accommodate, a senior EU official said, but that would depend on investments in U.S. oil and LNG infrastructure, European import infrastructure, and shipping capacity. "These figures, again, are not taken out of thin air. So yes, they require investments," said the senior official, who declined to be named. "Yes, it will vary according to the energy sources. But these are figures which are reachable." There was no public commitment to the delivery, the official added, because the EU would not buy the energy - its companies would. Private companies import most of Europe's oil, while a mix of private and state-run companies import gas. The European Commission can aggregate demand for LNG to negotiate better terms, but cannot force companies to buy fuel. That is a commercial decision. "It's just unrealistic," ICIS analysts Andreas Schröder and Ajay Parmar said in written comments to Reuters. "Either Europe pays a super high non-market reflective price for U.S. LNG or it takes way too much LNG volumes, more than it can cope with." The United States is already the EU's top supplier of LNG and oil, shipping 44% of EU LNG needs and 15.4% of its oil in 2024, according to EU data. Raising imports to the target would require a U.S. LNG expansion way beyond what is planned through 2030, said Jacob Mandel, research lead at Aurora Energy Research. "You can add on capacity," Mandel said. "But if you're talking about the scale that would be necessary to meet these targets, the $250 billion, then it's not really feasible." Europe could buy $50 billion more of U.S. LNG annually as supply increases, he said. The EU has said it could import more U.S. energy as its plan advances to end Russian oil and gas imports by 2028. The EU imported around 94 million barrels of Russian oil last year - 3% of the bloc's crude purchases - and 52 billion cubic metres (bcm) of Russian LNG and gas, according to EU data. For comparison, the EU imported 45 bcm of U.S. LNG last year. Higher EU fuel purchases would, however, run counter to forecasts for EU demand to decline as it shifts to clean energy, analysts said. "There is no major need for the EU to import more oil from the U.S., in fact, its oil demand peaked a number of years ago," Schröder and Parmar said. ($1 = 0.8571 euro)
Reuters
15 minutes ago
- Reuters
Exclusive: Cadence to plead guilty and pay $140 million to US for China sales
July 28 (Reuters) - Cadence Design (CDNS.O), opens new tab agreed to plead guilty and pay more than $140 million to resolve U.S. charges for selling its chip design products to a Chinese military university believed to be involved in simulating nuclear explosions, the Justice Department said on Monday. Cadence was accused of violating export controls by illegally selling chip design software and hardware to front companies representing China's National University of Defense Technology. NUDT's supercomputers are thought to support nuclear explosive simulation and military simulation activities, according to U.S. Commerce Department notices restricting shipments to the university. San Jose, California-based Cadence noted a charge related to the legal proceedings in its quarterly results, also released on Monday. Cadence shares rose 7.8% after it posted the news and its quarterly results. The deal, which comes as the U.S. and China meet for new trade talks, shows the U.S. is still willing to enforce U.S. export controls on China, even as it relaxes some of the restrictions as part of negotiations. NUDT was put on the Commerce Department's restricted trade list in 2015 to keep it from using U.S. technology to power its supercomputers, according to department postings. Other aliases and locations were added to the university's listing in 2019 and 2022, including Hunan Guofang Keji University, Central South CAD Center, and CSCC. The U.S. investigation into Cadence, which began more than four years ago, involved 'historical sales by Cadence to customers in China,' according to a company filing. Cadence received a subpoena from the U.S. Commerce Department in February 2021, demanding records related to certain customers in China. A related November 2023 subpoena followed from the Justice Department over the company's business activity in China. Entities are placed on the restricted trade list, formally known as the entity list, for activities deemed contrary to U.S. national security or foreign policy interests. U.S. companies are not allowed to ship goods and technology to them without licenses from the Commerce Department, which are generally denied. Cadence will hold a call about its second-quarter financial results at 2 p.m. Pacific Time (2100 GMT) on Monday. Cadence, whose customers include major semiconductor manufacturers and companies such as Nvidia and Qualcomm, is known for its electronic computer-aided design software. Electronic design automation (EDA) tools are key to designing chips and verifying that they are bug-free. NUDT has developed chips to power university supercomputers, including Tianhe-2, once touted as the world's best supercomputer, which the U.S. believes has been used in research on or the development of nuclear explosive devices. Twelve percent of Cadence's revenue came from China last year, down from 17% in 2023, amid regulatory developments and geopolitical tensions.
Daily Mail
15 minutes ago
- Daily Mail
Chaos at major airline as 270 pilots grounded and 140 demoted... sparking fears for holiday travel
Airlines were supposed to rake in record profits in 2025. Instead, another company is cutting hundreds of staffers. Spirit Airlines said on Monday it will furlough about 270 pilots while demoting another 140. It's the latest major move from the cash-strapped budget carrier, as it looks to scale down its workforce, downsized schedule, and return to profitability. The furloughs will go into effect on Nov. 1, while designation downgrades for captains will take place on Oct. 1, the company told Reuters in an emailed statement. 'We are taking necessary steps to ensure we operate as efficiently as possible as part of our efforts to return to profitability,' the airline said. The announcement, first reported by Bloomberg News, comes as Spirit tries to overhaul its business to move away from its no-frills image and rebrand as a premium airline. The Florida-based carrier had filed for bankruptcy protection last November, following years of losses, heavy debt, and failed merger attempts. It emerged from bankruptcy in March. 'Spirit continues to shrink, and with it, the value of pilot seniority and Spirit careers continues to erode,' said Captain Ryan Muller, chairman of the Spirit unit of the Air Line Pilots Association, adding that this marks the third round of pilot furloughs and downgrades since September 2024. The union said it is working on a third Furlough Mitigation Memorandum of Understanding to pursue voluntary options to reduce the scope of the furloughs and preserve pilot careers.
