If you have significant amounts of data in Excel, performing formatting and cleaning tasks may become tedious, repetitive, and painful to perform continuously. Thankfully with macros, you can automate these tasks and save yourself the work.

When you hear the word macro, it can be intimidating if you do not have any coding knowledge or experience. However, Excel has you covered with the Macro Recorder. This feature allows you to create a macro by performing the task; no coding needed!

How to Use Excel’s Macro Recorder

The Record Macro feature is a handy tool found under the developer tab. Since this heading is not automatically added to your ribbon, you may not even know it exists. Do not worry; with these simple steps, you can easily add it to your menu bar and get started.

  1. Open your Excel file in the desktop application.
  2. Select File and click on Options on the bottom left.
    File Menu in Excel with red arrow pointing to the word Options in the bottom left
  3. Click on Customize Ribbon, check the box next to Developer, then hit OK.
    Excel popup with customize ribbon selected on the left and a box around developer tab on the right
  4. The Developer tab is now accessible. Select it, and you will see different macro-options. We will be using the Record Macro feature.

When using macros, you will want to make sure to save your file as an Excel Macro Enabled Workbook and make sure that you safely enabled macros in Excel.

Record a Formatting Macro With Excel

There are several diverse types of tasks we can automate with this feature that will come in handy such as filtering, sorting, and formatting data. To get started, all you need to do is click record macro, perform the task you want to automate, and stop recording when you are finished.

If you make a mistake while recording the macro, you will have to stop the recording and start again, as the macro will follow your steps exactly. Alternatively, if you know how to troubleshoot macros in Excel, you can fix the mistake in the code directly.

  1. Click on the Developer tab you added in the earlier section and hit Record Macro.
    Excel Ribbon, Developer Tab is selected with a red arrow pointing to the Record macro option on the left
  2. Name your macro and hit OK. Below the name, you can create a shortcut key. This will allow you to quickly run the macro without following all the steps, like using CTRL + C to copy data.
    Record Macro Pop-Up to create and name your macro
  3. Go to your data table and select the row with date entries. (Tip: Select the entire row by clicking the first cell in the desired row and hitting CTRL + Shift + down arrow)
  4. Once the row is selected, right-click on the highlighted section and hit Format Cells.
    Excel table with date row selected and right click menu showing. there is a red arrow pointing to format cells
  5. Select Date and the format you want to apply (2012-03-14).
    Excel Format Cells Popup with Date selected on the left and yyyy-mm-dd format selected on the right
  6. To finish the macro, click Stop Recording.
    Excel ribbon with Developer tab selected and a red box around the stop recording macro option on the far left

How to Run Your Recorded Macro in Excel

If you set up a shortcut before recording your macro, you can use that to run it. If you do not have a shortcut, or it is not working, you can trigger it manually with the steps below.

  1. Under the developer tab, click on Macros.
    Excel ribbon with developer tab selected and red box around Macros feature on far left
  2. On the pop-up, click Run. This will automatically update any new data to be in the proper date format. To increase your productivity, we recommend setting up a keyboard shortcut for your macro.
    Excel Run Macro Pop-Up with option to run macro

Automate Your Tasks With Excel’s Macro Recorder

Whether you have copious amounts of data to sort through or are tired of doing the same tasks over and over, let Excel do the tedious work for you with the Record Macro feature.

Now that you have experience with macros, take your skills to the next level and learn to create even more sorting and formatting macros with coding. Start saving time and increase your productivity with the developer tab on all your spreadsheets!