ForLoops

How to Create For Loops in VBA

You can add lots of automation to your spreadsheet by utilizing visual basic (VBA) and running macros. One of the more common macros you might run include loops. In particular, using for loops in VBA can allow you to cycle through a range of data, check for criteria, and then execute commands. Or, you can also use it to do calculations and to compute totals. There’s a lot of potential with running loops in VBA. In this post, I’ll show you how you can get started with them.

Looping through data in Excel to highlight blank or incomplete data

In the following data set, I have some missing values where there is an (*) in place of a value in column D:

I’m going to loop through the column that shows the change value and I will look for the (*) values.

To get started, launch VBA from the developer tab or hit ALT+F11. Then, insert a new module. I’m going to call this subprocedure ‘cleanup’. I’ll start with declaring variables for the worksheet, individual cell I’m looping through, an integer to track how far along I am in the range, and an integer for the last cell within the range.

Sub cleanup()

Dim ws As Worksheet
Dim cl As Range
Dim lastcell As Integer
Dim i As Integer

Set ws = ActiveSheet
lastcell = ws.Range("A1").SpecialCells(xlCellTypeLastCell).Row




End Sub

In the above code, I’ve assigned the worksheet variable to the sheet that I’m on. And I set the lastcell variable to the last row in the sheet. This is the same as if you were press F5->Special->Last Cell. This makes it easy to determine where the data set ends, and gives me an endpoint for my loop. There are other types of loops you can use where you don’t need to specify an end. But this reduces the risk of you getting into a never-ending loop.

As for the loop itself, it will look like this:

For i = 1 To lastcell

    Set cl = ws.Range("D" & i)
    
        If cl = "(*)" Then
        
            cl.EntireRow.Interior.Color = vbRed
            
        
        End If
        

Next i

How it works is it uses the i variable to start from 1 and go all the way until the lastcell variable. In this example, that relates to 92 (the last row in my data set).

Then, it assigns the cl variable to each cell in column D as I go through that range. In the first instance, the cl variable is D1, then D2, and so on, until you reach the end (D92).

Next, it evaluates if the value of that cell is (*). If it is, then it highlights the entire row in the color red. If I run this macro, this is what my sheet looks like afterwards:

Loop in VBA that highlights rows in red.

It did the job well, as you’ll notice everything that had a value of (*) in column D, the entire row ended up getting highlighted in red. There are other colors you can use and other things you could have done. Next, I’ll show you how you can just delete these rows entirely.

Deleting rows while looping through data in Excel

Removing rows seems simple enough, but it is a little tricky here. For instance, to remove the row rather than to just highlight it, that involves a simple line of code (in my example I’m referring to my cl variable):

cl.EntireRow.Delete

If I run the macro with that code, this is what happens to my worksheet:

Looping through and deleting rows in Excel.

I’ve left the red highlighting in place to show you that there are still many rows that should have been deleted and that weren’t. So did the macro simply not work?

The problem lies with how it was set up. In the current macro, I’m moving down from one row to the next. If I’m at row 10 and delete it, then the next row I move on to is row 11. However, the issue is that once I delete row 10, everything moves up a spot. So what was previously row 11 now becomes row 10, causing me to effectively skip over that row and miss it. Now my macro no longer evaluates it.

There are a couple of ways to fix this. One can be that if I delete the row, I can adjust my i variable so that it deducts one so as not to skip over the next row. Just by adding a line of code, you can adjust for that issue:

For i = 1 To lastcell

    Set cl = ws.Range("D" & i)
    
        If cl = "(*)" Then
        
            cl.EntireRow.Delete
            i = i - 1
            
        
        End If
        

Next i

The i = i -1 line of code will reset the i variable back down a spot when a row is deleted. This will now prevent the macro from jumping over a row.

However, there’s another option you can use, and that’s looping through the data in the opposite direction.

Looping through the data backwards and utilizing the step keyword

In the first example, I looped through the data from row 1 to the last row. Here’s my full code for that:

Sub cleanup()

Dim ws As Worksheet
Dim cl As Range
Dim lastcell As Integer
Dim i As Integer

Set ws = ActiveSheet
lastcell = ws.Range("A1").SpecialCells(xlCellTypeLastCell).Row

For i = 1 To lastcell

    Set cl = ws.Range("D" & i)
    
        If cl = "(*)" Then
        
            cl.EntireRow.Delete
            
        
        End If
        

Next i



End Sub

I’m going to adjust this and go in reverse order. However, it’s not as simple as specifying start from the bottom number and go to row 1. You need to give VBA a bit more information. This is where you can use the Step keyword. By using that, you can specify in which direction you want the loop to go, and whether it should go 1 row at a time or jump multiple rows.

Here’s how the loop looks like if I want VBA to jump backwards one row at a time from the bottom:

For i = lastcell To 1 Step -1


    Set cl = ws.Range("D" & i)
    
        If cl = "(*)" Then
        
            cl.EntireRow.Delete
            

        End If
        

Next i

If you want to jump by 5 rows, you would use Step -5. However, because I want to evaluate each row, -1 is what I’ll use in this example. By running this loop, now the highlighted rows are all deleted:

Looping through and deleting rows in Excel.

Since the loop is starting from the bottom and working its way up, it doesn’t matter that I’m deleting rows; it doesn’t impact the rows above and so no adjustment to the i variable is necessary.


If you like this post on How to Create For Loops in VBA, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

H2EAnimateDashboards

Animate Your Dashboards in Excel With This Free Template

Dashboards in Excel can update when a user makes a selection on a slicer or refreshes data. You can even use macros to automatically update a chart or dashboard for you. In this post, I’ll share with you a template that I’ve created that will allow you to effectively play your dashboard, updating it from one period to the next, and showing the change in the chart over time. Here it is in action:

The template has three sections: one for pivot tables, one for the data, and one for the dashboard. You can set the file up however you want, the main area that needs to remain largely the same is the dashboard sheet. Every chart on this sheet only will automatically get updated. And for it to work properly, all the charts need to be linked to the one timeline chart in here (i.e. there cannot be more than one). For information on how to set up your timeline (or any other slicer for that matter) so that you can link it to multiple charts, you’ll need to learn about how to adjust Report Connections in this post.

