USING SPREADSHEETS TO MANAGE DATA A PRACTICE: FORMATTING AND MENU OPTIONS
Since 2011, I have been working in small to medium sized clinics. Among other duties over that time, I’ve been in charge of machine maintenance and repair, preparing for evaluations for accreditation, managing staff, and general purpose data tracking. While there are plenty of tools that allow us to keep track of everything we need to, it’s rarely free and offers too many constraints for my liking. I’ve developed some basic tools in spreadsheets to help me keep track of the things I’m responsible for, and felt like sharing to help us small clinics save a buck or two that can be better spent elsewhere. Like a working fridge.
I’ll show how I use the basic to intermediate functions of software like Excel / Google Sheets / Libre Office / etc. For simplicity’s sake, I’ll be using and talking about Google Sheets throughout this guide. Make a copy of the example worksheets to your own drives and play with them or take them apart as you please.
Any similarities to persons or places is purely coincidental.
| 1.0 useful tools | |
|---|---|
| 1.0 The View Menu Before diving into the functions I use and how I use them, I’ll spend a bit of time on some of the menu options I use. The only item I use here is Freeze. Freezing a range locks a set of rows or columns to the top or left of your sheet, allowing you to scroll at will while keeping those cells in place. This is useful if you want to keep your row or column headers in place so you don’t lose track of which column is assigned to what. |  |
| 1.1 The Insert Menu | |
|---|---|
| This menu is useful if you want to add empty rows, columns or cells in the middle of an already filled sheet. Tick boxes can come in handy for keeping track of tasks, and while this menu offers drop down menus, there are alternative ways of implementing these that I prefer. |  |
| 1.2 The Format Menu | ||
|---|---|---|
| There’s a bounty of options at your disposal here. The Number submenu lets you format a selected range of cells for a specific type of number, listing a number as a date, currency, percentage and so forth, including just plain text (my preference). Text and Alignment work like they do in any word processor. Wrapping has some options for when a cell’s text exceeds the cell size (I opt for clip). There’s even an option here for merging cells, which I use mainly for readability. The real meat of this menu is in the conditional formatting. The software will look at the cell and, depending on certain attributes, apply a formatting rule to it. For example, if there is an account owing, I can select “text contains”, and add a minus sign in the following value field, and apply a red colour to the cell background. This means that any cell with a negative number in it turns red, allowing me to quickly scan a worksheet for accounts owing. Additionally, I use it to quickly identify things like certain procedures and unresolved fields |  |  |
| 1.3 The Data Menu | ||
|---|---|---|
| Some useful choices here are adding filters to sort your data. I personally never use these, but depending on what your needs are, it can be useful. The most useful choice here is Data Validation. This is where I set up my drop down menus. I usually have a sheet tucked away somewhere with a bunch of lists, explicitly to propagate information to drop down menus across all my sheets. That way I only need to update one master list instead of several. Another useful trick is setting a cell to “is valid date”; this lets you simply double click the cell and select a date from a pop-up calendar. You can also choose to display a warning or flat-out reject input if a choice deviates from your intentions |  |  |