Tag: excel

Errors in Excel – Identifying and Overcoming Them Quickly!

Posted on by bonbon

Common Errors in Excel and How to Fix Them

Excel is a powerful tool, but it’s not without its quirks. Anyone who has spent time working with spreadsheets has encountered those frustrating error messages. Whether you’re a beginner or an experienced user, understanding and resolving these errors is crucial for accurate data analysis. In this article, we will explore some of the most common Excel errors and provide detailed solutions to fix them.

1. #VALUE! Error: Understanding Invalid Data Types

The #VALUE! error occurs when Excel can’t recognize the data type in a formula or function. This error often happens when you’re trying to perform calculations with incompatible data types.

To fix this error, double-check your formulas and ensure that all the data types are compatible. For example, make sure you’re not trying to perform mathematical operations on text cells without converting them to numbers first.

2. #REF! Error: Dealing with Invalid Cell References

The #REF! error indicates an invalid cell reference, often caused by deleting or moving cells that are referred to in formulas. This error can mess up your entire spreadsheet if not addressed promptly.

To fix #REF! errors, carefully review your formulas and check if any cell references are no longer valid. Update the formulas to use the correct cell references, or adjust your data ranges to include the appropriate cells.

errors in excel, excel error, help me; person pressing button under help me sgh

Photo by Mikhail Nilov on Pexels.com

3. #DIV/0! Error: Handling Division by Zero

The #DIV/0! error occurs when you’re trying to divide a number by zero. Excel cannot perform this operation, and it displays this error to indicate the problem.

To avoid #DIV/0! errors, you can use the IF function to check if the divisor is zero before performing the division. For example, you can modify your formula like this:

=IF(B2<>0, A2/B2, "Divide by Zero Error")

This formula checks if cell B2 is not zero. If it is, it performs the division; otherwise, it displays a custom error message.

4. #NAME? Error: Fixing Unrecognized Functions or Formulas

The #NAME? error occurs when Excel doesn’t recognize a function or formula name in a cell. This can happen due to misspellings, missing quotation marks, or incorrect syntax.

To fix #NAME? errors, double-check the spelling and syntax of your functions and formulas. Ensure that all function names are spelled correctly and are accompanied by the necessary parentheses and arguments.

5. #N/A Error: Handling Not Available or Missing Data

The #N/A error stands for “Not Available” and occurs when Excel can’t find the value you’re looking for, often in functions like VLOOKUP or MATCH.

To handle #N/A errors, you can use the IFERROR function to display a custom message or value when an error occurs. For instance:

=IFERROR(VLOOKUP(A2, Table, 2, FALSE), "Not Found")

In this formula, if the VLOOKUP function returns an error, it will display “Not Found” instead of #N/A.

6. #NUM! Error: Dealing with Invalid Numerical Values

The #NUM! error in Excel occurs when a numerical calculation is invalid, such as taking the square root of a negative number.

To fix #NUM! errors, review the mathematical operations in your formulas. Check for any calculations that might result in negative numbers where they are not allowed or other mathematically impossible operations.

7. Errors in Excel – Prevention Techniques: Data Validation and Cell Auditing

Preventing errors is as important as fixing them. Excel offers various tools to help you prevent errors before they occur. One such tool is data validation, which allows you to set rules for the type and range of data that can be entered into a cell. By using data validation, you can significantly reduce the likelihood of invalid data causing errors in your spreadsheets.

Another helpful feature is cell auditing, which enables you to trace the precedents and dependents of a cell. By understanding how cells are connected in your spreadsheet, you can identify potential sources of errors and correct them proactively.

8. Excel’s Built-in Error Checking Tools: How to Use Them Effectively

Excel provides built-in error checking options that can automatically detect and highlight potential errors in your spreadsheet. To access these options, go to the “Formulas” tab, click on “Error Checking,” and choose “Error Checking Options.”

Here, you can customize the types of errors Excel should check for and how it should alert you. By utilizing these built-in tools, you can quickly spot errors and address them before they cause significant issues in your calculations.

