DataByte
February 2019
Roses are red
Violets are blue
We love Excel
And now you can, too!
- Spreadsheets on holiday
- For the Love of Slicers
- SharePoint Libraries
- Alphabetizing in Excel
- Simplified spreadsheets and Fancy charts
- ICYMI- VBA to Save the Day Recording
- RDM Session in Promising Practices 2019
Spreadsheets taking a holiday?
Recently, some folks have discovered that their formulas and charts randomly stop working from time to time. Have you noticed that this little Excel holiday seems to occur when the Pulse Check Survey results are hot off the presses? It's not a coincidence! The Pulse Check workbooks have code written into them that disables the automatic calculation of formulas. This is an Excel feature that helps make the tool more efficient. To prevent a spreadsheet meltdown, once the calculation setting is switched to manual, it applies to all workbooks that are open on your computer. For any of you who have angered Excel and lost a lot of unsaved work, you can appreciate that having calculations set to manual is a nice little self-preservation feature! So if you forget you have the Pulse Checks open and then open up another file, you might notice that some numbers and charts don't work like they should. Don't worry, these tools aren't broken! Just navigate to Formulas -> Calculation options -> Automatic.
Or for quick keys, hold Alt then hit M -> X -> A (you don't have to hold the M+X+A).
You can do this in any open file and it will apply the change to them all. Be sure to save unsaved work first. ;)
For the Love of Slicers...
Mult-select
Holding down control and multi-select button for “slicer reversal”
Old flames still hanging around can really crowd things.
Pivot tables have a knack for remembering old data that is no longer in your data set. These memories can linger in your slicers long after the data is gone. You don't have to keep looking at it, though.
Right-click on the slicer -> Slicer Settings -> Hide items with no data
Pivot table filters do this, too!
Right-click the pivot table -> PivotTable options -> Data tab -> In number of items to retain per field, select None -> Click OK, then refresh the pivot table.
Custom lists
Do "out of order" slicer buttons make you see red? Excel alphabetizes by default, but sometimes that is not the order that makes sense with your data.
- Select the slicer you want to sort, then click File -> Options -> Advanced -> Scroll down to General group -> Click "Edit Custom Lists..." button.
- Then click "NEW LIST" -> put the slicer items in the List entries: -> Then click Add.
- The list will be added into the Custom lists part, double click it to apply it.
- Then go back to the workbook, rt-click the slicer, Slicer Settings-> Use Custom Lists when sorting.
Local Sharepoint Libraries: Share the love, not the frustration!
Some of our schools have *local Sharepoint libraries set up for their Academic Trackers. This makes it easy to give teachers direct access to the Academic Trackers as soon as they are posted, thus eliminating the need for an admin with SLIM access to log in and pull the file to distribute to the staff.
However, giving access to many users can have it's problems. Ideally, once a Tracker is posted, users will click the dots to the right of the document title, and then select download to view it on their computer. But this doesn't always happen. Users with access to this shared library can open the Tracker in it's online format and then change tabs, click on slicers and make other edits, creating confusion when others don't notice that these changes have been made. It's also possible to cause the file to be "locked up" or "checked out" if it's not closed properly, preventing other users from viewing or downloading the file. Frustrating!
Don't worry, there is a solution! The shared library on your local Sharepoint site can be locked down to allow users to view and download the files without allowing any changes. If your school has a local Tracker library and needs permission changes, talk to your Ops Manager about adjusting the permissions. Better yet, send them this Smore. ;)
Directions are here: How to Edit Sharepoint Library Permissions
~Special thanks goes to Jennifer Prince, CR ROM and Jacob Rutherford, KS Ops Mgr for testing this out for us!
*contact your RDM for details on how to set up a local library for you Trackers
Alphabetizing + Decimals = LOVE
Grab a cup of coffee and let Eric teach you how to woo Excel into playing nice with your alphabetical lists!
Sealed with a K.I.S.S.
Keep It Super Simple and get amazing spreadsheets by adjusting a freeze pane, adding filters with a header under a merged title cell and double sort to alphabetize per grade level.
All dressed up
Insert an average or baseline into your line or bar charts, and add a little pizzazz with custom images
Teacher Contact Calls
In case you missed it...
NR RDM Ashley Ego held a VBA session in January.
Check it out here!
https://sas.elluminate.com/p.jnlp?psid=2019-01-09.1043.M.9D2B6C9F83BC4044E7F1AB1F6B9149.vcr&sid=559
Promising Practices 2019 Feb 28 @ 3:35pm ET
Your RDMs are at it again! We're bringing teachers tips and tools for how to effectively navigate the trackers to use for meaningful conversations with students with this session:
Paving the Path: Utilize Academic Trackers to Identify Needs, View Progress, and Communicate with Students
Session Description:
This hands-on workshop will guide you through your school's Academic Tracker, help you find and interpret course- and student-specific data, and utilize the data to efficiently communicate with students and families. During the session, you will have the opportunity to participate in engaging activities, have meaningful conversations about tracker data, and leave with a variety of ideas for using the tracker to guide and inform strategic plans for student success.
https://k12training.com/ContentDetails.aspx?id=4C51F2911D0340DAB72FC34EF5977994
Thursday, Feb 28, 2019, 03:30 PM
undefined
Your Regional Data Managers
Ashley Ego
HHPA | HA-Ind | HVAM | INDLS | ISIN | ISMI | ISPA | ISWI | MEVA | MGLVA | MVCA | OHVA | WIDCA | WIVA
Email: aego@k12.com
Website: https://www.linkedin.com/in/ashleyego/
Location: Milwaukee, WI, USA
Phone: 337-280-7256
Eric Hasper
Western Region
CAVA (Fresno, Kings, Los Angeles, Maricopa, San Joaquin, San Diego, San Mateo, Sonoma, Sutter), ISCA, iQLA, NVVA, GBVA, NVDCA, NVPA, IDVA, ITCA, ORVA, ISORPH, CVA, ORDCA, WAVA, ISWA
Email: ehasper@k12.com
Website: http://www.linkedin.com/in/eric-hasper-43146568
Location: Phoenix, AZ, USA
Phone: 602-299-3763
Kristina St. Clair
Southern Region
ALVA| ARVA | CASC | FLCCA | FPCSO | GCA | LAVCA | NCVA | SCVCS | TNVA | VAVA |
Email: kstclair@k12.com
Location: Richmond Hill, GA, USA
Phone: (912) 662-3300
Paula Mobley
Central Region
AZVA, ISAZ, CODCA, CPOA, PPOS, IAVA, ISKS, ISKA, KSVA, ISMN, IQMN, MNVA, NMVA, ISOK, OVCA, TOPS, TVAH, UTVA, UTVA-PT, WYVA
Email: pmobley@k12.com
Location: St Paul, TX, USA
Phone: 972-743-0500