Excel Macros for beginners: How to record and write VBA Macros in your Excel 2019 workbooks?

Here’s a reader question:

I have a coworker that is using Excel VBA Macros to analyze a bunch of data. Can you explain how Macros work, and probably suggest a way for me to pick that skill up? Any simple tips for beginners are very appreciated.

Thanks for the question.

Excel Macros explained

Mastering Macro development is probably one of the most important tasks in order to become a power Excel user. In essence, we use Macros in order to automate tasks in Excel (or any other Office application) that would have been otherwise mundane, boring and very time consuming.

Here are a few examples of tasks we could automate with Macros in Excel

1. Creating and manipulating workbooks and worksheets.
2. Creating and manipulating charts
3. Import data from files or other systems.
4. Analyze data and create/refresh charts and dashboards
5. Customize forms to capture feedback
6. Send emails
7. And much more…

Due to the fact that Excel ships with a built in Macro recorder (unlike Outlook and PowerPoint), there are two main options for adding a macro to an Excel workbook:

1) Recording a sequence of user actions which si probably easy to get started with, but doesn’t scale up for complex tasks which might involve interaction with another Office applications or manipulating bigger data subsets.

2) Writing your Macros by leveraging Visual Basic for Applications (VBA). This is a relatively simple procedural language that allows you to quickly develop more complex automation.

In this post, we’ll be covering both cases. We’ll start by showing you how to record your macro and continue by providing a detailed procedure for inserting VBA macros to spreadsheets.

We’ll demo both options using a very simple task: We’ll automate the addition a worksheet name “DEMO” to an existing workbook and copying a specific table from Sheet1 to it.

Recording Excel macros

We’ll use a very simple example to outline the process.  Please proceed as following:

• In Excel, open an existing workbook or create a brand new one.
• Navigate to the View tab in the upper Ribbon.
• In the Right hand side, you’ll notice the Macro section.
• Hit View and select Record Macro.
• The form below will open up. Now provide a name to your Macro and store its content into the existing workbook. You might notice that here you can easily assign a key shortcut so you can more easily run the Macro.

Tip: Make sure to enter a detailed description to your Macro.

• Now, hit OK.
• At this point, go ahead and manually perform the steps you would like to record – in our case i will go ahead and manually create the new spreadsheet, rename it and then copy the table from Sheet1.
• Once done, hit Macros again and select Stop Recording.
• If you now hit Macros and then select View Macros, you’ll see a list of all Macros stored in all open workbooks (or specific ones)

• If you hit the Edit button, the Microsoft VBA Editor will open.
• The VBA editor shows the code that Excel has just generated for you in the background. In the next section of this tutorial i will show you how you can write a similar piece of code on your own.

• Close the VBA Editor.

Saving your Macro enabled file

Now we’ll need to save the workbook. As it contains a Macro, we should save it as a XSLM file (Macro Enabled Excel File)

• Hit File, then Save
• Select a location in your Computer or OneDrive in which you’ll save your file.
• Provide it a descriptive name
• Select .xslm as the File Type.
• Hit OK.
• Nice job, you have just recorded a simple macro!

Create your VBA Macro in Excel

As we previously mentioned, in many instances the Excel Macro recorder will fall short and won’t meet all your needs. In these cases, you’ll need to write your own Code.

Tip: There are cases that you might want to use an hybrid approach to Macro creation. You could start by recording your Macro and then just make manual adjustments to the auto-generated code with VBA.

Setting up the Excel development environment

This one sounds pretty fancy, but is actually quite simple. If you don’t see a tab named Developer in your Ribbon, start by enabling the Developer tab.

After the Developer tab is made visible, your Ribbon will look as following:

Writing your VBA macro

• Open your Excel file.
• From the Ribbon, hit Developer.
• Now hit the Visual Basic button  (Alternatively, hit Alt+F11).
• GO to Module1 and paste the following code:

Sub Write_Macro()
'This code creates a Macro named Write_Macro
Dim MySheet As New Worksheet
Dim MyTable As Range
' Create a worksheet programatically and call it TEST
Set MySheet = ThisWorkbook.Sheets.Add(After:=Sheets(Sheets.Count))
MySheet.Name = "TEST"
'Copy the table from Sheet1 into TEST worksheet
Set MyTable = Sheet1.Range("A1:C11")
With MyTable
.Copy Sheets("TEST").Range("A1")
End With
End Sub

• Close your VBA Editor.
• Back in Excel, hit Developer.
• Now hit on Macro and look into the list of Macros in your Workbook. You’ll notice the Write_Macro entry, which we just write on our own.