In conclusion, mastering the art of handling common Excel errors is essential for anyone working with spreadsheets. By understanding the causes of these errors and implementing the provided solutions, you can ensure the accuracy and reliability of your Excel-based analyses. Additionally, utilizing preventive measures and Excel’s error checking tools will empower you to create error-free spreadsheets, saving you time and frustration in your data-driven tasks.

Remember, practice makes perfect. The more you work with Excel and encounter these errors in Excel, the more adept you’ll become at identifying and resolving them efficiently. Happy Excel-ling!

code execution has been interrupted window

How to Make Excel Wait or Pause in a VBA Macro

Posted on by Bb

When running macros, I have often found the need for an Excel wait or pause, mainly so that other things can happen. DoEvents surely helps for waiting for things to happen within Excel, but sometimes you need to wait for processes to start or finish outside of Excel before moving onto other steps in VBA.

Why Wait?

I am a big fan of automating processes, especially with VBA in Excel. For example, I update and save a spreadsheet to SharePoint that a Power BI model is using. I have a Power Automate process set up that automatically refreshes the Power BI model whenever this particular spreadsheet is updated. The challenge is that Power BI temporary locks the workbook as it reads it, so if I am updating a few times in a short amount of time, Excel needs to wait until the SharePoint file is unlocked.

Also, I have another process where I call a Python script and once that is done, I continue on with VBA code to process the workbook Python outputs. I need Excel to wait while the Python code runs. Side note: let me know if you would like me to write more about these other automated processes.

The Solution

Good news – making Excel wait is very easy! Here is the simple code:

This will have Excel wait for 50 seconds since the time is represented as (H:MM:SS). You can adjust as needed.

Bonus

In my code, I don’t need it to wait every time – only when it runs into an issue saving the workbook on SharePoint. Here is how I have set my code using an error handler in my VBA macro.

I hope this helps you! Please comment or reach out if you would like to see other topics covered. I truly enjoy writing about Excel tips and tricks, especially VBA and automation!

Addressed in this post (to help others find this):

  • How to pause Excel
  • How to make Excel pause
  • Make VBA pause
  • Make VBA wait
  • Make Excel wait
  • VBA code wait, VBA macro wait
  • VBA code pause, VBA macro pause
  • VBA wait for another application

Referencing an Excel Sheet With An Apostrophe in its Name – Solved!

Posted on by Bb

The Problem – Apostrophes in Sheet Names

You are trying to use a formula to reference an Excel Sheet with an apostrophe in its name and you keep receiving an error message or a #REF! reference error.

The Solution (Very Easy!)

Great news! Simply replace the apostrophe with two apostrophes in your formula. That’s right, two consecutive apostrophes, not a quote. Very simple solution indeed!

Below I provide two examples. You can manually update the name in your formula (or in the cell you are referencing if using an indirect lookup), or if working with many sheet names, you may opt to use the substitute solution shown below (formula displayed in E5).

The substitute can be nested in an indirect formula also.

Excel Sheet with an apostrophe solution
Excel Sheet With An Apostrophe Solution

Please comment below on any other challenges you would like for me to cover. I am contemplating doing a quick solution series in this format!

Also, be sure to check out Dose for Excel (click the image below)! Add over 100 functions to Excel to increase your productivity and more!  Disclosure: this is an affiliate link, so I may earn a small commission if you decide to purchase the add-in. Thanks for supporting my blog!

Dose for Excel - +100 Functions

Some topics addressed in this post:
Ref Error in lookup
#REF! Error in lookup
Ref Error in formula
#REF! Error in formula
How to fix ref error in Excel from apostrophe
Apostrophe in Excel sheet name issue



DROP, TAKE & VSTACK: How They Are Useful! Spilled Frequency Distribution with Total Row.

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/EMT1788-1789.xlsx
Learn how to create a formula Frequency Distribution Report with Total Row and learn how the DROP and TAKE Array Functions can be helpful in dynamic spilled array formula reports..
Topics:
1. (00:00) Introduction
2. (00:20) Continue reading “DROP, TAKE & VSTACK: How They Are Useful! Spilled Frequency Distribution with Total Row.” →

Executing SQL in Excel Tool/Template

