I recently passed my second SANS certification exam: SANS SEC401: Security Essentials: Network, Endpoint, and Cloud certification Exam. This is very different from my first certification: SANS FOR572: Advanced Network Forensics: Threat Hunting, Analysis, and Incident Response. This is because SEC401 is much more about pure memorization instead of hands-on-keyboard skill development. It’s hard enough trying to memorize things with ADHD but when you throw in test-time panicking you’re really not going to remember anything. So I tried to index to make things easier for my panicking brain to find. Here are my tips.
Get Accommodations
Talk to SANS if you’re eligible for testing accommodations no matter what you have going on. (I forgot to do this, but I’m pretty sure they have testing accommodations for a wide range of needs.)
Setup Your Spreadsheet
Set up your columns however you like, but to it three times. In my case I made three columns: Book Number, Page Number, Topic. I repeated this 3 times with an empty column in-between.
Let me put in some sample data
Book Columns
First, let’s make a formula to fill in all the book columns.
Formula for E2 and I2:
=A2
Similarly we use a formula for each page column so it has the same information as column B
Formula for F2 and J2:
=B2
Flipping Topic and Sub-Topic
Now for column G. We want column G to flip topic and subtopic. This is because when I’m panicking during the exam I don’t want try and figure out how I indexed the topics. I just want to look them up. (This will make more sense once we fill in real information) We do this by getting all the characters in a cell written after “:” (=RIGHT(C2, LEN(C2) — FIND(“:”,C2) — 1)). Getting all the characters written before “:” (=LEFT(C2,FIND(“:”,C2)-1)), and concatenate them with “:” (=CONCAT(chars_after, “:”, chars_before)
The formula for G2 is:
=CONCAT(RIGHT(C2,LEN(C2)-FIND(": ",C2)-1),": ",LEFT(C2,FIND(": ",C2)-1))
Flipping Parenthesized terms
K2 will be similar, but what we want is to take the characters within parentheses “()” and switch them with the characters outside the parentheses. We use this with MID. MID is used by giving it a starting number and the number of characters to you want to show. In this case our starting number is where “(“ is located +1. The number of characters we want to show is the location of “)” minus the location of “(“ minus 2. Once again we want to concatenate those items together with “(“ and “)” in the right places. The formula for K2 is:
=CONCAT((MID(C4,(FIND("(",C4)+1),((FIND(")",C4)-FIND("(",C4))-1))), " (",(LEFT(C4,(FIND("(",C4)-2))),")")
Applying Formulas to multiple Rows
Now duplicate the formulas for 1000 rows.
1. Select E1000 through K1000
2. Hold down control+shift and press the up arrow
3. Hold down control and press “d”
Now as you index, you’ll have automatically switch topic with subtopic and abbreviations.
What to Index
- Slide Titles
- Key terms within slides
- Bulleted items
- Application Names
- Terminal Commands
- Skim for key terms within paragraphs
Index the labs too just in case. I used the all-in-one digital copy of the materials and I split screened it with my spreadsheet.
Finalizing Index
Stacking Columns
Once you’re done indexing I stack all the items on top of each other on a new sheet. (We use a new sheet so if we make a mistake we don’t lose what we’ve already indexed)
- Highlight A1, B1, C1
- Hold down control+shift and press the down arrow
- copy the items
- Paste values into the new sheet. Be sure you use PASTE VALUES (you don’t want to paste over formulas by accident)
- Do this again for E2, F2, G2, but remember you’re pasting them in columns A, B, C
- Do this again for I2, J2, K2, but remember you’re pasting them in columns A, B, C
Now you should have a spreadsheet with a bunch of rows and columns A,B, and C filled.
Removing Error Columns
- Select Cell A1 and click the “Filter” button on the “Data” tab
2. Select Columns A through C, and click the “Remove Duplicates” button
3. Now click the “Sort/Filter” button in the same cell as Topic (it looks like a down pointing arrow), enter the # character into the search bar, and click apply.
Now highlight all of the cells excluding the header row. I normally select the first cell under Book, hold control and press the right arrow, then hold control and press the down arrow.
Hold down ALT and press “;” to select only the visible cells.
Then Right-Click on a selected cell and click “Delete Row”
Now all of your error rows are gone.
If you end up with a bunch of blank rows after this step then you can highlight all of the rows again, press “F5" on your keyboard (yes mac too), click the “Special” button, select “Blanks” or “Empty Lines,” and then click “OK”
From here you can right-click on the selected cells and click delete. If asked, have the remaining cells shifted up.
Lastly, Sort by Topic by using the filter/sort button in the same cell as “Topic”
Printing
When printing you will have the option to choose how many columns and rows per page. I printed 6 column, and as many rows as I could while still being able to read the page.
I then took a marker and numbered the pages at the bottom center of the page.
Lastly, On each corner of each page I marked the first three letters of each term closest to that corner. This showed me the starting and stopping letters for each column on each page. It made it easier for my to know what was on the page.
Aaaaaand done with the index….
Post-it Tab Every 25th Page
Get some Post-it tabs (at least 4 colors), and mark every 25th page (page 25, page 50, page 75, page 100, etc.) in your books.
Most indexing instructions tell you to highlight, tab, and mark down the page number in excel, and the information you’re trying to index. However with tests of pure memorization, there’s going to be something you have to index on almost every page. Which means you’re tabbing every page. So you’re better off highlighting what you’re indexing,