• Save your spreadsheet as a .xslm (Macro enabled Excel file)

Running your Macro

Executing your macro is very simple: From the Macro Dialog shown above, highlight your Write_Macro entry and hit Run.

Enabling VBA security permissions

In Microsoft Office Macros come by default disabled, in order to avoid running harmful code. If you would like to run your Macros you’ll need to allow that. Execute this optional step in case you have received an error message when running either of the two macros we have created above.

• Hit Developer in the Ribbon.
• Select Macro security.
• Select the Disable all Macros with notification option.
• Hit OK.

Wrapping up

In today’s short tutorial we learnt how to insert Macros either by recording them or writing them by using Visual Basic for Applications directly.

This is just a starting step for beginners to get familiar with Excel development. Feel free to post any questions and comments in the Comments section.

How to display the developer tab in Outlook, Word and Excel 2019/365?

Last updated: February 2019

Applicable to: Office 365, 2019, 2016, 2013; Windows 7 & 10 and MAC OS X operating systems.

Here’s a question we got from a reader:

I am trying to figure out macro development from going through some simple examples written in VBA. I figured out that some of the examples point out to the developer menu in Microsoft Excel. For some reason, i don’t see that menu item in the Excel ribbon. Am i missing out something? Is there any specific setting or configuration to make the Developer tab visible?

Thanks for the good question.

As we explained in the past, the simplest way to automate tasks in Microsoft Office applications (Excel, Word, PowerPoint, Outlook, Visio and Outlook) is using Macros. Excel, Word and Visio ship a built-in Macro recorder that allows you to capture a specific sequence of actions and re-use them later on.

Recording macros might be good for starters, but most probably you will be using Visual Basic for Applications (VBA) to write your own custom Macros.

On top of that, popular applications like Outlook and PowerPoint do not offer the macro recorder so you’ll need VBA chops if you want to automate a presentation or a mail message.

Adding the Developer menu to the Ribbon in Windows

The best way to access the VBA developer environment, is the so-called Developer tab. The tab is turned off by default in Microsoft Office, but you can learn how to set it up in a breeze.

Follow along as we setup the macro VBA development in Excel, Word, Outlook and other Office apps:

1. First off, open your Office application. In this walk through i have focused on Excel, but the process for the other apps is quite similar.
2. Place your mouse on the Ribbon and hit your mouse right click menu.
3. Then go ahead and hit Customize the Ribbon.

4. Next, go ahead and check the Developer entry in the right hand side column, as shown below.
5. You might at this point choose to show or hide the different sections of the Developer tab (Code, Addins, XML etc’). Leave those checked (visible) if you are a relatively newcomer into Macro development.

6. Once done hit OK.
7. Your development environment is pretty much ready. Time to get started with your coding

Showing Developer tab in MAC OSX

Microsoft Office for MAC OSX has ships tools for Macro recording and development.

If you are on MAC and would like to go ahead and access your development environment, proceed as following:

1. Open the relevant Microsoft Office application, be it Word, PowerPoint or Excel.
2. In the upper command bar, hit Tools
3. Then go ahead and select Macros
4. Now you’ll have couple options:
   1. Hit Macros… in order to access your presentation/workbook or document macros.
   2. Hit Development, to access the VBA editor

Show development environment on MAC

Note: Currently (Office 365), Outlook for MAC OS X still doesn’t support Macro development.

How to open an Excel 97/2003 .xls file in Excel 2016, 2019 and 365?

Last updated: February 2019

Applicable to: Office 365, 2019, 2016, 2013, 2007. Windows 7 and 10 operating systems.

Skyler set us a note about her excel spreadsheet not opening:

“I work as an analyst and have a significant number of old xls files were created in the previous version of Excel. Now that i have upgraded to the new version, Excel 365 won’t open the existing files telling me that they are corrupted and can’t be opened. Is there any possible way to associate or convert Excel 2003 files to version 2016, so they’ll open up correctly? I have invested significant amount of work in those files and would hate starting from scratch :-(.

Many readers have asked us about how to troubleshoot compatibility issues between older Office versions and the existing version 2016. In this post we’ll discuss what to actually do if Excel is not opening your existing xls format files or won’t save changes to your existing spreadsheets into a new format. Although we have written the tutorial specifically for Excel, the procedure below is fully applicable to other relevant Microsoft Office applications such as Word, PowerPoint and Visio.

Set your Trust Center settings

• Open Microsoft Excel 2016, hit File and select Options.
•  In the dialog box select Trust Center  and hit on Trust center settings.

•  Select Protected view on the left, uncheck all the options under Protected View and select OK.

•  Now restart Excel 2016 and try to open Excel document.

Convert xls files to 2016/2019

Compatibility Mode allows to convert Office files so they become usable in later versions.

Two caveats with using Compatibility on your existing xls workbooks:

• It changes the layout of your document or spreadsheet.
• It is not reversible – once the file is converted to the new format; in this case – .xlsx; you won’t be able to revert the file to the .xls version. Therefore our recommendation is to backup the file by simply creating a copy of it before you go ahead and follow the steps below:

Please proceed as following to convert your workbook to version 2016:

1. Open your existing spreadsheet
2. Take a backup of the existing spreadsheet by saving it with a different name. You might want to add the suffix before_converting to the file name (File>>Save As>>Set the new file name with the suffix and select the current file type)
3. Now, hit File
4. In the Compatibility Mode section, Hit Convert
5. If prompted, hit OK
6. Your old spreadsheet will go through the conversion process and modified to version 2016/2013.

Set Excel’s default format to .xls

This should probably be your last resort, but if the steps below didn’t help, you might want to try setting the Excel 97-2003 workbook format to be the default in Office 2016-2019. Proceed as following:

• Open Excel 2016, click on File and select Options.
• Click on Save on the left and in the Save files in this format drop down box, select Excel 97-2003 workbook.

• Hit OK.
• Now the default save format option for Excel documents will be as a xls file.
• Voi’la you are able to open older Excel versions in Excel 2016.

Suggested next steps

• Got a related question ? See our quick guide on how to get your Excel questions answered.

How to automatically highlight alternate rows or columns in Excel 2016?

A reader was asking about whether we know of a method or formula to highlight every other row and column in Excel. Today, we’ll learn how to apply different color schemes to alternate rows or columns in Excel. There are several ways to alternate row colors, but today, we will focus on two main ways to do that: using conditional formats and using table designs.

Using conditional formatting:

• At first we need to select the rows that should be shaded using conditional formatting.

• Now go to the home ribbon, click on the drop down box of conditional formatting and select new rule.

• In select a rule type, hit use a formula to determine which cells to format.
• In the format values where formula is true, enter the formula =mod(row(),2)=0.

• Then click on the format, go to fill and set a background color (I assigned it to green).

• The formula describes when modulus of row (1 to n) divided by two = zero fill the background color green.
• Modulus is the remainder value in a division.
• So from the selected rows 2,4,6 will have the background color green.

• Thus alternate rows shading with conditional formatting can be done.

Fill alternates using Using Table style:

• We can also use table style format instead of using condition formatting.
• To do so, select the cells that has to be formatted and click on Format as table in the home ribbon and select anyone of the needed table format.

• If u already have a header in the selected cells, check on the My table has headers.

• In the table style options, we can check or uncheck on the options provided.

• Filter buttons will provide you with drop down symbol.
• First column will make the fonts bold in the first column.
• Last column will make the fonts bold in the last column.
• Banded columns will make alternate columns shaded.
• Headed Row will make the headed hide or visible.
• Total Row will calculate the total number of rows in the spreadsheet.
• Banded Rows will make alternate rows shaded.

How to make Office 2016 or 2019 or 365 your default Microsoft Office application?

Last updated: February 2019

Applicable to the following Office versions: 2019,2016,2013, 365; Windows 7, Windows 10.

Here’s a question we received from one of our readers:

“I’ve installed a new version of Office 365 in my system, but after the installation all my Office documents are showing up as unknown file types. Can you help me to set Office 365  as the default file opener for Word, Excel, PowerPoint and Visio files?”

Thanks for the question. In this post we will learn how to set your new Office version (being it 2016 / 365 or 2019) as default for the Microsoft Office files types mentioned above.

Making Office 2016/2019 the default program  instead of older versions

Windows allows us to associate the newer Office version to all relevant files by executing a Quick Repair action from the Control panel. Steps are described below:

• Open the Control Panel in your system and click Programs and Features.

• Search and check on Microsoft Office 2016.
• Click on it and hit Change present at the top.

• Check on Quick repair to fix the issues and select Repair.
• Mostly repairing will fix you with the issues, but if still the issue is not cleared try the manual process.

Set your default programs/apps manually

Setting Excel 2016 / 365 as default spreadsheet program:

• Right click on any .xlsx file in your computer, then go ahead and select Open with and select Choose another app.

• Click on Excel 2016 and check on Always use this app to open .xlsx files.

• Now you can see that all .xlsx files default program is set to Excel 2016.

Note: Using a similar procedure, will allow you to make Excel the default program for csv, Google Sheets, Open Office.

Word 2016 / 365

If Microsoft Word 2016 is not your default program for opening documents proceed as following:

• Right click on the .docx file, move over open with and select Choose another app.

• Click on Word 2016 and check on Always use this app to open .docx files.

• Now you can see that all .docx files default program is set to Word 2016.

PowerPoint 2016:

If Powerpoint 2016 doesn’t open your presentations, proceed as following:

• Right click on the .pptx file, move over open with and select Choose another app.

• Click on PowerPoint 2016 and check on Always use this app to open .pptx files.

• You are all set.

Setting defaults in Windows 10

Note: You can execute the steps above in Windows 10 using the Default apps screen. Here’s how you do it:

• Hit the Search Windows magnifying glass icon (located at the left hand side of your task bar).
• In the search box, type ‘Default’; and select Choose a default app…
• Then for each known/relevant file type, select the default opening app.

Additional notes:

• There might be cases, mainly around compatibility with older file formats, in which you might want to leaver the older Office versions (2010 and older) as default.
• There might be cases in which Word, Powerpoint or Excel 2016, will not be listed in the default program list. If that’s the case immediately after you have installed the new version, you might want to restart your computer and re-check. If there is still an issue, you might need to re-run the installation program.
• Compatibility issues might arise when trying to open Microsoft Office files in Open Office, Libre and Google Docs.

Next steps

• Still got an unanswered question? See our guide on how to get answers to your Microsoft Office questions.

Automatically format cells, dates, columns and rows in Excel 2016?

Here’s a question from Krithi:

“I know that Excel has some cool capabilities for setting automatic formats (such as colors/fonts sizes / height / styles) of individual columns, rows, cells specially when it relates to dates and times. Can you provide more information about auto-formatting in Excel spreadsheets”

Great question. In this post we will show you how to add the auto format menu  in the ribbon, working with auto format and format dates and times in Excel 2016.

Adding the auto format menu to the ribbon:

• Open your Excel 2016, right click on the ribbon and choose customize the ribbon.

• Add a new tab and create a new group in the main tabs option.

• In choose commands from drop down box, select All commands.

• Scroll through to the Auto format entry, select it and click Add.

Working with Auto format:

• Open the Excel document which has to be auto formatted.
• Select the cells that are needed to be formatted and click on Auto format from the toolbar or the ribbon.

• Now click on your needed format and hit OK.

• If needed we can customize the format by click on the options.

• Numbers are used to add the numbers with dollar symbol or with decimal value.
• Borders are those borders in between those cells.
• Font helps to use text in bold letters with increased size.
• Patters are the design patterns that can be used in the headings.
• Width/Height increases the selected cells width and the height.

Format date cells:

Format cells is the one where each cells can be formatted separately. Like a single cell number, alignment, font, border, fill, protection. Here’s an example to format date in a cell.

• Enter the date, right click on cell and click format cells.

• In number, select category as date and click on anyone of the date formats.

Suggested next steps

How to easily create checklists in Excel sheets and Word documents?

Applicable to: Word and Excel 2019, 2016, 2013, 365. Microsoft and Apple Office versions.

Latest update: September 2018

Chances are that from time to time you might want create forms using in Office in order to easily capture user feedback or document a standard operating process. In today’s tutorial, we’ll teach you pretty much everything you need to know in order to create and format tick box lists either as part of your Excel spreadsheets or Word documents. Once done you might want to use your checklist as a template. The tutorial below refers to Office 2016 applications, however it is relevant to 2019 and 2013 versions as well.

Enable the developer tab

Note: The developer tab allows you to access many useful controls that you’ll use when creating your form. You will need to enable the developer tab for each Office Application you’ll use.

• Open Microsoft Word or Excel, click on File and select Options.
• Open Customize Ribbon tab and select Main tabs option under Customize the Ribbon.

• Click on Developer option and press OK button.

• Once the Developer option is clicked it will be displayed as additional menu on top of the screen with several developer options.

Making Excel checklists

We would like to create the following task list:

• Write your tasks in column A as shown above.
• Under Developer option click Insert and then click check box  which is placed under form control .

• Select the required cell to place the check box.
• To remove Check box 1, click on text and delete it.
• Click on the right corner of the cell and drag down till the last item of the list.

• Right click the check box and click format control.

• Link the check box to the cell next to it. If C 2 then Click on Cell link under control and type $C$2.Repeat the same for all check boxes.

• To count the number of items marked insert a COUNTIF function next to the last item of the list.

• Hide cell C.
• Insert IF function at the end of the list.
• The final result looks :

Insert check boxes in Word

• The user has to create the check list and click on Check Box Content Control under developer option  . 

• The same process should be repeated for the list of items.

Suggested next steps

• Got a related question? Look into our guide on how to get your Excel questions answered.

How to open, view and compare two Excel worksheets side by side?

A pretty helpful function when working with spreadsheets is the ability to compare them side by side.In today’s tutorial  we’ll show you how to view and compare two or more worksheets in the same or different workbooks side by side.

View two worksheets in same workbook:

• Open your excel spreadsheet which consists of two workbooks.
• Now click on New Window and we can see that same workbook is opened twice.

• It allows us to work with different workbook at the same time.

• Then click on View side by side in the View ribbon, which allows us to view same workbook side by side.

• Click on different worksheet on the same workbook and compare with the other one.
• Go to View and select Synchronous scrolling will allow us to scroll on both sheets at the same time.

• Move the worksheet if needed and click on Reset window position to make sheet go back to it’s original position.

Open two worksheets from different workbooks:

• Open the two excel spreadsheets which has to be viewed and compared side by side.

• Then do the same process done for two worksheets on same workbook.
• Click on View side by side in the View ribbon, which allows us to view same workbook side by side.

• Click on different worksheet on the same workbook and compare with the other one.
• Go to View and Synchronous scrolling will allow us to scroll on both sheets at the same time.

• Move the worksheet if needed and click on Reset window position to make sheet go back to it’s original position.

Compare multiple worksheets:

• Open two or more worksheets of different workbook, click on Arrange on and select how it has to be arranged.
• If two or more worksheets on same workbook, then click on the new window accordingly to the no of worksheets.
• Then click on Arrange on and select how is has to be arranged.

Thus we can use the side by side view for workbooks. Thanks for reading

How to combine two or more cells or columns in Excel 2016?

Here’s a question from Nanda:

“I need to copy list of names available in spreadsheet to a text document. But when I copy and paste it, all goes wrong. Can you help to have first and last name in the same column?”

Sure. Read on for the details.

To merge columns using concatenation function:

• Open the spreadsheet to which your column has to be merged.
• Using an example we will discuss about using concatenation function.

• In this example the first name and the last name has to be merged in the full name column.
• In the C3 column type =CONCATENATE (A2, ” ” ,B2).

Here A2 is Vinod and B2 is Mvd. The ” “ represents a space between A2 and B2.

• Now we can see the merged name.
• After this just drag the green bottom pointer to the end of the names.

Concatenate using Auto fill options:

Using the auto fill functions we can easily merge names into one column. No need of using the formulas. Just by entering the column number we can merge the names in one cell. Read further for the procedure.

• Open your spreadsheet and place the cursor click on the blank space where the names has to be placed.
• Now, click on Home in the ribbon and select the drop down box of Auto Sum in the Editing menu.

• Select more functions, in the select category click All.

• Search for concatenate function and click OK.

• In the text 1, select the column by clicking on the icon in the text box.

• Similarly for other text columns and select OK to merge the columns.

That’s it. This is how we can merge names in same column.

Thanks for reading..

How to remove duplicated entries from an Excel 2016 list?

If you use Excel to analyze large data sets, you might have encountered the need to find and remove duplicate rows from your data. In today’s post, we’ll provide a simple tutorial to help you find those duplicates and scrub them from your data. Although Excel 2016 is used to outline the steps below, they are fully applicable to Excel 2013 as well.

To find the duplicate entries:

• Open the spreadsheet and select the range in which you need to find the duplicated entries.

• In your Home ribbon, select the drop down box of Conditional formatting.

• Then move your cursor over the Highlighted cells and hit Duplicated values.

• Now you can see that all the duplicated entries in the selected cells will be highlighted.

To manage Highlighting rules:

• In the Home ribbon, select the drop down box of conditional formatting and select Manage rules.

• Click on Edit rules in the selected formatting.

• Select the rule type accordingly to your need.

• Select Duplicate in the format all drop box to highlight the duplicate entries.
• Select Unique in the format all drop box to highlight the unique entries.

• Click on the format box to modify the font size, color, underling and effect and click OK in both dialog boxes.

To Remove the duplicated entries:

• Warning: please make a copy of your original spreadsheet before removing any data!
• Select the cells in which the duplicates has to be deleted.
• Now, select Data in the ribbon and select Remove duplicates in the data tools menu.

• Make sure the columns are selected and click on OK to remove the duplicate entries.

• Check on the columns which are needed to be check for duplicate entries.