Amazing Method for Executing SQL Queries in Excel Using VBA (Import Data) – No Need to Use Multiple Applications!

Posted on by Bb

I have found several use cases for my team and I to save time and utilize Excel alone for pulling data via SQL, as opposed to running queries in Access, SSRS, SSMS or Toad and then exporting that data into Excel for manipulation and analysis. There is not only the benefit of saving time by skipping the export/import process, but also in the ability to build templates/files and save them for quick and easy future SQL pulls. The days of needing multiple applications for your SQL pulls to import into Excel are over!

The best use cases I’ve found that support this method are repeatable processes where the same input variables are required each time, and where the query results will not exceed the row limitations of an Excel sheet (~1M). Even in this case, you may find the first two sections of this post useful for learning about constructing and executing SQL queries for use outside of Excel. This alone may save you time if you are in the habit of writing long and involved queries with changing criteria.

If you are exceeding the Excel row limit but still want to work in Excel, using Power Pivot can handle this, so you may wish to utilize Power Query instead. See my post on utilizing parameters with Power Query here for more information on how to import the data this way.

I have also found this approach to be useful when pulling data from different sources using the same criteria. It saves me lots of time.

In this example, we will construct and then run a simple SQL query using a few parameters/variables that we enter into the spreadsheet.

Follow along so you can see how it works in practice, and then I encourage you to try it out with your own data. Once you master this method, hopefully you will find amazing ways to apply it to your own work!

A copy of the file described in this post is available for purchase – just reach out to me for information. A more complex version that handles wildcards is also for sale.

Setting Up the Variables/Criteria/Parameters for Executing SQL Queries in Excel

My goal will be to run this query: Select * from Store where CreatedDate > #3/1/2022# and State = ‘GA’

  • The two variables in this example will be the created date (3/1/2022) and the State (GA).
  • At the top of my first sheet, we will designate named ranges for the two inputs.
  • Simply type in Created Date in cell A1 to identify the input, then in cell B1, type in 3/1/22.
  • Make B1 a named range – we will call this CreatedDate.
  • Similarly, in A2, type in State, and then in B2, type in GA. Ultimately, you can add in data validation and use lists, but let’s keep it very simple for now.
  • Make cell B2 a named range called State.
    • If you are not familiar with named ranges, the easiest way to make one is to select the cell or cells and then type the name into the dropdown box at the top left next to the formula bar, where the cell address is displayed. Alternately, you can press CTRL+F3 to bring up the Named Ranges dialog box.
SQL Execution in Excel VBA - setting up the inputs for the variables using Named Ranges
Executing SQL Queries in Excel – Setting up Inputs for Variables Using Named Ranges

Constructing the SQL Statement

From here, we can either construct the SQL statement in an Excel sheet or in VBA. I have found that it is far easier to construct in an Excel sheet, especially for complex statements with many criteria. This way certainly allows for more flexibility and better troubleshooting in my opinion. Also, it’s easier to manage from a support standpoint.

  • I will add a new sheet/tab to house the SQL Statement. I will start by typing in the full statement I expect in to A1, just to use as a reference as I build out the statement in the cells below.
  • I then break the statement out line by line, using column A for the variables, and column B for the statements and column C for the joining of statements with variables.
  • Finally, I use the TEXTJOIN function in cell A4 to join together all of the rows in the combined section. I used a space as the delimiter (” “). This ensures proper spacing throughout the resulting SQL statement.
  • I then make cell A4 a named range called SQLStatement.
  • See image below to see how I have achieved all of this.
    • I have included some helper/formula notes on how to format the variables, since the SQL statement has to be properly formatted in order to work.
Executing SQL Queries in Excel variable setup
Construction of SQL Queries in Excel

VBA Code for Executing SQL Statement and Importing the Data

  • Open the VBA editor (Alt + F11).
  • Add a new module.
  • Add a new subroutine. I’ve called mine SQLPull.
  • Very important step – enable the required references. Go to the Tools menu and select References. Select the ActiveX Data Objects 2.8 Library and Recordset 6.0 Library – see the last two checked in the image below (yours may be different versions). Click OK to save.
VBA Project References – ActiveX Data Objects/Recordset

