PlanIt Police Blog

Editing schedule reports? Payroll exports? Whatever it may be that you need to edit in a spreadsheet, there are a few simple useful Excel formulas you can use to make edits much quicker.

Excel is a very powerful tool. The trick with it is knowing how to use it and understand what all the formulas do. Below is a list of 5 formulas in Excel that are simple and extremely useful for scheduling.

1. Alphabetizing

Highlight all the cells you want to alphabetize. Then in the top right hand corner press the "Sort & Filter" button. Choose "Sort A to Z." You will see the cells automatically sort into alphabetical order. If you have other data in the cells to the left or right, they will stick with the correct cell in the column you have highlighted. You only need to highlight the one column you want sorted alphabetically. 

2. Spacing

Evenly spacing cells in a spreadsheet to fit around the contents inside is simple. There are a couple different ways you can do it. The first and simplest way to evenly space out cells is to go to the top of the sheet. Go to the column you want to space correctly and double click between the two columns. Your mouse arrow should turn into a symbol that looks like a line with an arrow pointing out on both the left and right side. 

This will correct the spacing horizontally. If you want to correct the spacing vertically, you will go to the left side, and double click on the bottom line of the cell you want to adjust. This is the best process to use if you just have a couple cells to adjust.

Another way you can adjust the spacing is to use the Formatting tool. This is very useful if you have a lot of cells you need to evenly space at once. Highlight all the cells you want to adjust, click the top right "Format" button, and click either "Autofit Row Height" or "Autofit Column Width."

This will automatically collapse the cells to fit the text that is in them and leave just enough room so you can read what is in them.

3. Sum

If you have a lot of numbers you need to add up, you can use the SUM function. Simply highlight all the numbers/cells you want to add (*tip: you can highlight multiple cells at once by clicking the first cell, holding down the Shift key on your keyboard and clicking the last cell. Click here to read other ways you can quickly highlight cells). Leave one empty cell at the end for the excel scheduling formula to input the answer. At the top right you will see the "AutoSum" option. Once you click that, the sum will automatically calculate and show in the blank cell at the bottom.

4. Rounding

The rounding feature in Excel allows you to round numbers either up or down and will round to either the left or right of a decimal point. Numbers 1-4 will be rounded down, and 5-9 will be rounded up. 

You can choose to display more or fewer digits after the decimal point. Simply click on the cell(s) you want to round, type "=ROUND(number, num_digits)" Depending on how many digits you want to round the number to, this will look different for everyone. 

5. Freeze panes

You can freeze multiple rows or columns when scrolling though a list of information. To do this you would first need to highlight the row or column you would like to freeze, select the "View" tab and then click "Freeze Panes". This will make it easy to keep track of column headers when scrolling through long lists of data.

Learning Excel can be overwhelming at first. It is beneficial to learn core functions like these to get started. The more you get comfortable with it, the more time you will save. 

In PlanIt scheduling software we produce personnel scheduling exports in Excel file format. This allows you to get data out of the system and organize and distribute it however you'd like. For example, maybe your police department is required to submit time off balances for each employee to their city or town administration each month. You can export different reports with the information you need, modify it, and send it off to the city/town administration. No need for any manual calculations.

Learn more about PlanIt's personnel scheduling software at https://www.planitpolice.com/

Need an Excel template for your personnel schedule?

Read More:

• Top 3 Schedule Examples for 24x7 Coverage with 8 Hour Shifts
• Why You Should NOT Purchase Scheduling Software for Your Department
• 3 Reasons Why Police Departments Are Now Using Scheduling Templates