Excel

Buttons can be created using Insert > Illustrations > Shapes and then entering text for the button. You can connect Excel scripts to the button you created by right clicking on the button and selecting ‘Assign Macro.’
You can easily make a spreadsheet or Excel system more usable by incorporating the theme into the spreadsheet. There are built in themes in Excel which can be accessed by Page Layout > Themes in the Toolbar.
- From there, you can customize color palettes and font combinations, which can make an Excel system immensely more attractive and professional.
You can also make forms in Excel look more like web pages by:
- Adding empty columns and/or margins to the top and left side
- Adding a thick border around the form
- Filling the entire form in with one color
- Filling and adding borders to the data entry cells

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:

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:

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:

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):

Right-click on the Excel ribbon and hit “Customize the Ribbon”
Select “Customize Ribbon”
Check the “Developer” in the checkbox on Main Tabs
Click OK and close out of the Excel Options window
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:

Click Insert
Click “Command Button”
Put the button on the spreadsheet
Right-click on the button
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.
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:

On the File tab, go to Options > Customize Ribbon.
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

The macro name should not contain any spaces.
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

Click on the DEVELOPER tab
Click on 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

Don’t save just yet (in order to reuse and make a template)
Delete all the data in the cells
THEN save as in a macro enabled format *xlsm
Make a copy of the newly saved template
Open it
Click on DEVELOPER tab
Click on Macros button
You will see the a dialogue window with the macro(s) you have created
Select the macro name you want to use
review the description of your macro to make sure you are using the right one
Click on Run button