Here is an overview the VBA code I wrote, that is pasted below.

  • Note that it will connect to the database you identify after you update the connection string if needed.
  • It will then execute the SQL select statement and grab the recordset.
  • The code will then write the headers from the query into the cells identified.
  • Next, it will paste the rows that are returned by the SQL query, starting in the specified cell.
  • Finally it will close the connection and end the subroutine.
  • For my applications of this, I like to switch to manual calculation and turn off screen updating because I have found it improves the speed of loading the data. You may choose to leave these alone if working with less data.

I have added commentary and explanations throughout to hopefully help you to modify as needed to support your own needs.

Sub SQLPull()

‘* www.bonbonsguide.com *
‘Importing Data into Excel using a SQL select statement in a cell

Dim Conn As New ADODB.Connection
Dim mrs As New ADODB.Recordset
Dim DBPath, sconnect, sqlstring As String
Dim icols As Long

””””””””””””””””””””””””’
‘Database Name/Path
””””””””””””””””””””””””’
DBPath = STOREDB

‘NOTE:
‘If you are querying a SQL Server or Oracle DB, use the name of the database:
‘DBPath = YourDatabaseName

‘If you are querying an Access Database, use the path to the file in quotes:
‘DBPath = “\myfileserver\Bonbon\MyAccessDB.accdb” OR “F:\Bonbon\MyAccessDB.accdb”

””””””””””””””””””””””””’
‘Connection String
””””””””””””””””””””””””’
‘The DSN is the existing ODBC connection on your PC – this must be set up first!

‘Uncomment the applicable sconnect string for your database and modify as needed. For MS Access, no modifications should be needed.

‘SQL Server using Windows Authentication:
sconnect = “Provider=MSDASQL.1;DSN=STOREDB;DBQ=” & DBPath & “;HDR=Yes’;”

‘ORACLE Connection using an UID/PWD:
‘sconnect = “Provider=MSDASQL.1;DSN=WAREHOUSE;uid=bonbonsguide;pwd=helpsus;DBQ=” & DBPath & “;HDR=Yes’;”

‘MS Access:
‘sconnect = “Provider = Microsoft.ACE.OLEDB.12.0; data source=” & DBPath & “;”

””””””””””””””””””””””””””””””””””””””””””””””””””””’
‘Set Timeouts (These may not be required in your environment)
””””””””””””””””””””””””””””””””””””””””””””””””””””’
Conn.ConnectionTimeout = 200
Conn.CommandTimeout = 200

””””””””””””””””””””””””’
‘Connect to datasource
””””””””””””””””””””””””’
Conn.Open sconnect

””””””””””””””””””””””””’””””””””””””””””””””””””’
‘VBA get SQL Statement from Sheet/Named Range
””””””””””””””””””””””””’””””””””””””””””””””””””’
sqlstring = Range(“SQLstatement”)

””””””””””””””””””””””””””””””””””
‘Get the recordset – this command will execute the SQL Statement’
””””””””””””””””””””””””””””””””””
mrs.Open sqlstring, Conn

””””””””””””””””””””””””””””””””””
‘Return the Header Names
‘”””””””””””””””””””””””””””””””””’
‘If you don’t need the headers or are using your own, comment the block out

‘Where Headers will be pasted:
‘Sheet1 = Sheet identifier – use Sheets(“name of sheet”) to use sheet name instead
‘Cells (4 – indicates row 4, + 3 indicates to start in column C) … Edit this as needed

For icols = 0 To mrs.Fields.Count – 1
Sheet1.Cells(4, icols + 3).Value = mrs.Fields(icols).Name
Next

””””””””””””””””””””””””””””””””””
‘OPTIONAL – SPEED UP IMPORT
””””””””””””””””””””””””””””””””””
‘If retrieving lots of records, it may speed it up if you set calculation to manual during the import process.
‘Setting the screen updating to false may also speed up the import. Comment these out if preferred.
Application.Calculation = xlCalculationManual
Application.ScreenUpdating = False

