Creating a spreadsheet is much like creating your pop-up in that it is entirely customizable to your likes and needs. You can use automatic formatting tools to liven up your spreadsheets and to easily locate and present your data.
In the previous post “Hidden Treasures of Spreadsheets: Effective Tools for Project Management and Budgeting – Part 1” we discussed commonly used functions & formulas and examples of how we used them in our spreadsheets at The Lionesque Group. We learned that formulas calculate the value in a cell and that formulas that are predetermined in the data processing software are also known as functions. We also dove into keyboard shortcuts that make inputting and editing data much faster and easier.
Part 2 is about special tools that data processing software provide to easily format your spreadsheets.
One of the great benefits about formatting is that it allows you to personalize the appearance of your data. At TLG we like to call this “branding our spreadsheets.” We can use our brand font and colors to personalize it. Other formatting examples include highlighting cells, putting borders around cells, centering the data in the cells, among hundreds of others. Cells, rows, columns, ranges of each, and the entire spreadsheet can be formatted and branded manually or automatically. Below are several useful formatting tools:
Conditional Formatting: The conditional formatting tool automatically formats a cell or cell range to specific formats depending on the value within the cell. As its name suggests, the basics of this tools is using conditions, most commonly, “if then”. You tell this tool exactly what formatting action needs to take place. You have to think through a statement like this: if a certain cell has a certain value then a certain formatting action has to take place. What that formatting action is, is entirely up to you!
At TLG, one of the ways we use conditional formatting is for project management purposes. For example, if we have a task within a project that is marked as completed it will automatically highlight the cell to the color green. On the other hand, if a task is behind schedule it will turn the cell the color red. Conditional formatting can also be used for infinite purposes. In sales for example, you can use it to show top performers and/or highest and lowest days in sales. Again, conditional formatting is tailored to you and what makes sense for you and your pop-up.
Follow these steps to access the conditional formatting tool:
- In Google Sheets: In the menu > Format > Conditional Formatting
- In Excel: In the menu > Home > Conditional Formatting
Paint Format: Located at the top left hand corner of the screen by default, the Paint Format tool is the little icon that looks like a paint brush. Paint format is one of our favorite tools! Thanks to this tool, branding your spreadsheet is a breeze. If this tool didn’t exist, formatting cells would be extremely tedious as you would have to edit every single cell manually. At TLG we use this tool for everything! We use it to format both complex spreadsheets and as we do simple ones. Let’s say that you have a cell where you modified the text appearance by changing the font, increasing the size, centering it, making it bold, coloring it blue, and finally putting borders around the cell. If you wanted to have the same format for another cell, you would have to undergo six different steps to make the cells similar. Luckily, with the Paint Format tool, it takes only one click to copy the formatting of any, row, column, ranges, or entire spreadsheet.
To use it in Google Sheets and in Excel: Select the cell or cell range of the format you want to copy, then click on the Paint Format icon. Finally click on the cell you want to apply the formatting to and you will see it is automatically modified for you. *From our last post, remember that you can use the “F4” key on your keyboard to repeat the last formatting command. In this manner you won’t have to repeat the three steps just mentioned, but instead you’ll just have to click the cell or cells you want formatted and press “F4” for PC & “Fn+F4” for Mac. While using the F4 key only eliminates one step, overtime it can save you a lot of time especially if you have hundreds of rows of data you have to edit.*
Number formats: Excel and Google Sheets have a predetermined format in each cell. To change this format to one that best fits your needs to go:
- In Google Sheets: Format >Number > You can choose options such as currency, date, time, and others.
- In Excel: In the menu > Home > Number > Use the drop down arrow to choose how to format the values.
To apply the Currency Format while in the cell, simply type a dollar sign ($) prior to typing the value you want to display.
To apply the Percentage Format while in the cell, simply type a percentage sign (%) after typing the value you want to display.
To apply the Fraction Format while in the cell, simply type the number 0, enter a space by tapping the space bar and type the fraction as you regularly (including the forward slash). Please note that this quick trick only work in Excel.
When creating spreadsheets, using tools that automatically format your data will not only assist in branding your spreadsheet but it will also save you time when doing so!