Between social media, online banking, shopping sites, work sites, and more how many passwords have you created? How many passwords have you forgotten? If you’re like most people you’ve forgotten too many and always at the worst times! And sometimes it’s not easy to get back in to your account or device (if you get locked out of your iPhone we can help). A password keeper can help you keep track of all your passwords to make sure you always have easy access to your accounts. Below is a quick tutorial on how to use excel as a password keeper.
Note: If you prefer not to make the spreadsheet check out the bottom of this post to get your free copy of the password file.
Key Components of a Password Log Book
First let’s talk about what’s important in a password book. The obvious answer is passwords! But, there’s more information that may also be relevant and important to keep together. My password log has the following information for each entry:
- Account Name – what is this password for?
- Category – is this for work, online banking, shopping, a hobby, etc
- Email – if you have multiple email addresses, which email did you use with this account
- Username – if there is a username associated with this account
- Password – what password did you use
- Security Questions – did you have to answer security questions, if so, what answers did you give
- Account # – sometimes there is also an account number you want to remember
- Comments – just an extra section for any comments or notes you want to remember
Create a Password Keeper File in Excel
Now that we know what we want included, how do we create our password keeper in excel? If you’re new to Excel it may be helpful to read through this quick Excel 101 tutorial before you begin.
- Open Excel and start a new workbook
- Create the following column headers by typing the words below into the cells indicated:
- Cell A1 – Account
- Cell B1 – Category
- Cell C1 – Website
- Cell D1 – Username
- Cell E1 – Password
- Cell F1 – Security Questions
- Cell G1 – Email Provided
- Cell H1 – Account #
- Cell I1 – Comments
- We want the header row to be more prominent so make the text in row 1 bold. Highlight row 1 by clicking on the gray 1 to the left of that row and click on the bold icon.
- Next, let’s “fix” our header row at the top of the screen so if we end up creating hundreds of rows (you’d be surprised how many passwords you’ll need to keep) the header row is always at the top and visible. To do this, select cell A2 by clicking in it. Then, under the View menu, click Freeze Panes.
This will force all rows above and all columns to the left of the current cell to stay fixed as you navigate around the spreadsheet. So, since only row 1 is above the cursor (and no columns are to the left of it) row 1 will stay at the top of the spreadsheet even if you scroll down to row 300. Give it a try!
- Now let’s make finding an account a little easier by adding filters to the headers. Excel makes adding filters extremely easy. Simply click on any one of the cells in row 1, then, go to the Editing section under the Home tab, click Sort and Filter and select Filter.
You’ll notice that there are dropdowns arrows next to each column name. Once you add data you can use these dropdowns to find your data quicker. Below is an example with some data entered:
From the filter dropdown you can search for an item, sort the table or select certain item(s) using the checkboxes.
- Next let’s make some of the columns a little wider so the words are visible. Highlight the entire worksheet by clicking in the gray rectangle in the upper left corner. Then, once the entire sheet is selected (grayed out), move your cursor over top of the line between the column A and B headers. When the cursor changes to a line with a left and right arrow coming out of it, double click.
This will automatically change the width of column A to fit the data (or text in our case) in column A. And, since we have the entire worksheet highlighted, every column in the worksheet will automatically adjust to fit the data in each column.
- Though I don’t recommend printing and keeping your password book anywhere some people prefer to do it this way. So let’s modify the page setup so we can print the password list if we want to. Go to file and select print to see the print preview.
From the preview it looks like our password sheet is too wide for one piece of paper so let’s change the orientation to landscape so it prints on the long side of the paper.
The password log is still too big so it’s carrying over to page 2. So, select “fit sheet on one page” from the scaling button. Now our entire password spreadsheet fits on one page.
How to Put Password on Excel File
Now, how secure would a password file be if it anyone could find and open it? Not very. So, let’s secure our password keeper by putting a password on the excel file. To add a password to the excel file open the file menu. From the info tab select “protect workbook”. From the dropdown select encrypt with password. You will then need to select a password by entering it twice (it is very important you remember this one!). Now every time you open the file you’ll need to enter the password you just created.
Note: You may also be interested in this post on how to convert excel to pdf for free.
Storing your Password Book
For easy and ready access it’s convenient to store your new password log in the cloud. Services like dropbox and google drive are the perfect place to keep your password file so you can access it on the go on your smartphone. (Check out this post for more information on the cloud and a comparison of cloud services.)
As one final security step, don’t name your file password keeper. Change the name to something that you can recognize but doesn’t yell out to hackers MY PASSWORDS ARE STORED HERE!
For a free copy of the password log template, subscribe below.
Note: After clicking download head over to your email and look for an email from Amy @ German Pearls. If you don’t see our email check your spam folder (they sometimes hide there 🙂 ).
While there are many methods available for maintaining your password log I’ve found this method to be helpful and convenient. How do you store all that secure information? Is there another way you prefer? Leave a comment below.
If you’re looking for a different type of Excel spreadsheet template we can make you a custom spreadsheet or database. Check out our services here.