””””””””””””””””””””””””’””””””””””””””””””””””””’””””””””””””””””””””””””’
‘OPTIONAL – CLEAR DATA IN ROWS BEFORE PASTING RECORDSET OR DO OTHER PREP
””””””””””””””””””””””””’””””””””””””””””””””””””’””””””””””””””””””””””””’
‘Add your code here.

””””””””””””””””””””””””””””””””””
‘Paste the Rows/Records
‘”””””””””””””””””””””””””””””””””’
‘Importing rows returned – the range below will be where the data starts – line this up with the headers, one row below.
Sheet1.Range(“C5”).CopyFromRecordset mrs

””””””””””””””””””””””””””””””””””
‘Close the Recordset
””””””””””””””””””””””””””””””””””
mrs.Close

””””””””””””””””””””””””””””””””””
‘Close Connection
””””””””””””””””””””””””””””””””””
Conn.Close

”””””””””””””””””””””””””””””””””””””””””””’
‘Turn automatic calculation and screen updating back on.
”””””””””””””””””””””””””””””””””””””””””””’
Application.Calculation = xlCalculationAutomatic
Application.Calculate
Application.ScreenUpdating = True

End Sub

Wrapping Up

I hope this helps you out. Below is the final look of my file for this example. I have added a button for the users to click in order to run the VBA code and execute the SQL by assigning the macro. I have formatted so the data returns in a table for easy manipulation and analysis. I have also updated the general formatting and named the sheets.

To save you time, a copy of the file is available for purchase – just reach out to me for information.

I also have another sample template file available that allows you to put multiple criteria in a list and constructs the query accordingly, searching all items. It even allows for the use of wildcards by automatically formulating LIKE statements! Exciting stuff! Contact me if interested in purchasing.

Tool for Execution of SQL Queries in Excel (For Sale)
Tool for Execution of SQL Queries in Excel (For Sale)

VSTACK & 3-D References: Combine Worksheet Data into One Table!!!!

Posted on by Annie D

Learn how to combine data from multiple worksheets into a single table with a fully dynamic and automatically updating table.

Download Excel File: https://excelisfun.net/files/EMT1786.xlsx
Topics:
1. (00:00) Introduction
2. (00:48) Worksheet data and downside
3. (01:19) Worksheet Name Syntax
4. (02:10) Formula Continue reading “VSTACK & 3-D References: Combine Worksheet Data into One Table!!!!” →

TOCOL, DROP, EXPAND Array Functions to UnPivot Data into Proper Table.

Posted on by Annie D

Download Excel File: https://excelisfun.net/files/EMT1784-1785.xlsm
Learn how to convert a rectangle of data with row headers and columns headers into a proper table of data using Array Functions such as DROP, EXPAND, TOCOL and INDEX.
Topics:
1. (00:00) Introduction
2. (00:30) Continue reading “TOCOL, DROP, EXPAND Array Functions to UnPivot Data into Proper Table.” →

How To Filter Data Using Anti Join In Excel With Power Query

Posted on by Annie D

Automating your data filtering and cleaning processes can be such a huge time saver. For today’s video, George is going demonstrate how you can compare and merge queries by using Anti Join in Excel with Power Query.

***** Video Details *****
00:00 Continue reading “How To Filter Data Using Anti Join In Excel With Power Query” →

Average Excluding Min and Max Values -TRIMMEAN function in Excel formula

Posted on by Bb

Download Excel File: https://excelisfun.net/files/EMT1744.xlsx
Learn how to calculate the average while excluding the min and max values. See how to use TRIMMEAN function to exclude a given percentage of high and low data. Continue reading “Average Excluding Min and Max Values -TRIMMEAN function in Excel formula” →

rank in Access

Solved: Pass a Parameter to a SQL Query in Power Query

Posted on by Bb

I’ve done a fair amount of research around dynamic and parameterized queries, specifically around an Excel query parameter, as in trying to pass a parameter to a SQL query in Power Query.  After much trial and error, I finally have found a solution that works.  Here’s the quick and easy way to do this and harness the power of query folding.  This solution works in Excel – the process is a bit different in Power BI.

Please note that this post assumes you are at least moderately familiar with Power Query and SQL querying. Your mileage may vary depending on your situation.

