Microsoft Excel





Microsoft Excel topics include formulas, formatting, VBA macros and user-defined functions, and everything else related to the spreadsheet user interface, including error messages.

Share tech news, updates, or what's on your mind.

Sign up to Post

Wrapper-2, VBA.  Show result of Excel Worksheet Function in Access Query
A query can call a function, and a function can call Excel, even though we are in Access. This is Part 2, and steps you through the VBA that "wraps" Excel functionality so we can use its worksheet functions in Access.

The declaration statement defines the function name and its return data type. The values it needs to calculate are specified as parameters, or arguments.

Create an error handler that will exit without the user realizing there might have been a problem, and define a default value of zero for the function return value.

See how to open Excel and refer to it so code in Access can use it. Reuse the Excel application object to make susbsequent processing faster ... so, how do we clean that up?

Use the function in a query! Part 1 shows a query with the calculated column that calls this code:

Even though topics may be technical, I do my best to consider that a beginner may be watching, so extra detail is added. Hopefully not so much that more advanced users turn away ... maybe you will comment and let me know your thoughts about this? Thanks!

have an awesome day,

1. Dimension oExcel as object at top of module

2. Name of function follows the "Function" keyword

3. Enclose function parameters in parentheses

4. Declare data type of return value

5. Set up error handler

For more information on the error handling, here is a short video: Basic-Error-Handling-code-for-VBA-and-Microsoft-Office.html

6. Define default return value

7. Validate parameters -- not done in this example

8. See if Excel is loaded and ready to use

if not, then open Excel in the background and use oExcel to represent it

9. Call the Excel function you want and pass parameters

10. Assign the result to the function return value

Learn SQL Server Core 2016
LVL 12
Learn SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

How To Insert Foreign and Special Characters when in Windows Applications
Enter Foreign and Special Characters

Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~

Use these codes in any Windows application! ... whether it is a Microsoft Office product like Excel, Word, PowerPoint, Access,... or a simple program like NotePad.

Along these lines, I've written a tool to make it easier to input and lookup Unicode when you are in Access, and am making another video on Unicode - so be on the lookout for it.

Please Like, Comment, and Share with your friends ~

have an awesome day,

1. To insert a special character, make sure Num Lock is on

2. Hold down the ALT key

3. Type the ASCII code on the numeric keypad

4. Release the ALT key

How to Add a "Save As" icon to the Quick Access Toolbar
How can you see what you are working on when you want to see it while you to save a copy?

Add a "Save As" icon to the Quick Access Toolbar, or QAT.

That way, when you save a copy of a query, form, report, or other object you are modifying, you can still see what you are doing and can give your object a better name.

While Access is used to demonstrate,  you can do this in other Office products like Word, Excel, and PowerPoint too.

have an awesome day,

1. click on down arrow at end of QAT to Customize it

2. Choose "More Commands..." from the drop-down menu

3. choose to show 'All Commands'

4. click on the last command in the list of QAT commands on the right

   or whatever command you want to add the command below

5. double-click <Separator> in the left list to add it to the right list below what is selected

6. in the left list, doube-clicm Save As to addit below what is selected (now the separator)

7. click OK to close the Customize the Quick Access Toolbar dialog box

8. Enjoy faster saving!

Enumerating Prime Numbers in Excel / VBA
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11).
The larger templates are marginally faster (4 - 6%) but at great expense: 100's or 1000's of lines of code.  

The macro assigned to my "Find Primes" button is listed below:

Option Explicit

Public i As Long
Public j As Long
Public estr As Double    
Public esti As Long              ' used with estr to estimate the size of the primes array
Public iend As Long            ' user input - program will enumerate all prime <= iend
Public sr As Long                 ' square root of iend
Public PrX() As Boolean      ' Templated Possible Primes
Public Primes() As Long      ' # Primes <= iend

Sub DefinePrimes()

With ThisWorkbook.Worksheets("Sheet1")   ' rename the sheet if needed
  iend = .Cells(1, 5)
  If iend < 40 Then
    iend = 40
  End If
  estr = 1 / (WorksheetFunction.Ln(iend) - 1.15)
  esti = Int(estr * iend)
