Michael Alexander

Cheat Sheet / Updated 03-10-2022

Companies and organizations are always interested in business intelligence — raw data that can be turned into actionable knowledge. This need for business intelligence manifests itself in many forms. Dashboards are reporting mechanisms that deliver business intelligence in a graphical form. Most data analysis benefits from a spreadsheet, so Excel is inherently part of any business-intelligence tool portfolio. This Cheat Sheet provides some useful information and tips for working with Excel dashboards and reports.

Cheat Sheet / Updated 03-09-2022

Microsoft Power Query has its own formula language and its own functions. Here are a handful of Power Query functions that will help you better massage and transform your data. These functions should prove to be some of the most useful in terms of data transformation.

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.

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.

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.

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

Article / Updated 10-15-2021

In Microsoft Excel, you can improve the readability of your dashboards and reports by formatting your revenue numbers to appear in thousands. This allows you to present cleaner numbers and avoid inundating your audience with gigantic numbers. Here's how it works To show your numbers in thousands, highlight them, right-click, and select Format Cells. After the Format Cells dialog box opens, click the Custom option to get to the screen shown in this figure. In the Type input box, add a comma after the format syntax. #,##0, After you confirm your changes, your numbers will automatically appear in the thousands! The beauty part The beautiful thing is that this technique doesn’t change your numeric values, or truncate them, in any way. Excel is simply making them look cleaner. Take a look at this figure: The selected cell has been formatted to show in thousands; you see 118. But if you look in the formula bar above it, you’ll see the real unformatted number (117943.605787004). The 118 you are seeing in the cell is a simpler version of the real number shown in the formula bar. Custom number formatting has obvious advantages over using other techniques to format numbers to thousands. For instance, many beginning analysts would convert numbers to thousands by dividing them by 1,000 in a formula. But that changes the integrity of the numbers dramatically. When you perform a mathematical operation in a cell, you are changing the value represented in that cell. This forces you to carefully keep track of and maintain the formulas you introduced to make the numbers easier to read. Using custom number formatting avoids that by changing only how the number looks, keeping the actual number intact. Making numbers even clearer If you like, you can even indicate that the number is in thousands by adding to the number syntax. #,##0,"k" This would show your numbers like this: 118k 318k You can use this technique on both positive and negative numbers. #,##0,"k"; (#,##0,"k") After applying this syntax, your negative numbers also appear in thousands. 118k (318k) What about millions? Need to show numbers in millions? Easy. Simply add two commas to the number format syntax in the Type input box. #,##0.00,,"m" Note the use of the extra decimal places (.00). When converting numbers to millions, it’s often useful to show additional precision points, as in 24.65m More digestible numbers are easy in Excel, once you know these little tricks.

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.

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

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