In my example, “SParameter” is the name of the parameter I am using, and it represents a store number (retail location identifier). The related field in my SQL table is [Store].

Create the parameter:

  • Add a table, with a single record and column.  This can be on any tab in your workbook.
  • Add this table into Power Query by selecting the table, then Data > From Table/Range.  When the query editor opens, if necessary, change the data type (in my case I need it to be text).
  • Right click on the record and select “Drill Down”.  Since you have only a single record, this step will automatically create a parameter.
  • In the properties, I name this SParameter (you may enter the name of your choice for your parameter).  Note that the name is case sensitive.

Create the SQL query:

Build your SQL query as normal, but note that we are not going to put a where clause in referencing the new parameter here (see “Why Not Include the Parameter in the SQL Statement” section below).  Here’s my simple query example – notice that I’m not including the store field in the criteria section of the query though this is the field my parameter will ultimately filter on.

Select * FROM Storelist WHERE OpenDate < GetDate()-365

If you think like me, you may be pausing here – my table has millions of records and I don’t want them all pulling in – don’t worry, this is the beauty of the process.

  • Write your SQL statement in Power Query as noted above.
  • When the Query Editor returns the columns and record sampling, for the field you will be using your parameter, filter with any single value (Text Filters > Equals). This is simply a placeholder, to create the Power Query M formula for the next step.
  • You will now see that filtering in the formula bar. 
  • Replace the placeholder value with the name of your parameter, removing any quotes from the placeholder.  This is how it looks for me: = Table.SelectRows(Source, each [Store] = SParameter).
  • Power Query is smart enough to modify its native query to use the parameter, so it’s not going to pull in the millions of records and then filter after the fact.  Success!!  This is the power of Query Folding!
  • Side note – if you are wondering about GetDate(), this is the SQL current system date, so in a way this is technically a dynamic parameter as well.  In my case, the query will only return stores with an open date that is older than 365 days based on today’s date.  You may find this useful for your query as well.

Why Not Include the Parameter in the SQL Statement?

In the SQL statement, logic would lead us to add the store number/SParameter as part of the criteria in the select statement.  It may seem counter intuitive, but we cannot successfully put the parameter directly into the SQL code/query.  I have tried several times, but the challenge is that you are combining data sources and at this time, this is not allowed due to the built in security (SParameter portion is local, SQL portion is external).

If you are tempted to test this yourself, the format would be Select * FROM Storelist WHERE Open < GetDate()-365 and Store = “&SParameter&”.  You will likely run into this error “Formula.Firewall: Query ‘SParameter (2)’ (step ‘Filtered Rows’) references other queries or steps, so it may not directly access a data source. Please rebuild this data combination.”

I truly hope this post helped you out.  If so, please feel free to leave a comment below letting me know so, and if you’d like, add what you’d like me to cover in a future post. Also, feel free to share this with someone else who may find it useful.

Please bookmark and subscribe to my blog!  I am always curating and adding new, relevant content! Thanks so much!

Also, be sure to check out Dose for Excel (click image below)!  Add over 100 functions to Excel to increase your productivity and more!  They have a free trial right now, so you can try it out today!  Disclosure: this is an affiliate link, so I may earn a small commission if you decide to purchase the add-in.  Thanks for supporting my blog!

Dose for Excel - +100 Functions

Beautiful: What Fruit Is New Today – 2300

Posted on by Annie D

A tough question from the MrExcel Message Board. There are 1500 rows showing products in a delimited string. Someone might have had “Cherry, Apple” yesterday and today they have “Apple, Banana, Cherry”. Can you use a VBA macro to subtract Cherry Apple from Apple Banana Cherry and end up with just Banana?
Today, I end up solving this with Excel Power Query, Split by Delimiter To Rows, and then a Right Anti-Join. The best part: you can refresh the query tomorrow.

View on YouTube

Post to New MrExcel Forum With XL2BB – 2299

Posted on by Annie D

We’ve migrated the 1.4 Million posts in the MrExcel Message Board to a new board software. One thing that is different: the steps to post your spreadsheet to the board. This video will take a look at the free XL2BB Excel add-in that you can use.

View on YouTube