ReDim PrX(iend + 30)           ' we always want to have a little extra space
ReDim Primes(esti)               ' we always want to have a little extra space
  sr = Sqr(iend)
  .Cells(1, 4) = "Find Primes <= "
  .Cells(1, 5) = iend
  .Cells(2, 4) = "estr ="
  .Cells(2, 5) = estr
  .Cells(3, 4) = "esti ="
  .Cells(3, 5) = esti
  .Cells(4, 4) = "sr ="
  .Cells(4, 5) = sr
  PrX(2) = True
  PrX(3) = True
Binomial Distribution
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac Newton by several years in publishing his work. Actually, it was Jacob's brother Johann Bernoulli (1667-1748), an academic who made sense of Leibniz's notes and was primarily responsible for getting Leibniz's "Calculus" published. In fact, it was Johann that coined the term "Integral" (Leibniz called it sums), and may have even been responsible for the calculus symbology. If the name Bernoulli sounds familiar and you're an engineer, it's probably Johann's son Daniel Bernoulli you recall.

For over 300 years, mathematicians and statisticians have used the Binomial Distribution to group and model real world observations and is the basis of many other statistical distributions.

Here are my spreadsheets if you want to take a look at them:
How to use the IF function in Excel
Many functions in Excel can make decisions. The most simple of these is the IF function: it returns a value depending on whether a condition you describe is true or false. Once you get the hang of using the IF function, you will find it easier to use Excel's many other decision-making functions.
When to use the IF function

Use the IF function when you want the value of a cell to depend on a condition. The condition is typically the value of another cell or of a value you input. If the condition you choose is a cell value, the cell can be on the same worksheet, a different worksheet, or even a different workbook.

Here's a simple example:
Let's say cell B3 displays a total sale amount. If that amount is greater than $100, you want cell B4 to show a discount rate of 10%. But if B3 is not over $100, you want the discount to be zero.

Here's the syntax of the IF function:
=IF (condition, value if true, value if false)

Note that in the function, there are three arguments. In Excel and many programming languages, the arguments a function gets are separated with commas.

So we write out the function as follows:

Translating Excel into English, this means:
Evaluate the statement that B3>100. Is this true or false?
If it's true, the cell gets a value of 10%.
If it's false, the cell gets a value of 0.

This formula goes inside cell B4. So depending on whether B3 is under or over 100, B4 will adjust to either 0 or 10%.

Now let's put this…
LVL 19

Administrative Comment

by:Kyle Santos
Congratulations.  Your video has been Accepted and is now published on Experts Exchange.  Feel free to share this video by selecting the social sharing icons to your left.
Using, Creating and Modifying Styles in Microsoft Excel
Excel styles will make formatting consistent and let you apply and change formatting faster. In this tutorial, you'll learn how to use Excel's built-in styles, how to modify styles, and how to create your own. You'll also learn how to use your custom styles in multiple workbooks.

To follow along with this tutorial, you can use your own files or download the files I use in the video:
When you want to format cells in Microsoft Excel, you can do it manually, by selecting fonts, font color and size, background colors and borders, or you can do the formatting quickly and automatically using styles. If you used styles in other programs, you’ll be familiar with the concept: a style is a mixture of formatting that you can apply over and over, like paint.

There are two advantages to using styles:

  • Speed. When you have a lot of cells to format, it’s faster to apply a style than to apply all the formatting features individually. And if you need to change a formatting feature of all the cells – like just the color or the font – changing the style definition will immediately update the cell formatting.
  • Consistency. When formatting a lot of cells, it’s easy to make a mistake and select a slightly different color or font size. But when you apply styles, the exact, same formatting gets applied every time.
  • Excel has built-in styles that you can use, and you can also modify them and create your own.
LVL 19

Administrative Comment

by:Kyle Santos
Congratulations, Bob!  Your video is Accepted and now published on Experts Exchange.
How to tell Microsoft Office that a word is NOT spelled correctly
This Experts Exchange video Micro Tutorial shows how to tell Microsoft Office that a word is NOT spelled correctly. Microsoft Office has a built-in, main dictionary that is shared by Office apps, including Excel, Outlook, PowerPoint, and Word. When an Office module, such as MS Word, gives us the red squiggly underline signifying a misspelled word, yet it really is spelled correctly (such as a proper noun), we're all familiar with how to add it to the custom dictionary, that is, simply right-click the word and select Add to Dictionary. But suppose we type in a word that does not get the red squiggly underline, that is, Word thinks it is spelled correctly, yet we do not like that spelling, and we want Word (and other Office apps) to flag it as a misspelling. This video explains a Microsoft supported technique for achieving that.

