Excel Macros For Dummies book cover

Excel Macros For Dummies

By: Michael Alexander and Dick Kusleika Published: 03-22-2022

Save time and become an Excel wizard with the world’s leading Excel macro guide

Do you love Excel and all the things you can do with it, but wish you could just work…faster? Excel macros—automated workflows that save you time and energy—might be just what you need. In Excel Macros For Dummies, you’ll learn over 70 of the most productive, time-saving macros in less time than it takes to back up the files on your computer!

Every chapter in the book gives you practical info and exercises you can put to work immediately, alongside step-by-step instructions and guidance on how to customize Excel to fit your every need. Inside, you’ll find:

  • Automations that take your Excel productivity to the next level, and beyond
  • Fully updated macros compatible with the newest version of Excel included in Microsoft 365
  • Careful explanations of the basics as well as tips for the advanced user

With something for everyone, Excel Macros For Dummies is the productivity supercharger you’ve been waiting for. Grab a copy today!

Articles From Excel Macros For Dummies

9 results
9 results
Excel Macros For Dummies Cheat Sheet

Cheat Sheet / Updated 03-01-2022

Excel shortcut keys allow you to perform certain tasks using only the keyboard, the idea being that you increase your efficiency when you limit the number of instances your hands have to move back and forth from the keyboard to the mouse. Getting in the habit of using these shortcut keys can help you work more efficiently when using the Visual Basic Editor.

View Cheat Sheet
An Excel Macro to Close All Workbooks at Once

Article / Updated 01-07-2022

One of the more annoying things in Excel is closing many workbooks at once. For each workbook you've opened, you need to activate the work, close it, and confirm the saving of changes. Excel has no easy way to close them all at once. This little macro takes care of that annoyance. How the macro works In this macro, the Workbooks collection loops through all opened workbooks. As the macro loops through each workbook, it saves and closes them down: Sub Macro1() 'Step 1: Declare your variables Dim wb As Workbook 'Step 2: Loop through workbooks, save and close For Each wb In Workbooks wb.Close SaveChanges:=True Next wb End Sub Step 1 declares an Object variable that represents a Workbook object. This allows you to enumerate through all the open workbooks, capturing their names as you go. Step 2 simply loops through the open workbooks, saving and closing them. If you don't want to save them, change the SaveChanges argument from True to False. How to use the macro The best place to store this macro is in your personal macro workbook. This way, the macro is always available to you. The personal macro workbook is loaded whenever you start Excel. In the VBE project window, it is named personal.xlsb. Activate Visual Basic Editor by pressing Alt+F11. Right-click personal.xlb in the project window. Choose Insert→Module. Type or paste the code in the newly created module. If you don't see personal.xlb in your project window, it doesn't exist yet. You'll have record a macro using personal macro workbook as the destination. To record the macro in your personal macro workbook, open the Record Macro dialog box. In the Store Macro In drop-down list, select Personal Macro Workbook. Then simply record a few cell clicks and stop recording. You can discard the recorded macro and replace it with this one.

View Article
An Excel Macro to Open Workbooks to a Specific Tab

Article / Updated 01-07-2022

In some situations, it's imperative that your Excel workbook be started on a specific worksheet. With this macro, if users are working with your workbook, they can't go astray because the workbook starts on the exact worksheet it needs to. In the example illustrated here, you want the workbook to go immediately to the sheet called Start Here. Open the workbook to the Start Here sheet. How the macro works This macro uses the workbook's Open event to start the workbook on the specified sheet when the workbook is opened: Private Sub Workbook_Open() 'Step 1: Select the specified sheet Sheets("Start Here").Select End Sub The macro explicitly names the sheet the workbook should jump to when it's opened. How to use the macro To implement this macro, you need to copy and paste it into the Workbook_Open event code window. Placing the macro here allows it to run each time the workbook is opened: Activate Visual Basic Editor by pressing Alt+F11. In the project window, find your project/workbook name and click the plus sign next to it to see all the sheets. Click ThisWorkbook. In the Event drop-down list, select the Open event. Type or paste the code in the newly created module, changing the sheet name, if necessary. Enter your code in the Workbook Open event.

View Article
10 Jobs for Excel Macro Gurus

Article / Updated 01-07-2022

