Become a Spreadsheet Master
5 Tips to a Faster Workflow
Spreadsheets can be intimidating and difficult to learn – but don’t worry, we’re here to help. At Trial Partners we love to use spreadsheets and, through our BAA with Google, utilize Google Sheets as a fast, safe way to store work with patient referrals.
To help you begin your journey to becoming a spreadsheet master, we’ve compiled 5 simple tips below to make you more effective with Excel/Google Sheets.
Tip #1: Say Goodbye to Your Mouse
While using a mouse is easy – it is also very slow. Moving from cell to cell and clicking/double-clicking is a surefire way to keep your workflow slow.
To speed yourself up, get use to using your keyboard exclusively when you are working with spreadsheets. More importantly play with the following commands and use them to quickly navigate to, highlight, or edit a cell.
1) Your arrow keys will move your cursor to the next cell in any direction you choose:
← left arrow
↑ up arrow
→ right arrow
↓ down arrow
2) Shift + arrow:
This command will highlight your current cell and the subsequent cell you choose (based on your arrow direction). Continue to hold shift and select different directions to select multiple cells horizontally and vertically.
3) Ctrl (Command for Mac users) + arrow
This combination will move your cursor immediately to the next blank cell in a column or row. So if you have 100 rows of data and want to instantly move to the bottom, use this command for lightning fast navigation!
Use combinations of the above to move quickly and highlight the data you want. See the video below to see how combining arrow keys, shift, and ctrl/command keys can help you move and select information quickly.
Tip #2: Match your formatting
The last thing most people want is to have their spreadsheet contain haphazard fonts, sizes, and styles. To quickly make your spreadsheet uniform in styling (or to quickly style certain cells of your choosing) use the paintbrush tool!
This handy option allows you to copy and paste the format of one cell to any number of cells you select next.
To use it:
1) Format a cell to your liking
2) Click the paintbrush icon
3) Select all the cells you would like to have that same formatting
Tip #3: Transposing data
If you’ve ever had data in a row that you wanted to appear in a column (or vice versa) then this one is for you.
Rather than the tedious act of cutting and pasting each cell, you can use the transpose command to quickly make a row into a column or a column into a row!
1) In your desired cell, type: =transpose()
2) Inside the parentheses, input the cell selection that you would like to modify.
And that’s it! The software will take care of the rest.
In our example below, we have a list of 100 emails in a row. Watch how easy it is to turn those into a column (plus we use our navigation skills from Tip #1 to quickly select all the emails).
Important: Once you’ve transposed your data, make sure to copy and paste the new data as values. Since the transpose function needs to reference data to work, if you delete the original data before pasting the new information as values then it will disappear when you delete it!
Tip #4: Text to Columns (Deliminate)
Another frustration with spreadsheet data can be breaking up information into separate cells. To do this quickly, we can use the ‘Text to Columns’ function in Google Sheets or the ‘Deliminate’ function in Excel.
In our example below we have address data that is all lumped into one cell. To break up the state and zip codes into separate columns, we simply highlight the addresses and then tell the system which ‘delimiter’ we’d like to use to separate the data (this can be anything from a space, tab, comma, slash, or any other option (including letters and numbers)!
You can see in the video that Google Sheets is smart enough to detect automatically how we should break up the information, but we chose to specify using a comma as a delimiter.
Tip #5: Concatenate
Now that you’ve delimited your data, what about combining it back together? That’s where the concatenate function comes in handy:
1) Type =concatenate()
2) In the parentheses, select the cells you would like to combine
3) Separate the cells using a comma
4) If you want to insert any custom punctuation, spaces, etc, simply enter them in quotation marks between the comma separators
In our example below we are combining address information so that street, city, state and zip code are all together and formatted properly.
Spreadsheets don’t have to be scary or intimidating. In fact, with the right help, Google Sheets and Excel can be extremely powerful tools to help speed up your workflow and improve your organization.
So get in, start playing, and see if these tips can help you begin your journey to spreadsheet mastery!
Have a Question About Using Spreadsheets?
We pride ourselves as spreadsheet masters! Contact us below and we’ll be happy to answer them for you.