1. Locate the ExcludeDictionary file

Exit all Office apps.

Using Windows/File Explorer (or whatever file manager you prefer), navigate to this file:


Of course, <username> is your user name (in my video, it is Joe). The exact name of the file will vary depending on your language and Office version, but it will begin with ExcludeDictionary and have a LEX file extension.


2. Open the ExcludeDictionary file

Using Notepad (or whatever plain text editor you prefer), open the ExcludeDictionary file, which will be empty the first time you open it. If you do a File>Open, make sure that All Files is selected, since it is a LEX

Expert Comment

Thank you for the detail and writing it in an understandable way.

Based on your information, I played around with the various English files and my locale and language settings and saw it work as you suggested. I also found that it is safe to simply delete the files, and let Office recreate the appropriate one when it performs a spell check.
LVL 63

Author Comment

by:Joe Winograd, Fellow&MVE
You're welcome, Bill. I'm glad to hear that it works for you. And thanks to you for determining that it is safe to delete the LEX file(s) — that Office will re-create the appropriate one when it performs a spell check. I hadn't tried that — very good to know! Regards, Joe
Excel Error Handling Part 3 -- Run and Fix Bugs
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel.

Part 1 of this series discussed basic error handling code using VBA.

Part 2 went in depth on how the VBA  to copy values to blank cells works, and how to loop.

Although helpful, it is not necessary to watch parts 1 and 2 before this lesson.

This lesson runs code to see what it does and then breaks working code so we can explore errors.  We run and fix, debug, compile, use and not use Option Explicit, step through code while it is running, look at the watch window to see values of variables, set and clear breakpoints, stop, continue running, and learn how debugging and error handling work.

01. For a list of macros, press Alt-F8

   When you are in an Excel Workbook, press Alt-F8 for a list of Macros.

02. To go to VBA, press Alt-F11

   When you are in an Excel Workbook, press Alt-F11 to go to the Visual Basic Editor (VBE) where you can write Visual Basic for Applications (VBA).

03. To watch variable values, press Ctrl-W

   When you are in VBA code, press Ctrl-W to open the Watch window and set expressions to watch the value of.  If a variable name is highlighted when Ctrl-W is pressed, it will be filled in the Expression.

04. Stop

   Add a Stop statement to the code to cause the code to stop on that line when it runs.

05. To single-step, press F8


Expert Comment

by:Erika Anderson
Thank you!
Fix Code to fill Missing data in Excel. Loop through spreadsheet in VBA.
This Experts Exchange lesson shows how to use VBA to loop through rows in Excel.  In order to sort, filter, and use database features, there needs to be a value in each column for every row. When data arrives with values missing, code to copy values where it is blank can be run.

This lesson shows the manual process to fill blanks, and the VBA code to implement it.

This is Part 2 in a 3-part series to discuss error handling in Excel.

Part 3 of this series will add errors to the code and then show how to fix them.  Part 1 of this Experts Exchange series suggested basic error handling code.

01. Set up the error handler

   At the top of the code for your procedure, the error handler is set up using     On Error GoTo Proc_Err

02. Dimension Variables

   Declare variable names and data types that will be used in this procedure.

03. Initialize Variables

   Initialize the values of variables that will be used in this procedure.

04. Calculate Variables

   Calculate the values of variables that be determined.

05. Give User a Chance to Back Out

   Issue a message box to the user and allow them to stop the process.

06. Determine the Last Row

   Determine the last row on the worksheet that needs to be written to.

07. Loop through each Row of Data and Save or Write Value

   Loop through each row and either save the value that is there, or write the last value saved if a value is needed.

08. Continue Looping until Done

   Continue looping until all values in the specified column are written.

09. Exit Code

   After the procedure code, a line label for the exit code (Proc_Exit: ) is used to signify what happens at the end of the procedure. This is code to gracefully exit.

10. Error Handling Code

LVL 24
This is Part 2 in a 3-part series to discuss error handling in Excel.  It can be watched independently of the other videos.  This presentation focuses on automating a manual process to copy values from one row to the next in a column of an Excel spreadsheet.