Once you’ve got the charts you want to be connected to the timeline, then it’s a matter of just updating the settings section on the Dashboard tab. This is off to the left, with the values that you need to enter/update highlighted in yellow.

Settings section on the template.

These simply specify what date you want to start from, where you want to end at, and by which interval you want to jump (e.g. x days/months/years). Depending on the frequency you select, your dashboard can either play very quickly, or very slowly.

The last step is to just click the Animate Dashboard button at the end of the home tab:

Animated dashboard button.

Upon clicking this, the timeline will jump by the intervals you specified. No other changes will be made to any filters or slicers you have selected. The only changes will take place to the timeline, at which point, you should see something similar to the video posted at the top of this post.

You can download the free Animate Dashboard template for free, from here.


If you liked this free template that helps you animate your dashboards, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

H2Etogglebutton

How to Create a Toggle Button on the Excel Ribbon

In a previous post, I went over how to modify the Excel ribbon with some basic buttons. This time, I’m going to focus specifically on a toggle button that can change the value of a cell or trigger a calculation or macro. In this example, I’ll show you how you can create a toggle button on the ribbon that will change the color of the sheet, switching between having no fill and a white fill.

The first thing you’ll want to do is download the CustomUI Editor, which you should be able to find online through a search. Once you have that, you can get to work on coding the xml.

Creating the ribbon code

To get started, you can refer to this post on how to create a simple tab and button on the Excel ribbon. Once you have that basic framework set up in the CustomUI Editor, it’s just a matter of modifying the type of button you’ll use. In that post, I just inserted some generic code for a button:

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
	<ribbon startFromScratch="false">
		<tabs>
			<tab id="customTab" label="Custom Tab">
				<group id="customGroup" label="Custom Group">
					<button id="customButton" label="Custom Button" imageMso="HappyFace" size="large" onAction="Callback" />
				</group>
			</tab>
		</tabs>
	</ribbon>
</customUI>

The button in that example is just a basic button. To turn it into a toggle button, I just need to modify the syntax so rather than saying ‘button’ it says ‘toggleButton’. I still need a label, an image, and specify what happens when it is pressed (the ‘onAction’ argument). Here’s what my updated xml code looks like:

<customUI xmlns="http://schemas.microsoft.com/office/2009/07/customui">
	<ribbon startFromScratch="false">
		<tabs>
			<tab id="customTab" label="Custom Tab">
				<group id="customGroup" label="Custom Group">
					<toggleButton id="customButton" label="Custom Button" imageMso="HappyFace" size="large" onAction="Callback" />
				</group>
			</tab>
		</tabs>
	</ribbon>
</customUI>

Changing button to toggleButton just allows the button to have a pressed state so that when you click on it, it can look sunken. You can use that variable to also dictate what action should be taken. The key to making the toggle button work is to attach some macros to it, which is what I’ll cover next.

Creating the callback macros

A callback macro tells Excel what should happen when the toggle button is clicked. Here’s what the macro should look like initially:

Sub Callback(control As IRibbonControl, pressed As Boolean)

End Sub

The pressed value is a boolean, so it will only either be true or false. The next part of the code involves setting up an if statement to check on that value.

    If pressed = True Then

        'code goes here if the value is true (pressed)

    Else
    
        'code goes here if the value is false (not pressed)


    End If

In this example, I’m going to make a simple macro where the sheet is either filled white, or there is no fill effect. The code isn’t complicated and I will start will selecting all the cells, selecting the interior property, and setting it to solid (i.e. ‘filled’):

Cells.Interior.Pattern = xlSolid

And to change it back so there is no fill effect:

Cells.Interior.Pattern = xlNone

Within the full callback macro, this is how all the code looks:

Sub Callback(control As IRibbonControl, pressed As Boolean)

    If pressed = True Then
    
        Cells.Interior.Pattern = xlSolid

    Else
    
        Cells.Interior.Pattern = xlNone


    End If


End Sub

Here is the toggle button in action:


If you liked this post on How to Create a Toggle Button on the Excel Ribbon, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

H2EcustomUIEditor

How to Customize the Excel Ribbon Using the Custom UI Editor

In a previous post, I covered how you can customize the Excel ribbon in what was a pretty manual process. The good news is there is a much easier way to make changes to the ribbon using the Custom UI Editor. Below, I’ll cover how you can add and remove both tabs and groups, how to add buttons, and how to even use your own images. The first thing you’ll want to do is to download the Custom UI Editor. There’s not a definitive place you can always find this tool at so your best bet is to do a Google search.

How the Custom UI Editor works

Once you have downloaded the Custom UI Editor, you can get to work and begin to customize the Excel ribbon. Unlike just a simple customization, when you modify the ribbon using the Custom UI Editor, you are making changes to the Excel file itself. That means when you send the file to someone, they will see the changes you have made. With just a ribbon customization, those changes only apply to your computer. But these changes will be saved within the file. The Custom UI Editor goes through the cumbersome process of attaching the XML code to the Excel file and makes it a lot easier to make changes to the ribbon.

When you first open the file, you’ll see a blank canvas such as this:

Custom UI Editor showing a blank page.

Start with clicking on the Folder icon to load up the Excel file that you want to modify. Any Excel file will do. Next, go to the Insert menu and click on Office 2010 Custom UI Part:

Insert menu on the Custom UI Editor.

This will create the xml file for Office 2010 and newer versions of the ribbon. If you want to ensure these ribbon modifications also work on Office 2007, then you will want to also insert the file for Office 2007 Custom UI Part. If the file isn’t going to be used on an older version of Excel, this isn’t necessary, but it also doesn’t require much additional effort.

The Custom UI Editor includes some sample code within it that you can automatically load so that you don’t have to start from scratch. With one of the xml files selected, let’s go back to the Insert menu, and this time click on Sample XML and Custom Tab.

Inserting sample XML from the Custom UI Editor.

This will insert some xml to get you started:

The Custom Tab XML code is loaded into the Custom UI Editor.

