Introduction to Powerschool and Excel Jared Schatz Staff Accountant ( 509) 354-7313

1. Introduction to Powerschool and Excel Jared Schatz Staff Accountant (509) 354-7313

2. Data Layout Basics Include some type of key – example student ID number. The best place for this key is in the first column Have your data in rows and columns with no rows or columns that are completely blank. The first row should contain labels that describe the data.

3. Hints Freeze panes View, Freeze Panes Split screen View, Split screen Also with mouse! Quickly format multiline heading Use Alt Enter Copy and Paste Home, Copy and Home Paste CTRL + C and CTRL V Use Enter to paste

4. Hints Review options in the pull down menu Home, Paste, Paste Special. Use the copy feature and Home, Paste, Paste Special and select Values to change formulas to constants Select a cell and use the box in the lower-right-hand corner to autofill cells below. This can be accomplished by dragging the box down or by double-clicking the box.

5. Shortcuts Use Shift + arrow keys to select data Use Shift + end + arrow keys to quickly select groups of data Ctrl + * selects all data Click box to left of the ‘A’ column to select entire worksheet Ctrl Home moves to beginning of worksheet Ctrl End moves to end of worksheet

6. Shortcuts Use CTRL + ; to enter the current date Use CTRL + : to enter the current time Use Quick Access Toolbar or CTRL Z for undo CTRL B toggles bolding CTRL I toggles italics CTRL U toggles underlining

7. Formatting Select data to be formatted Select Home, Font to enter format cells dialog box Try setting on different tabs across the top

8. Basic Formula Entry Use “=“ to start entering formula. Cell references are in the format of A1 Simply click on cells to enter references instead of typing If you make an error, you can press ESC to exit without saving Formulas entered in this manner will automatically adjust to new cells as they are copied and pasted Use the format of $A$1 if you want an absolute reference – i.e. the cell reference will never change

9. Functions A number of functions exist to help with your formulas =SUM(A1.C5) will add all of the numbers in the range =AVERAGEA1.C5) will average all of the numbers in the range =MIN(A1.C5) will find the minimum all of the numbers =MAX(A1.C5) will find the maximum all of the numbers =UPPER(A1) will change the case to all capitals, =LOWER(A1) to all lower and =PROPER(A1) to upper and lower

10. Functions =VLOOKUP(A1.C5,E1,2,FALSE) is an example of the powerful vlookup function In this example it will start with the value in E1. It will try to find that in the first column of the A1.C5 range. This is why it’s good to have your key in the first column of a spreadsheet. When it finds that value, it will display the data from the second column of the A1.C5 range – column B.

11. Functions =VLOOKUP(A1.C5,E1,2,FALSE) is an example of the powerful vlookup function In this example it will start with the value in E1. It will try to find that in the first column of the A1.C5 range. This is why it’s good to have your key in the first column of a spreadsheet. When it finds that value, it will display the data from the second column of the A1.C5 range – column B.

12. Data Management Select a group of data (or just one cell to self-select) and choose Data, Sort from the pull-down menu to sort data. Various selections can be made for different columns Select one of the cells in the spreadsheet and choose Data, Filter from the pull-down menu to set up filters. Then use the arrows that appear on the header line for many options in filtering the data.

13. Advanced Powerschool and Excel Jared Schatz Staff Accountant (509) 354-7313

14. Data Layout Basics Include some type of key – example student ID number. Have your data in rows and columns with no rows or columns that are completely blank. The first row should contain labels that describe the data.

15. Conditional Formatting Select data to be formatted and then use Home, Conditional Formatting to format data based upon values in the spreadsheet

16. Data Concatenation (Put values together) =CONCATENATE(A1,B1,..) will put all of the data in those cells into one cell =A1&B1 will do the same thing You can add constants within double quotes like =A1&”, “&B1)

17. Multiple Formulas Multiple types of formulas and functions can be merged into one formula As an example based on the concatenate example =VALUE(A1&B1) will take the characters in A1 and B1, merge them into one and change them to a number.

18. Links There are two ways to link cells in one spreadsheet to cells in a different spreadsheet The first is to use Copy and Home, Paste, Paste Special, Paste Link The second is to type = and the click the cell being referenced The rules about absolute references apply here as well. If you don’t want the link to change as it is copied, use the format of $A$1

19. Text to Columns Text to columns is a way that you can separate a specific column into multiple columns Ensure that you have blank columns to the right of the one to be separated Select the entire column and then choose Data, Text to Columns from the drop-down menu A wizard will step you through the process of separating based upon a specific character or based upon the width of the data