You will learn how to loop through rows using VBA.  This example copies values to blank cells, but you could do something else in your loop.

Part 3 of this series will add errors to the code and then show how to fix them.

Part 1 of this series reviewed basic error handling code.

Attached is the download file for this lesson, and the next: Books_ErrorHandling_02_Code2_CopyDownBlanks.xlsm
Exploring SharePoint 2016
LVL 12
Exploring SharePoint 2016

Explore SharePoint 2016, the web-based, collaborative platform that integrates with Microsoft Office to provide intranets, secure document management, and collaboration so you can develop your online and offline capabilities.

Basic Error Handling code for VBA and Microsoft Office
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code.

This lesson, Part 1, is the basics.  Whether you are writing VBA for Excel, Access, Word, or another Microsoft Office application,  basic error handling is the same.

01. Set up the error handler

   At the top of the code for your procedure, the error handler is set up using     On Error GoTo Proc_Err

02. Exit Code

   After whatever your procedure does, a line label for the exit code (such as Proc_Exit: ) is used to signify what happens at the end of the procedure. This can be code to cleanup object variables, or simply code to gracefully exit.

03. Error Handling Code

   After the exit code, a line label for the error handling code (such as Proc_Err: ) is used to begin what happens if there is an error.

Expert Comment

by:chris pike
For someone who is trying to wrap their brain around VB for the first time, this video is starting to shed light on the subject.
Well done video, very helpful.

Thanks so much.
I will definitely look out for more videos from crystal (strive4peace).
LVL 24
thank you, Chris and you're welcome  ~ if you have any questions about basic error handling, please post them here.
How to install the Office 2016 desktop applications that come with the free trial of Office 365 Home
In a previous video Micro Tutorial here at Experts Exchange, I explained how to get a free, one-month trial of Office 365, which provides the desktop versions of Office 2016. For Windows, this includes Access 2016, Excel 2016, OneNote 2016, Outlook 2016, PowerPoint 2016, Publisher 2016, and Word 2016, as well as Microsoft OneDrive. The previous tutorial ended at the point of downloading the installer for the Office 2016 desktop modules for Windows. This new tutorial goes through the installation process for those applications.

1. Run the downloaded installer

Using Windows/File Explorer (or whatever file manager you prefer), locate the downloaded installer for the Office 2016 apps that are included as part of the Office 365 Home subscription. The name may vary depending on your operating system, but it will look something like this:

Setup.<lots of other characters here>.exe

Run it (usually, via a double-click, but that depends on your file manager and settings) and then click the "Run" button on the "Security Warning" dialog.


2. Accept the User Account Control dialog

Depending on your User Account Control (UAC) settings, you may or may not get the UAC dialog. If you do, click the "Yes" button.


3. Wait until all Office 2016 apps are installed

Although it says, "We'll be done in just a moment", grab a cup of coffee.


4. Check for the Office Tools shortcuts

Check to make sure that the installer created a "Microsoft Office 2016 Tools" program group, with two shortcuts in it.


5. Check for the Office shortcuts

Check to make sure that the installer created shortcuts for all of the Office 2016 apps. It does not
How to get a free trial of Office 365 with the Office 2016 desktop applications
Office 365 is currently available in five editions. Three of them are for business use: Office 365 Business Essentials, Office 365 Business, and Office 365 Business Premium. Two of them are for home/personal use: Office 365 Home and Office 365 Personal. However, only one of them offers a free trial — Office 365 Home. This Experts Exchange video Micro Tutorial explains how to go through the process of obtaining the free, one-month trial for Office 365 Home, which includes the desktop versions of Office 2016. For Windows, this includes Access 2016, Excel 2016, OneNote 2016, Outlook 2016, PowerPoint 2016, Publisher 2016, and Word 2016, as well as Microsoft OneDrive. In a subsequent EE video Micro Tutorial, I show how to install the downloaded desktop versions of those Office 2016 modules in a Windows 7 system.

1. Visit the website for Office 365 Home

Visit the site with the only Office 365 edition that currently offers a free trial:


2. Request free trial

Click the "Try for free" button.

Step2aClick the "Try 1-month free" button.


3. Sign into your Microsoft account

Enter your email or phone for your Microsoft account, your password, and click the "Sign in" button.


4. Go through the payment process