What the code has done is created a tab called Custom Tab and within that, created a Custom Group. Lastly, it has also added a button called Custom Button, which is a large size and uses a HappyFace icon that is built-in within Excel. Here’s what that looks like in the actual Excel file:

Custom button using the Happy Face icon.

The one thing that’s left to do is to link the button to some VBA code so that it does something when you click on it. I’ll cover that towards the end of this post.

If you wanted to run this same xml code for the older version of Excel (2007), then everything would work the same except for the very first line. Instead of this:

<customUI xmlns=”http://schemas.microsoft.com/office/2009/07/customui”>

You would use this:

<customUI xmlns=”http://schemas.microsoft.com/office/2006/01/customui”>

All you need to do is change 2009/07 to 2006/01.

To check that your code is correct, you can click on the red checkmark icon at the top:

Red checkmark button in the Custom UI Editor.

And if you get this message:

Custom UI message saying the XML is well formed.

Then your code is good to go and doesn’t contain any (obvious) errors.

Adding a custom button to the Home tab

Using the above example, you can customize the Excel ribbon to create a group and custom buttons inside of a new tab. However, I prefer simply adding any custom buttons on the Home tab to make them easy to find. Unless you have many buttons and macros, you probably don’t need to put them on an entirely separate tab of their own.

If you don’t want to create a new tab and just want to put your buttons in an existing tab, then you can use the following code to put them on the Home tab:

<tab idMso=”TabHome”>

When you use a reference of ‘Mso’ that means it is an existing Microsoft tab/group/image. You need to refer to the correct name (see further down for a list of groups and tabs) and then you can put your custom group or button in that tab rather than creating a new one. Here’s what the full code would look like by changing this one reference from the above example:

Custom UI Editor showing button group created within the Home tab.

In Excel, I still have my custom group, but now it isn’t on its own tab. Instead, it goes to the end of the tab:

Custom group showing at the end of the Home tab.

If it’s too far to the end what you can do is insert it before a certain group. Let’s say I want to put it just before the Alignment group. Then I just need to adjust the code slightly to add the insertBeforeMso (this is case-sensitive) attribute for the group tag:

<group id=”customGroup” insertBeforeMso=”GroupAlignmentExcel” label=”Custom Group”>

This is how the full code looks:

Custom UI Editor with custom group inserted before the Alignment group.

And now my custom tab shows up a lot earlier in the home tab:

Custom group and button showing on the home tab after the Font group.

Another thing I can do is also remove some groups. If I don’t want the font group, I can add the following line of code in the Custom UI Editor:

<group idMso=”GroupFont” visible=”false”/>

Here is the updated code:

Custom UI Editor that shows line removing the Font group.

And here’s what the ribbon looks like:

Excel ribbon without the Font group.

You could make all the Microsoft tabs and groups invisible if you wanted to and can control where you custom group goes. The key is knowing the correct names.

Names of the Microsoft tabs and groups

Here is a list of the Microsoft tabs and the reference you will want to use when modifying the ribbon:

Tab NameRibbonX Referenece
HomeTabHome
InsertTabInsert
DrawTabDrawInk
Page LayoutTabPageLayoutExcel
FormulasTabFormulas
DataTabData
ReviewTabReview
ViewTabView
DeveloperTabDeveloper

Here are the main groups from the Home tab:

Group NameRibbonX Reference
FontGroupFont
AlignmentGroupAlignmentExcel
NumberGroupNumber
StylesGroupStyles
CellsGroupCells
EditingGroupEditingExcel
ClipboardGroupClipboard
UndoGroupUndo

There are more groups (from other tabs) but for this purpose, I just included the most common ones.

Adding an image to a button

If you want to use an existing Microsoft image for your button, then you can view the imageMso gallery here. Once you find the image you want to use, just put that in place of the HappyFace image in the earlier code.

However, suppose you want to make a custom image. I’m going to create one using the Amazon logo to create a button that will open my browser to the Amazon.com website.

For starters, I need to get an image. For large ribbon buttons, you want to aim for a size of 32 x 32 and for smaller images, 16 x 16. As long as it’s a square image, however, you should be okay. Wide images will stretch and won’t look as good. This is the image I’m going to use:

Amazon logo.

I’m going to use just a simple code for creating the button, which looks as follows:

Custom UI Editor showing code for the Amazon button.

I haven’t associated an image to this button yet. To do that, I’m going to click on the xml file and go back to the Insert menu. This time, I’m going to select Icons. This will open up launch a dialog box where I can now select the image I want to use from my computer. Once I’ve selected it, it now shows up underneath my xml file:

Image file showing attached to the xml file.

I can right-click on the name ‘amazon’ to change the id to something else. Whatever if it is, that’s what I need to reference in my xml code. Since it’s not a Microsoft image, I just add the following attribute:

image=”amazon”

And here is my full code:

Custom UI Editor showing the code referencing the custom image.

If I open up Excel, this is what my custom button looks like:

Custom Amazon button on the ribbon.

But right now, my button doesn’t do anything. That leads us to the last section on how to customize the Excel ribbon: callbacks.

Setting up callback macros

A callback tells the button which code to run. So that means you need some VBA code to begin with, otherwise, the button isn’t going to do anything. I’m going to create a simple macro that will just open the Amazon.com website:

Sub Amazon()

