Before:

After:

 

More resources:

https://www.techrepublic.com/blog/windows-and-office/20-excel-tips-for-creating-stylish-spreadsheets/

https://www.fm-magazine.com/news/2018/feb/excel-formats-and-styles-differences-201818309.html

https://medium.com/@nathanmccallister/how-to-make-your-excel-spreadsheets-look-professional-in-just-11-steps-20592e1781cf


Excel, VBA, and Macros

Excel Tips

This is a useful resource we have referenced when learning more about Excel. The Tech Community forum has Excel experts that have been in all sorts of situations and can help you debug when a problem arises:

  1. https://techcommunity.microsoft.com/

Dropdowns

Dropdown lists or menus make it easier for users to enter data and can help limit entry choices to a cell, which reduces error. Here are some useful resources that we found when implementing them:

  1. https://exceljet.net/excel-functions/excel-sumproduct-function
  2. https://trumpexcel.com/select-multiple-items-drop-down-list-excel/
  3. https://techcommunity.microsoft.com/t5/excel/sumifs-formula-for-a-multiple-select-dropdown/m-p/167865

VBA

VBA (Visual Basic for Applications) is Excel’s programming language and can help implement programming logic into spreadsheets. We used the following websites to help learn the basics of VBA:

  1. https://www.excel-easy.com/vba.html
  2. https://www.thespreadsheetguru.com/
  3. https://docs.microsoft.com/en-us/

Our team found it valuable to use VBA to create Buttons for our spreadsheet, specifically using Macros. You can use Macros by doing the following (source: Easy Excel):

  1. Right-click on the Excel ribbon and hit “Customize the Ribbon”
  2. Select “Customize Ribbon”
  3. Check the “Developer” in the checkbox on Main Tabs
  4. Click OK and close out of the Excel Options window
  5. Go to the Developer Tab

You now have access to developer tools for the excel sheet. You can assign a button to do a task by doing the following:

  1. Click Insert
  2. Click “Command Button”
  3. Put the button on the spreadsheet
  4. Right-click on the button
  5. Click “View Code.” The Visual Basic Editor appears. To access the editor any time, just go to the Developer tab and click “Visual Basic” on the left side.
  6. Again, Excel Easy is a great reference. We couldn’t recommend it enough. Here’s some VBA example code that allows clicking on a button to output the value “Hello” to the cell, A1:

          Option Explicit

          Private Sub CommandButton1_Click()

          Range(“A1”).Value = “Hello”

          End Sub


More about Excel Macros

(source: https://www.guru99.com/introduction-to-macros-in-excel.html)


EXCEL MACRO records your steps in excel and once you save the macro will be able to play the recording back as much as you want.


VBA MACROS is good for automating time consuming and repetitive tasks. These tasks are often rote and don’t require too much critical thinking skills. VBA macros runs in the excel environment and doesn’t require programming expertise to utilize. To make advanced modifications to the macro as basic knowledge of VBA is necessary to achieve more intricate goals.


WHY use VBA Macros?

To prevent daily tasks from becoming boring and tedious. Macros solve such problems by automating such routine tasks such as, importing data, and formatting to specific requirements.


WHAT IS VBA?

VBA or Visual Basic for Applications, is the programming language that Excel uses to record your steps as you perform routine tasks. Programming expertise is NOT necessary because Excel has features that automatically generates the source code for you.


Macro Basics

(source: https://www.guru99.com/introduction-to-macros-in-excel.html)

Macros are one of the developer features. The developers tab is not displayed in excel by default so you will need to display it using the following steps:

  1. On the File tab, go to Options > Customize Ribbon.
  2. Under Customize the Ribbon and under Main Tabs, select the Developer check box.


Protect Your Data

(source: https://www.guru99.com/introduction-to-macros-in-excel.html)

For security purposes, macros are automatically disabled in excel. This is because macros can be used to compromise your system by attackers. In order to run macro you must first enable macros, and be sure to only run macros that you know come from a trusted source.


When using macros you must save your workbook in *.xlsm, which is a macro enabled format.


Creating macros best practices

  1. The macro name should not contain any spaces.
  2. Fill in the description of the macro when creating one. It is similar to commenting in code, as it helps you and others to understand what the macro is doing.

 

CREATING A MACRO STEP by STEP

(source: https://www.guru99.com/introduction-to-macros-in-excel.html)


First follow the steps to open the Developer Tab as written in the VBA section above.


Step 1) Record Macro

 

You will get the a dialogue window that includes:

Macro name: Fill in the Macro name (remember no spaces)

Store macro in: leave it as default.

Description: Fill in the Description

 

Step 2) Record your Macro

This can be importing data, executing functions, or anything you may need. 

 

Step 3) Format the Data

Make the columns bold, add color, add more functions at the end. You should use macros to help standard formatting as well.

 

Step 4) Stop Recording Macro

Click on stop recording macro button

 

Step 5) Replay the Macro