Using the File Review Spreadsheet to Organize Your Department Files

Questions

• How do I use the File Review spreadsheet to organize my department's files?
• How do I find the folders I need to review?
• How do I apply the same DepartmentAction to every file in a folder at once?
• How do I refresh the summary after I make changes?

Environment

This article is for department staff who have been asked to review their shared drive content as part of the migration to Microsoft Teams. The File Review spreadsheet is provided by Bowdoin IT and contains a complete list of your department's files plus a summary view to help guide your decisions.

• Audience: Department staff designated as file reviewers for their area
• Software: Microsoft Excel (desktop version recommended, since the bulk-paste shortcuts work most reliably there)
• Support process: A Bowdoin Service Desk technician will meet with your department's migration representative for a working session to review the spreadsheet and help guide the file review process.
• Not in scope: The actual move of files to Teams, OneDrive, or the College Archives. That step is coordinated by IT after your review is complete.

Resolution

1. Open the spreadsheet

Open the Excel file IT sent you. The workbook contains two sheets, shown as tabs at the bottom of the window:

• AllFiles - the full list of every file in your department's shared drive area
• Pivot - a summary view that groups files by folder and highlights folders worth your attention first

2. Start in the Pivot sheet to identify folders that need review

Switch to the Pivot sheet. Your goal is to decide what should happen to each folder. The choices are:

• Move to a Teams team or channel
• Move to a personal OneDrive or to a Research folder
• Send to the College Archives
• Delete outdated or unnecessary files

The Pivot sheet helps you decide where to start:

• Red shading shows folder size - the darker the red, the larger the folder
• Purple shading shows a video or image file
• The 0-3 Years, 3-8 Years, and 8+ Years columns show how recently files in that folder were accessed
• After you enter decisions in DepartmentAction and refresh the Pivot sheet, your updated FinalDecision(auto) values also appear as columns
• The + and - buttons on the left expand or collapse folders and subfolders. You can also right-click a folder name in the Pivot sheet and use Expand/Collapse to show the folder level you want to review.

3. Note the folder you want to act on

From the Pivot sheet, find the folder you want to review and note its folder path.

4. Switch to AllFiles and focus on the columns you need

Go to the AllFiles sheet. The main columns you need to know are:

• Status - used later to track progress after review
• FinalDecision(auto) - shows the value used in the Pivot sheet
• DepartmentAction - this is the column your department fills out
• ArchivesRecommendation - used by the College Archives when they make a recommendation

At the start of the review process, FinalDecision(auto) usually shows how recently files were accessed:

• 0-3 Years
• 3-8 Years
• 8+ Years

The spreadsheet also uses color to help you quickly understand what is happening:

• Yellow in FinalDecision(auto) means the College Archives made a recommendation, but the department has not entered an action yet.
• Green means the department and the College Archives agree.
• Orange means the department and the College Archives entered different decisions.

The College Archives will meet with the department's data owner and migration representative to review any differences and reach agreement about what should move to the College Archives.

5. Filter the file list to that folder

1. On the AllFiles sheet, use the filters in the Root, Path2, Path3, Path4, and Path5 columns to match the folder you noted.
2. Once filtered, the AllFiles sheet shows only the files inside that folder.

6. Apply the same DepartmentAction to all files in the folder

With your filtered list visible:

1. Find the DepartmentAction column (column C).
2. In the first visible row, type the action you want for this folder. Use one of these values:
   • {Team}/{Channel}
   • OneDrive-{username}
   • Research-{folder name}
   • College Archive
   • Delete
3. Press Return.
4. Click the cell again to select it, then copy it with Command + C.
5. Click the cell directly below it.
6. Select to the bottom of the visible filtered list with Command + Shift + Down Arrow.
7. Select visible cells only with Option + ; (semicolon). This prevents the paste from affecting hidden rows.
8. Paste with Command + V.

The same DepartmentAction is now applied to every visible file in that folder. FinalDecision(auto) will update automatically based on your entry.

7. Clear the filter

Open the Data tab in Excel's ribbon and click Clear. This removes the filter so you can move on to the next folder.

8. Refresh the summary

1. Switch back to the Pivot sheet.
2. Right-click anywhere inside the pivot table.
3. Select Refresh.

Your updated FinalDecision(auto) values now appear in the Pivot sheet.

9. Repeat for the remaining folders

Continue working through folders that still need an action, starting with active working folders that should move to Teams, then reviewing the largest, oldest, or most media-heavy folders. Once the DepartmentAction column is complete, Bowdoin IT will coordinate the remaining migration steps.

Visual Guide: How to Review and Organize Your Files

See attachments for a larger image.

Additional Resources

• Microsoft: Filter data in a range or table
• Microsoft: Copy visible cells only
• Microsoft: Refresh PivotTable data

Additional Help

Chat with Bowdoin Bot or contact the Bowdoin College Service Desk at (207) 725-3030.