ActiveWorkbook.FollowHyperlink (“https://www.amazon.com”)

End Sub

The callback function itself can be generated from the Custom UI Editor. If I click on the icon next to the checkmark that looks like a block of code:

Callback button on the Custom UI Editor.

The application will produce the VBA code I need to put into my Excel file:

Callback generated from the Custom UI Editor.

I’m going to copy and paste that back into VBA. However, I need to add a line in between as that code only sets up the macro, it doesn’t do anything yet. I need to reference the macro I created earlier. The full callback macro looks as follows:

‘Callback for btnAmazon onAction
Sub Callback(control As IRibbonControl)

Amazon

End Sub

Now when the button is pressed, the ‘Amazon’ macro will run, which opens the Amazon.com website. You can create a custom button for each macro you want to run and assign an image to each one. All you need to do is to use the callback macro to link the button to the code you want to run.


If you liked this post on How to Customize the Excel Ribbon Using the Custom UI Editor, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

H2Edatepicker9001

Free Excel Date Picker Add-In

Are you looking for an easy way to add a date to your Excel spreadsheet? You can download my free date picker add-in for Excel. It is useful if you have a form and you want people to select dates or if you just want an easy way to enter a date without worrying about whether it is in the right format.

***Please note on an earlier version of this add-in (and as reflected in the video), the calendar was designed to pop up next to the active cell. However, due to many issues related to scrolling and possible zooming, and multiple screens, it is now set to open at the top (and in the middle) of the screen***

How the date picker add-in works

To launch the add-in, click on CTRL+SHIFT+Z, which will trigger the following calendar to pop up:

Date picker add-in for Excel.

By default, it will jump to the current month. Clicking on any of the dates will enter the date value into the active cell. You can use the arrow keys on the left or right side to change the months. If you want to jump by years, double-click on the year and just enter the desired year. The calendar will automatically adjust, which will be quicker than if you were to just continue pressing the arrow buttons.

Right now the add-in is a stand-alone but look for it to be included as part of a larger add-in package. If you have any suggestions for other features to include in an add-in, feel free to contact us.

How to install an add-in

You can download the date picker add-in here. Once you’ve saved it, go into Excel and select File -> Options -> Add-ins and then depending on your version, you may see an option at the bottom to go to manager Excel Add-ins:

Manage Excel add-ins button.

Click on the Go button and then you will have a list of add-ins you can install. If you didn’t save the add-in into the default folder where the rest of the Excel add-ins are, you just click the Browse button to find where you saved the file. Then, make sure the add-in is checked off and click OK and it will be ready to go.


If you liked the Date Picker Add-in, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

H2EForm

How to Create a Form in Excel

In a previous post, I covered how to add checkboxes. Now, I’m going to go a step further and show you how you can create a form in Excel from start to finish. And at the bottom of the page, you can download a file that you can use for your own custom forms. It will incorporate, list boxes, checkboxes, validation rules, and allow you to move the data onto a separate sheet. For now, let’s start from scratch.

Step 1: Determine the data you want and how it should be entered

The first step in creating a form in Excel is determining what information you want to collect. In this example, I’m just going to include name, address, city, state, email, a checkbox to confirm if it is okay to contact the person, a rating, and an area for comments. It is also important to determine how users should enter these values. While it’s easy to leave everything as text, that can make it difficult to ensure someone doesn’t enter invalid data. And if the data is not useful, it will defeat the purpose of the form.

Here are the types of inputs I’m going to use for my fields:

  • Name: Text
  • Address: Text
  • City: Text
  • State: List box
  • Email: Text
  • Contact confirmation: Checkbox
  • Rating: Radio button
  • Comments: Text

Next, let’s work on the form’s design.

Step 2: Designing the form and creating the inputs

One thing I did to help make the form cleaner from the beginning was to turn off gridlines. You can do that by going to the View tab and unchecking Gridlines under the Show group:

Hiding gridlines in Excel.

This will make your form look more like a form and less like a regular Excel sheet. Another thing you can do is in that same section, unselect the Formula Bar and Headings, which will add more white space and are unnecessary if someone is just filling in a form. However, you may want to save this for the end when your form is done.

Since an Excel form can come in all shapes and sizes, the one thing that may help you in the design process is to set every column to a width of 2. This way, it will be easier to maneuver in case one field needs to be bigger than another without having to try and force everything to be a similar length.

As for the input fields, there are a few things you will want to do:

  • Make sure they are long enough. A good way to test this is by entering a long value, or what you might think will be the longest value into each field and then adjusting its length so that everything displays correctly.
  • Assign a named range. This is useful to keep things organized and it will make it easier for you to refer back to the field later on if you only have to remember its name, as opposed to its cell reference.

Now, let’s move on to creating the fields in the Excel form. What you can do for text entries is to just add some outlining and highlighting to existing cells. A subtle light grey can be a good way to indicate that is an input value. And I’ll also add a border to help make these fields stand out. If you set the column width to 2, you’ll also need to merge the cells as needed.

Creating text fields in Excel.

For the State field, I’ll go back to the Developer tab where I will select the option for a List Box from the Form Control section — which is next to the Radio Button on the right. When in doubt, you can hover over each control to see what it is.

Form controls in Excel.

After creating the List Box, I need to populate the list plus link to a cell where the selected value should go. I’ll start with creating a range of cells for all 50 states and then assign a named range for them called StateList.

Then, I will set up a named range called StateNumber for the linked cell. Here is what the List Box control shows when I go into Format Controls and select the Controls tab:

Format control properties for a list box.

But this is not enough as the list box returns a number, not the state’s initials. I will need another cell to pull that in. I created a named range for State and here is what my sheet looks like:

List box displayed on an Excel spreadsheet.

In the list box, I selected MT, which returns a value of 26 in the StateNumber range. To extract the state’s initials, I need to use a formula to get that. Since I’m getting the data from one column, I’m just going to use the INDEX function. Here is what the formula in the State named range looks like:

=INDEX(StateList,StateNumber,1)

It is looking at the StateList and pulling out the row that relates to the StateNumber. Since MT is the 26th selection, that is the value that gets returned. So now my List Box is working correctly. What I like to do with these named ranges is to hide them so that the user doesn’t see all these intermediate steps. All it takes is to just move the List Box over top of these cells:

List Box in Excel.

And just like that, the user only sees their selection and not the calculations afterwards. You could certainly use a drop-down list for states but I thought I would try something different and more user-friendly for this example.

Next, let’s go to the email field. This can be tricky because although you want this to be text, you also want to control what a user enters to avoid a possible error. You can’t guarantee the email will be correct but you can take steps to at least prevent some errors. The key here is going to be to create a data validation rule. There are two things that should be present in email addresses: the @ sign and a period. To create a data validation rule, select on the cell and click on Data Validation under the Data tab:

Selecting data validation rules in Excel.

There are many rules you can set up such as limiting the entry to fall within certain dates, making sure it is a whole number, or that it is from a drop-down list. But this situation is unique and will require a custom formula.

Creating a custom formula for data validation in Excel.

To check for both the period and the @ sign, I will need to use the FIND function and check that the value is a number (which means that it was found). Here’s how that looks inside of an AND function:

=AND(ISNUMBER(FIND(“.”,Email)),ISNUMBER(FIND(“@”,Email)))

Since I set the field to a named range of ‘Email’ it is easy to reference it without worrying about whether I have selected the right cell. If I put this calculation in the formula section, now you won’t be able to enter a value that doesn’t include both a period and an @ sign. In addition, you can also specify the error alert and determine what pops up if someone enters something different that violates these rules. However, that’s not necessary as they will get an error anyway.

Now, I’ll add the checkbox for the email. This again comes from Excel’s Developer tab and the Form Control section. Simply select the checkbox and set up a linked cell. If you want more details on this, refer to the link at the top of this post for a more detailed outline of how to add checkboxes. I have positioned the checkbox right below the email field:

Email fields set up in Excel.

Next, I’ll add some radio buttons to allow someone to leave a rating. These are useful if you want to specify a number. Here I will go back to the Developer tab and create some radio buttons and re-size them so they don’t take up much space. Unlike the other controls, you will want them to all have the same linked cell; the purpose of radio buttons is that there is only one selection. Here is how I added them, just below the numbers that they refer to:

Radio buttons in Excel.

The radio buttons will automatically increment on their own so if you don’t pay attention to what order you’ve added them in you may get some unexpected results.

Lastly, I will add a large comment box where people can leave detailed comments. This can just be a large merged cell that takes up more space.

But the one thing you will want to do is make sure that Wrap Text is selected so that the comment fits in the box. And you will probably want to align it so that it is in the top left corner of the cell:

Wrap text selected for a cell.

Then, when I enter the text it looks correct:

Comment box filled in with top-left alignment and wrap text enabled.

Here is what my completed form looks like:

Form in Microsoft Excel.

It looks good, but we are still not done. Something needs to happen with these inputs otherwise the information goes nowhere. Let’s go over that next.

Step 3: Storing the data from the form in an Excel sheet

If you are sending just a single form over for someone to enter data in, what you can do is create an output page that will link to these values. Since they are all named ranges, you can easily reference back to them as such:

=Name

In the above example, if you created a named range called Name for the first field, it will pull in the data from there. On an output sheet, you might have formulas and values that look like this:

In column B I am showing the formulas. You can keep this tab hidden if you want it out of sight. You can even go one step further and make them very hidden.

Not sure whether your fields should go horizontally or vertically? In most cases, you’ll actually want them going across the top. When in doubt, consider the number of fields you have versus how much data you will be entering into the sheet. If you will have dozens of results that you will need to populate (or more), you probably don’t want to be cycling through that many columns; rows are easier to scroll through and that’s why it will probably make more sense for the fields to go across the top.

Once you have your output tab set up, you can copy the values you get back from these forms and start populating a database.

But what if you are doing data entry and need to make these entries multiple times and need the data to push to the output tab automatically after each entry? This is where you will need to set up a macro and need a button to trigger this movement onto the output tab. If you’d like to see how that code might work or just want a ready-to-use file that you don’t have to mess around with, you can download this free template.

The template will grab the input values and based on the named ranges, it will populate them in the output tab once you click the Post button on the main page. If there isn’t a named range that matches to a header on row 1 on the output tab, the data just won’t get copied over. Give it a try!

One additional step you may want to consider is locking down the form in Excel to make sure people don’t accidentally move things around or delete any formulas. You can protect the workbook and the individual sheets do that. Click here for information on how to lock cells.


If you liked this post on how to create a form in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

time-2676366_1280

Excel Automation: 10 Tasks You Can Automate Today!

Spreadsheets can allow you to analyze data and create reports efficiently. But sometimes the tasks that are involved can be difficult or appear to be time-consuming. The good news is that there’s a lot of automation you can achieve in Excel, and it isn’t always necessary to know how to code in order to do so. Below, I’ll show you 10 types of tasks that you can automate in Excel either on your own or with our help.

1. Cleaning and parsing data

One of the more challenging things in Excel is when you’re dealing with a dataset that may not be easy to manipulate. For instance, if you’ve got text mixed in with numbers or dates that aren’t in the right format, Excel may not interpret or recognize the data properly. But there are many formulas that can help you with that. Rather than manually fixing the data, you can use functions like TRIM, CLEAN, LEFT, MID, and RIGHT to extract what you need while also getting rid of extra, unnecessary spaces and other characters.

If you’re looking for more of a walkthrough of the process, there’s a detailed explanation in this post of how to parse data.

Through the use of formulas, you can save hours that you might otherwise spend trying to clean up your spreadsheet. And the best part is that once you’ve set it up, you can re-use the formulas as you add more data. You don’t need to use macros or complicated coding to clean up; a well-structured template can be enough to do the job for you.

2. Creating simple reports

One of the best features of using Excel is that once you’ve entered data into a spreadsheet, it’s even easier to create a report from it. One example is through the use of a pivot table, where through just a few clicks you can easily summarize your data and split it along different categories. Slicers can make filtering and summarizing data even easier in pivot tables, especially for users who aren’t very familiar with Excel. Forget any manual work here; just a few clicks and you’ve got a report that can quickly summarize information in a table for you!

Alternatively, you can also insert charts easily and Excel will try and select the best one based on your data set. There’s also lots of formatting you can apply to charts so that they have the look and feel that you’re after. And once you’ve got a look that works, you can re-use it over and over again.

3. Creating dynamic dashboards

Dashboards are incredibly popular but they can be complex to set up. Then there’s also the challenge of updating it and making sure the data is up-to-date. It can easily take you hours every time to make sure the information is accurate.

However, in this post, I show you how to create a dynamic dashboard that not only won’t take you hours but that will automatically update as you add data to it. And then, you end up with a report that looks great to send to management to easily review and update.

4. Routine data entry

One of the biggest headaches people can face when using spreadsheets is when they hard-code calculations. A hard-coded calculation is where you don’t reference any cells and just put the result in the cell; it can make it nearly impossible to decipher how that number was calculated (especially if you’re not the person who entered the value). If you go to re-calculate it or update it, you could spend a lot of time just trying to figure out the calculation.

However, by using a formula, there’s no ambiguity as to how a value was calculated. Not only does that save you the time of entering in data but it also makes it easy to correct and update the figure. Ideally, you should minimize the number of places you’re manually entering data into. By doing that, you’ll have a much more robust template where your inputs are kept to a minimum which will eliminate the need for a lot of data entry and your other calculated fields will update automatically. This type of automation doesn’t require complex coding and just needs an Excel spreadsheet to be carefully constructed so that it is efficient and makes the most of formulas.

5. Conditional formatting

Oftentimes you’ll want to color-code your data to highlight things you should be paying attention to. If you’ve got an aged accounts receivable schedule, it is useful to highlight which accounts are more than 90 days overdue. You could manually filter the data and highlight all the cells or rows in red that are overdue, but you can just use conditional formatting to do that for you.

Through conditional formatting, you can create rules to determine when a cell or row should be highlighted in red, when you may want it to be in yellow, or when you may just want to hide the text so that you can easily skip over it. For example, hiding zero values can make it easy to focus on more important numbers.

You can apply many different formatting rules and can even put in a hierarchy to determine if you want to keep applying formatting rules or whether you want to stop if a specific criteria is met. Conditional formatting can be complex but it can be a huge time-saver by allowing you to focus on just the items that are important to you. And once you’ve set up the rules, you don’t need to worry about making changes every time you add new data.

Check out this post to learn about conditional formatting and how you can apply it to your spreadsheet.

6. Updating other workbooks and sheets

If you use multiple workbooks, then another area where you can avoid re-entering data is by linking both workbooks. There are numerous ways that you can do this. One approach involves just linking directly to another worksheet where data will automatically pull from another table.

You can also use the INDIRECT function to reference another worksheet or workbook. Just like with a template, once you set up these formulas and connections, they are there to stay and you can avoid having to manually make changes by yourself.

7. Audit tracking by logging changes

One of the neat features of many Office products is they allow you to track changes that are made. This is normally when you share a workbook with other users. However, through the use of macros, you can have a separate sheet that can tell you which values were changed, when, and by who.

Rather than manually noting these changes or relying on people to make the updates themselves, it doesn’t take much effort through a macro to create a log of what’s been changed.

8. Generating PDFs

One thing many advanced Excel users like to do is to use automation to export reports into PDFs. While there is a way to print to PDF, and it’s particularly easy on the newer versions of Excel, it can be a time-consuming process especially when you need to print out multiple sheets. Here again, with a simple macro, you can auto-generate PDFs and save them in a predefined folder all with the click of a button.

9. Sending emails

Another feature many users like is the ability to use automation to send out emails right from Excel. Through the use of macros, this is also possible. You can create a macro that will enter in the email of the recipient, attach a file, enter the body of the message, and even send the email itself. This can be even set up on a large scale, such as sending out invoices to dozens or hundreds of customers, a process that could easily save you hours worth of work.

10. Just about anything else with VBA

The power of programming in Excel can unlock many different possibilities with what you can automate. Whether it’s using automation to help import data and then manipulating it, creating custom reports, or following a series of complicated steps, there are many tasks in Excel that can be expedited with a few clicks of a button. As long as there’s some logic to the process that you can break into steps, then you can also build that into the code and automate it.

Don’t know where to start? Contact us!

There is significant potential in Excel but not everyone knows how to use automation to make the most of it and to make a spreadsheet as efficient as it can be. You can contact us if you have a certain Excel issue that you need help with or if one of the tasks above has perked your interest and you’d like to learn more. We can help create solutions for you that work efficiently and that can save you many hours, perhaps even days every month.


If you liked this post on 10 Tasks You Can Automate Today, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

convertsummary

Convert a Summary Table in Excel Into a Pivot Table

Often times, when you download a data table from somewhere it’s not in the format you need it to be. Tables are often in a summary format where you have months going down and years going across, or vice versa. It’s the end result of what you want a pivot table to look like, but you can’t easily turn that into a pivot table itself. Below, I’ll show you how to turn a summary table in Excel that looks like this:

Summary table.

Into this:

Data in tabular format in Excel.

This format is much more Excel-friendly and one that you can easily convert into a pivot table.

Converting the table

The data I’m using is the same one that I used in an earlier post that went over transposing data. Transposing data, unfortunately, isn’t enough to make data workable if you want to convert it into a pivot table. You’ll want data to be in a tabular format so that there’s a header for the month, year, and value.

You could manually transpose one year at a time and copy the data one by one. But of course, that isn’t optimal at all. The good news is I’ve got a macro that can help you flip that data in one click. It will go through the painstaking process of reorganizing the data for you.

Here’s the code for the macro. You can just put it into a module (I’ll leave a template to download below if you aren’t comfortable doing this step yourself):

Sub flipdata()

Dim cl, nxtcl As Range
Dim lastcol, lastrow, firstcol, firstrow As Integer


'get total number of rows and columns in range
lastcol = Selection.End(xlToRight).Column
lastrow = Selection.End(xlDown).Row

'get first column and row
firstcol = Selection.Column
firstrow = Selection.Row

'assign output starting point
Set nxtcl = Cells(lastrow + 2, firstcol)

nxtcl = "Header 1"
nxtcl.Offset(0, 1) = "Header 2"
nxtcl.Offset(0, 2) = "Value"

Set nxtcl = nxtcl.Offset(1, 0)


'cycle through data

For yr = (firstrow + 1) To lastrow

    For mth = (firstcol + 1) To lastcol

        nxtcl = Cells(firstrow, mth)
        nxtcl.Offset(0, 1) = Cells(yr, firstcol)
        nxtcl.Offset(0, 2) = Cells(yr, mth)
        Set nxtcl = nxtcl.Offset(1, 0)

    Next mth

Next yr

End Sub

It will output the data a couple of rows below where your data ends. It’s important to select the entire range of data before running the macro since it will go through the range that you’ve selected, nothing else. And if there’s data below your selection, it will overwrite that.

After you’ve selected the data, then you run the macro. In my template, I’ve got a button that you can press that will do the job for you and then you’ll get something that looks like this:

Data in tabular form.

Once in this format, you can easily create a pivot table:

Pivot table.

If you’d like to download the file that contains the macro, it’s available here.


If you liked this post on how to convert a summary table in Excel into a pivot table, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

countdowntimer

How to Make a Countdown Timer in Excel

A countdown timer can help you track how much time there’s left to do a task or until a deadline comes due. Below, I’ll show you how you can make a countdown timer in Excel that can track days, hours, minutes, and seconds. In order to make it work, we’ll need to use some VBA code, but it won’t be much. And if all else fails, you can just download my free template at the end of the post and repurpose it for your needs.

Let’s get right into it and start with the first step:

Calculating the difference in days,

To calculate the difference between two dates is easy, as all you’re doing is subtracting the current date and time from when you’re counting down to.

The start date is just going to be today, right this very second. And Excel has a convenient function just for that, called NOW. It doesn’t require any arguments and all you need to do is enter the following formula:

=NOW()

Entering the date and time you’re counting down to is a bit trickier. As long as you enter it correctly, then calculating the differences will be a breeze. However, this may involve a little bit of trial and error since it’ll depend on how your regional settings are setup. For the countdown date, I’m going to set it to the end of the year. Let’s say 11:00 PM on New Year’s Eve. Here’s how I input that into my spreadsheet:

2020-12-11 11:00 PM

The key things to remember here are that there should be a space between the time and the AM/PM indicator (if you use it) and there should be two spaces between the date and the time. Then, it’s just a matter of whether you’ve got the right order of date, month, and year. This is where you may need to do some testing on your end to ensure you’ve got the correct order.

Now that the dates are set up, we can calculate the difference in days. To do this, we can just calculate the difference and use the ROUNDDOWN function to ensure we aren’t adding partial days:

There are 222 days left until the end of the year. By using the NOW function, the formula will automatically update and tomorrow the days remaining will change to 221, and so on. If your output’s looking a little different, make sure to check the formatting and that it’s set to days.

Calculating the difference in hours, minutes, and seconds

There’s not a whole lot of complexity when it comes to calculating the difference in hours, minutes, or seconds. We’re still subtracting the current date from the deadline. The only difference is that now we’re just going to change the formatting. If I do a simple subtraction, I end up with a fraction, which isn’t really usable in its current format:

Counting down the hours, minutes, and seconds left.

The trick here is to change the format of this cell so that it shows me hours, minutes, and seconds. And that’s an easy fix. If I just click on cell C10 and click CTRL+1, this will get me to the Format Cells menu. In here, I’ll want to select a Custom format so that the cells just shows hours, minutes ,and seconds:

Applying a custom format.

Here’s what the countdown timer looks like after the format changes:

Countdown timer.

It’s important to include a date in the calculation even though we’re just doing a difference between hours, minutes, and seconds. Otherwise, the formula wouldn’t correctly calculate in all situations, such as when the deadline hour is earlier than our current hour.

Putting it all together

Now that all the calculations are entered in, now it’s just a matter of formatting the data. We can create a countdown clock that separates days remaining, from hours, minutes, and seconds remaining.

One cell can have the difference in days, while another will have the difference in hours, minutes, and seconds. This goes back to just modifying the formatting and applying a custom format. Here’s how mine looks:

Full countdown timer.

Although we’ve gotten to this point, the challenge is that this countdown timer still doesn’t update on its own. Unless you want to click on the delete button all the time, the countdown isn’t going to move unless there’s something to trigger a calculation in Excel. That’s why we’re going to need to add a macro to help us do that, which bring us to the important last step of this process:

Adding a macro to refresh every second

We need a macro to update the file. Whether it’s every second, every five seconds, it’s up to you. While the countdown timer will update when someone enters data or does something in Excel, that’s not much of a countdown. This is where VBA can help us. If you’re not familiar with VBA, don’t worry, you can just follow the steps below and copy the code.

To get into VBA, click on ALT+F11. From the menu. Once you’re there, click on the Insert button on the menu and select Module:

Creating a new module in VBA.

Over to the right, you’ll see some blank space where you can enter in some code. Copy and paste the following there:

Sub RunTimer()

    If Range("C10") <> 0 Then
        Interval = Now + TimeValue("00:00:01")
        Application.Calculate
        Application.OnTime Interval, "RunTimer"

    End If
End Sub

One thing you may to change is the reference I made to cell C10. Change that to where you have your countdown timer. As long as there’s a value in the cell, the macro will continue running. All it does is check if there’s a value there, and if there is, it updates the worksheet every second. And by doing that calculation, your countdown timer will update even if you’re not making any changes to the spreadsheet.

You can also change the interval which currently updates every second, as noted by the 00:00:01. You can change this to five seconds, 10 seconds, however often you want it to update.

But there still needs to be something that triggers the macro to start running. You can assign a button or shortcut key to do that.

However, in this example I’ll activate it when the sheet is selected. Inside VBA, you should see a list of worksheets. Double-click on the one that contains your countdown timer:

Worksheets in VBA.

You’ll again see blank space to the right where you can enter code. And you’ll also see a couple of drop-downs near the top that you’ll want to look for. By default, the first one should say (General). Change this to Worksheet:

Selecting the Worksheet object.

Next, change the other drop-down which will probably say SelectionChange. Change it to Activate. Then you should see something like this:

Selecting the worksheet activate event in VBA.

Copy the following code into there to call the macro we created above:

RunTimer

Now when you switch to another worksheet and come back to the current one you’ll notice your countdown timer is updating on its own. If you want it to stop it, just clear the cell that has the timer. Otherwise, the macro will continue running every second.

The Countdown Timer Template

If you’d rather just use a template, then you can download one that I’ve made here. You don’t have to worry about macros and instead you just need to enter the end time; the time that you’re counting down towards.

I’ve also got a start/stop button that you can toggle to get the countdown timer going and that will pause it:

Countdown timer.

You can move the button as well as the time your counting down to onto another sheet if you don’t want someone altering it. If you have any questions or comments about this template, please send me an email at contact@howtoexcel.net


If you liked this post on how to make a countdown timer in Excel, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.

reconciler1100x620

Bank Reconciliation Template With Side-by-Side Matching and Automatic Matches

This bank reconciliation template is an update from an earlier file that was made three years ago. It offers many of the same features with some notable improvements, and I’ll go over both in this post. I’ll start off by highlighting the key features and how it can help improve the bank reconciliation process for you.

Matching transactions is easier than ever in this new bank reconciliation template

In the earlier version, the bank reconciliation template looked at the total of transactions for a day or that matched criteria and so it didn’t make match individual items. It can be a mixed bag since some people prefer one way of matching (e.g. multiple cash transactions on a book entry matching up to one large bank deposit amount versus having one deposit for each entry). This template tries to make both methods a bit easier.

The auto-matching feature takes care of the latter approach where each line is effectively given a unique id that will be used to match against other transactions. This is ideal for one-to-one matches where you don’t want to look at just the totals.

If you’ve used the earlier version of this file, you’ll know that in this template, you can set up categories and keys associated with them. For example, if a check transaction shows up on your accounting system as CK#1234 you can create a rule in this template to say anything with CK in the description is categorized as a check and that the numbers that follow the number sign form the key, or the unique identifier. You can create these rules in the Setup tab.

Here are a couple of examples as to how this looks:

setting up categories and keys on the bank reconciliation template

The Category is just the name of the category and the Identifier is what Excel will be looking for in the item description to see if it falls into that category or not. For checks, I’ve used the use numbers after identifier to say that what comes after CK# is what should be the key or the criteria that the template will be looking for when auto-matching. If there is no criteria, you can leave this blank and it’ll simply look at the amount and the category. However, this can be less accurate depending on if you have duplicates and similar data in your bank and book downloads.

You can also cap the length of the key, which is what I did in the above example, setting the Length of Key to 4. What this does is say that only the first four numbers will be pulled after the identifier. You can leave this blank and everything will be included. There is also a section for Gap if you don’t want it to immediately start pulling numbers after it finds the key. For instance, if I used CK as the identifier rather than CK#, then I’d want to set the Gap field to 1, to ensure that it skips over the next character, which in this case would be the # sign. But if you want to immediately pull data after the identifier, you can leave the Gap blank.

Alternatively, you can also just use the date as your key but that will not be very precise. The template and auto-matching will only be as strong as the rules that you put into place.

Manually matching transaction is easy, too

Even if you can’t auto-match all your transactions, I’ve tried to make this template as easy as possible to bulk match transactions as well. While the auto-matching is designed to help one-to-one matches, it’s also possible to match multiple transactions to one. This can be done using the Reconciler, which can be accessed via the Ribbon:

Ribbon buttons on the bank reconciliation file.

In the previous version, these buttons were within the file. Now, they’re on the home tab within the Ribbon, making it easy to access from anywhere in the file. Select a transaction from either the Book or Bank tabs and click the Reconciler button and you’ll have an interface where you can easily match transactions side-by-side:

In the previous version, side-by-side matching was not possible in the Reconciler and this allows you to easily do your matching within this interface. If I select the first transaction, which is a wire transfer, it will show me all the possible wire transfers I can match it to:

However, the ability to match the transactions won’t appear until I have the credits and debits matching an equal amount on both sides, to prevent running into a situation where I’ve matched an unequal amount:

By default, there will be no warning to pop-up when you’re matching transactions. However, if you prefer there to be one, you can change this in the Setup tab where there’s an option to toggle the confirmation from ‘No’ to ‘Yes.’

With it set to off, you can continue going through and matching transactions to ensure that you don’t have to click boxes before moving on to the next item to match. As it’s set up, you can match multiple items to one amount. However, you can’t match multiple-to-multiple and if you want to match multiple to one, then select the one transaction that will be matched to multiple items when launching the Reconciler. In the above screenshot, any transactions on the left-hand-side can be matched to multiple transactions on the right-hand-side, but not vice versa.

However, there is a SWAP button at the top of the form, which is also new, which can allow you to easily switch between the two views.

On both the Book and Bank tabs, there is a column for Manual Override and if you want to match an item manually you just need to enter a value in here. And that’s what the Reconciler does when you’re matching transactions. This is also where the next key feature comes into play: auditing and correcting your matches.

Audit trail from the Reconciler makes it easy to see which transactions are matched to one another

In the transaction that I matched above, it posted this in the Manual Override section:

You’ll notice that it says Previous BOOK Row 6. What this tells me is that the transaction was reconciled on the Previous OS Items tab, which includes transactions carried over from the previous period. It also tells me the row it was on and that it came from the book side. If the entry were to say Current, then it would be from the current transactions and that it would just be from the Book tab rather than the Previous OS Items tab.

If I wanted to undo this match, I could just press delete and clear the data in the Manual Override column. However, if you do this, be sure to clear off the other entry or entries related to it. Otherwise, you can be out of balance if you only cleared out one side of what was matched.

Reconciling the month

Once you’re done with your reconciliation and you want to see a list of your outstanding items, you can click on the Reconcile Month button in the Ribbon. This will spit out the outstanding items and group them by category. This process is similar to how the older version of the file worked.

When you’re finished and ready to start a new month or period, you can click on the New Month button which will clear the Book and Bank tabs and move any outstanding items over to the Previous OS Items tab. You’ll now be able to start a new month or period.

You can also use the Clear Data tab if you just want to remove everything and start completely from scratch

Testing out the file

If you want to give this file a try, please download the bank reconciliation template for free here. You can test out all the functions. There is a limit of just 25 transactions on the Bank and Book tabs. If you want the full version of the product, including with the code unlocked, please visit the product page here.


If you liked this post on the new bank reconciliation template, please give this site a like on Facebook and also be sure to check out some of the many templates that we have available for download. You can also follow us on Twitter and YouTube.