Even though it is a free trial, you must provide a payment method and go through the payment process. So be prepared with a credit/debit card or a bank account or PayPal. If you are unwilling to provide a payment method, you cannot get the free trial.


5. Go through the install process


Expert Comment

by:Yashwant Vishwakarma
Thank You for sharing Joe :)
LVL 63

Author Comment

by:Joe Winograd, Fellow&MVE
You're welcome, Yashwant. I'm glad you like it! Regards, Joe
Removing Duplicate Values in a Column in Excel
This Micro Tutorial explains how to quickly distill a column of data down to a reduplicated list of unique values, also if the duplicated cells are completely blanks.

Expert Comment

I think you meant "deduplicated list of unique values" not "reduplicated list of unique values".
Creating Interactive Pivot Charts in Excel
This Micro Tutorial will demonstrate how to create pivot charts out of a data set. I also added a drop-down menu which allows to choose from different categories in the data set and the chart will automatically update.
Creating an Interactive Chart for Marketers in Excel
This Micro Tutorial demonstrates in Microsoft Excel how to consolidate your marketing data by creating an interactive charts using form controls. This creates cool drop-downs for viewers of your chart to choose from.

Expert Comment

Got an error message partway through the video.  "The video playback was aborted due to a corruption problem or because the video used features your browser did not support.
Creating a Scrolling Table in Excel
This Micro Tutorial will demonstrate how to use a scrolling table in Microsoft Excel using the INDEX function.

Expert Comment

by:Stephen Byrom
I will use this a LOT

Expert Comment

by:Omar Hernandez
This just gave me an idea, thanks.
VLOOKUP Tips and Tricks for Marketers
This Micro Tutorial will demonstrate how marketers working with data will be able to use VLookups because it is the best technique to handle data sets.
Three Marketing Examples of the OFFSET Function in Excel
This Micro Tutorial will demonstrate three uses of OFFSET function for marketing data. This includes dynamic validation, creating named ranges for dynamic charts, and formatting Google Analytic data.
Rowby Goren Makes an Impact on Screen and Online
LVL 12
Rowby Goren Makes an Impact on Screen and Online

Learn about longtime user Rowby Goren and his great contributions to the site. We explore his method for posing questions that are likely to yield a solution, and take a look at how his career transformed from a Hollywood writer to a website entrepreneur.

The INDEX Function: Excel's More Stable Alternative to OFFSET
This Micro Tutorial will demonstrate three marketing uses for the INDEX function.
Creating a Pivot Table in Excel
This Micro Tutorial will demonstrate how pivot tables are created and explain the how to use them for critical data-sets that are large and require flexibility. I will provide with a raw data set to a high customized pivot table and show many exciting options as I go.
INDEX MATCH Alternative to VLOOKUP Functions
This Micro Tutorial demonstrates how and when in alternative to VLOOKUP, use INDEX MATCH.
Using the SUMIF & SUMIFS Functions to Build Dynamic Dashboards in Excel
This Micro Tutiorial will demonstrate how to build dynamic dashboards in Microsoft Excel..
Using Advanced Filters in Excel
This Micro Tutorial will demonstrate how to use advanced filters in Microsoft Excel. These filters are commonly used in real-world client projects for a reporting dashboard.

Expert Comment

The hint of using the apostrophe instead of the = sign at time ~5:00 saved my hide.  I needed exact matches so ME didn't give me also MET in a criteria table (actually an Excel Table).  When I tried   ="=ME", the Table of criteria I was making activated the Totals row.  Chaos ensued.  Using the apostrophe did not have that problem.  So, thank you very much for the hint.  Note that I was creating the Table using VBA.   I can now move on in my project.
Updating an Entire Dashboard with a Radio Button in Excel
This Micro Tutorial will demonstrate how to create an interactive dashboard in Microsoft Excel. The entire dashboard is controlled by a set of radio buttons. Every chart in the dashboard updates show the segment of data as you select a new option.

Expert Comment

by:Vishal Bharakhada
Hi Annie,

This is awesome. Thank so much for sharing this video.

Vishal G. B.

Expert Comment

by:Vishal Bharakhada
I have created this in excel 2016. All is working perfectly except color change. Whenever I apply to color for particular month it is getting applied to all month data.
Please suggest.
Vishal G. B.