As useful as a spreadsheet can be to convey information, they can be a little drab and disengaging if the full capabilities of Microsoft Excel aren’t understood and leveraged. For today’s tip, we’ll go over a few little-known and underutilized features that can help you create spreadsheets with more style and utility.
Visible Zeros
There’s a fair chance that, at some point, you’ll need to input data into Excel that begins with zeroes. The trouble is, Excel has a habit of hiding these zeros, so 0056907 would display as 56907. This unfortunately renders the data inaccurate. An easy fix to this is to just add a quotation mark in front of the number. This prevents the zeros from being omitted, so instead of resulting in 56907, you’d keep your original “0056907.
Adding a Drop-Down List
Adding a drop-down list to a spreadsheet is a simple, yet effective way to limit the input a particular cell will accept. The first thing you have to do is select the cell that needs to have drop-down capabilities, and click Validate in the Data tab in the header menu. On the Settings page of the window that pops up, there will be a menu labelled Allow. From that menu, select List and highlight the cells that make up the options you want in your drop-down, and click OK.
Accessing Tools on the Developer Tab
Depending on your needs, you may require more advanced capabilities in your spreadsheet, like creating option buttons, creating macros and other features. These can all be found in the Developer tab, which is hidden by default. In order to access it, you’ll first have to access the Excel menu at the top of your screen, and select Preferences. Once you’re provided with the Preferences menu, select Ribbon & Toolbar. You’ll see a list of the various options you can add or remove from your tabs. Selecting Developer will give you access to the tools that tab contains.
Shading Every Other Row
Adding shading to the formatting of your rows to break up individual rows can be very helpful to someone trying to read the information the spreadsheet has to share. To do this, you’ll need to highlight the area where you want to display the effect or use the Select All shortcut (Ctrl + A) to apply the effect to the entire sheet. In the Home tab, click Conditional Formatting and select New Rule from the drop-down menu. You’ll then have a Style drop-down menu to select from. Choose Classic, then select Use a formula to determine which cells to format. Enter the formula =MOD(ROW(),2) and pick your desired color, and your spreadsheet should be striped nicely.