Excel is everywhere. Companies in all types of industries hire Excel analysts to help provide insights and manage data. Knowing how to wrangle data and automate processes with macros will give you an advantage in the marketplace. These ten jobs are just some of hundreds of jobs available in the marketplace for Excel analysts with VBA macro skills. Accounting specialist Records operating transactions, performs month-end, and prepares financial statements Performs financial and budget analysis by reviewing operating data and assessing historical trends Helps coordinate budgeting and tracks monthly forecasts. Average US salary: $45,000 Marketing analyst Analyzes market penetration and integrates market research with CRM to create new leads Develops customer segmentation models and helps management understand buying habits Analyzes customer survey results and develops customer satisfaction dashboards Average US salary: $55,000 Human resources analyst Analyzes HR data and develops reporting related to time and attendance, attrition, diversity, and recruitment Develops manpower planning models and tracks manpower forecasts Routinely prepares dashboards and reports for quarterly management reviews Average US salary: $65,000 Sales compensation analyst Determines commission payments by analyzing sales transactions as they relate to organizational compensation rules Creates financial reports outlining the actual and projected commission payouts Develops sales incentive models, analyzing costs and advising sales and finance leadership on outcomes Average US salary: $70,000 Supply chain analyst Analyzes procurement and inventory data, ensuring warehouses are prepared for new items and appropriately stocked Analyzes vendor performance and identifies areas of cost savings and improved inventory fill rates Develops reports that track and forecast purchasing and inventory levels Average US salary: $66,000 Investment banking analyst Performs financial modeling and valuation analyses on public and private companies Develops financial and investment models for partners and clients Creates analytical and financial presentations for clients, management teams, and boards of directors Average US salary: $80,000 Business intelligence analyst Work with key personnel to understand business goals and relevant key performance metrics Integrates disparate data sources and creates analytical views that highlight actionable insights Synthesizes data into meaningful dashboards and visual reporting mechanisms Average US salary: $85,000 Statistical analyst Interprets quantitative data and designs statistical models for researching business questions Identifies patterns and relationships in data, giving management insight to previously unseen perspectives Summarizes statistical results into graphics and charts designed to convey results to the management team Average US salary: $77,000 Excel VBA developer Automates existing data integration and transformation processes Conducts the rapid application development for new proof-of-concept solutions Provides change/fix support for existing Excel add-ins and solutions Average US salary: $95,000 Management consultant Routinely conducts analysis on change management, financial performance, business restructuring, and cost management Identifies patterns and relationships in data, giving management insight to previously unseen perspectives Creates analytical and financial presentations for clients, management teams, and boards of directors Average US salary: $120,000

View Article
Manually Editing Data Connections in Excel

Article / Updated 12-25-2017

Once you have an external data connection, you can use the connection properties to point to another database table or query. You can even write your own SQL statements. SQL (Structured Query Language) is the language that relational database systems (such as Microsoft Access) use to perform various tasks. You can pass instructions right from Excel by using SQL statements. This can give you more control over the data you pull into your Excel model. Although a detailed discussion of SQL isn't possible, let's step a bit outside our comfort zone and edit our external data connection using a simple SQL statement to pull in a different set of data. Go to the Data tab on the Ribbon and select Connections. This activates the Workbook Connections dialog box shown here. Choose the connection you want to edit and then click the Properties button. The Connection Properties dialog box opens. Here, you can click the Definition tab. Change the Command Type property to SQL and then enter your SQL statement. In this case, you can enter: SELECT * FROM [Sales_By_Employee] WHERE ([Market] = 'Tulsa')This statement tells Excel to pull in all records from the Sales_By_Employee table where the Market equals Tulsa. Click OK to confirm your changes and close the Connection Properties dialog box. Excel immediately triggers a refresh of your external connection, bringing in your new data.

View Article
Excel Macros: Shortcut Keys for Navigating the VBE Project Window

Article / Updated 02-27-2017

What to Press on Your Keyboard What It Does Up arrow Moves up the project list one item at a time Down arrow Moves down the project list one item at a time Home Moves to the first file in the project list End Moves to the last file in the project list Right arrow Expands the selected folder Left arrow Collapses the selected folder F7 + Shift + Enter Opens the code window for the selected file

View Article
Excel Macros: Shortcut Keys for Debugging Code

Article / Updated 02-27-2017

What to Press on Your Keyboard What It Does F5 Runs the current procedure or continues after pausing Ctrl + Break Halts the currently running procedure F8 Goes into debug mode and executes one line at a time Ctrl + F8 Executes code up until the cursor Shift + F8 Steps over the current line while in debug mode F9 Toggles a breakpoint for the currently selected line Ctrl + Shift + F9 Clears all breakpoints Alt + D + L Compiles the current Visual Basic project

View Article
Excel Macros: Shortcut Keys for Working in the VBE Code Window

Article / Updated 02-27-2017

What to Press on Your Keyboard What It Does Ctrl + Down Arrow Selects the next procedure Ctrl + Up Arrow Selects the previous procedure Ctrl + Page Down Shifts one screen down Ctrl + Page Up Shifts one screen up Shift + F2 Goes to the selected function or variable Ctrl + Shift + F2 Goes to the last position Ctrl + Home Goes to the beginning of module Ctrl + End Goes to the end of module Ctrl + Right Arrow Moves one word to the right Ctrl + Left Arrow Moves one word to the left End Moves to the end of the line Home Moves to the beginning of the line Tab Indents the current line Shift + Tab Removes the indent for the current line Ctrl + J Lists the properties and methods for the selected object

View Article
Excel Macros: Standard Visual Basic Editor Shortcut Keys

Article / Updated 02-27-2017

What to Press on Your Keyboard What It Does Alt + F11 Toggles between the VBE and Excel windows Shift + F10 Displays the active window's shortcut menu Ctrl + R Opens the Project Explorer F4 Opens the Properties window F2 Opens the Object Browser F1 Opens VBA Help F7 Activates the open module window

View Article