09 July 2013

Love Varnish goes educational - How to make your own spreadsheet part 1!


Hi loves! Some of you might know this already about me, but I am kinda OCD when it comes to organizing my polish. If anyone wants to touch the Helmers that's fine, but I'll keep a close look to see if they place bottles differently and if they don't mess up my system. It keeps me calm knowing everything is in there in the correct order and such. But my love for organization doesn't stop at the Helmers.. it also reflects on my spreadsheet! Many lacquerheads keep track of their collection by using spreadsheets, and so do I. The other day someone mentioned on Facebook it might be nice to be able to read some tips and tricks because not everyone enjoys Excel or spreadsheets as much as I do and it can be though to figure out the correct formulas!

Because of the massive amount of text following, just take a jump for the full article!

Let's talk some basics! You can create a list in either Excel (offline) or Google Docs (online). I prefer Google Docs because it allows me to access my document everywhere, even on my phone, and I don't have to worry about it getting lost or anything because it automagically saves every 10 seconds or something. Also, you can easily share by sending the link to someone.

You can create as many columns as you like, but you'll probably want to do some basics like brand, polish name, collection it belongs to and how many coats you need when applied. You can also add finish, color and lots of other things!

When you don't have a spreadsheet yet but do have a collection already it might be a bit of a pain to start one. But, it does give you a nice insight in what you own and might even save you money in the end - no more buying the same bottle of polish you already have ;-)!

So, first we'll take a look at my current spreadsheet. I change it quite often, adding categories or statistics and I always update as soon as I have new lacquers, sometimes even when I just ordered them and they haven't arrived yet. You can find the complete document right here.




Google Docs (and Excel, too) allows you to create multiple sheets within the same document. I created 2 sheets: Stash list and Statistics. The second sheet is connected to the first. The Stash list is made up with the following columns:
A. This is a hidden column. It has an x on every row there is a polish entered. It serves a purpose on the Statistics sheet. I'll get to that later.
B. This is a hidden column. It marks all rows that are an indie polish. It serves a purpose on the Statistics sheet. I'll get to that later.
C. Here we are getting to some actual info - I put the brand name in column C.
D. Another basic column, the polish name.
E. Because I like to see how many polishes I own in a collection, the collection name goes here.
F. Currently I am in the process of swatching my complete collection on swatchicles. I need to create labels for that and keep track of which I did in this column.
G. The actual swatching is kept track of here.
H. Another hidden column. I want to expand my sheet with the info what color family the polish belongs to. Like "green" is a color family, but mint and darkgreen and teal are all versions of the shade.
I. In line with column H, this is also hidden. This is the specific color column, so here goes stuff like "mint".
J. Want to know which polishes are holographic, or a shimmer, or another finish? It's all in this column.
K. I am very happy I receive samples from time to time, but to keep my disclosure correct on all blogposts I have to keep track of which polishes were samples and which not. If it is a sample, I mark this column with "X".
L. The number of coats. Can be helpful when you want an opaque base for layering or just a quick manicure. "L" is a layering polish, too sheer to wear alone.
M. Luckily I am not as obsessive anymore, but I keep track of untrieds. When a polish is worn or swatched and shown on the blog, it counts as tried. Every untried is marked with "X".
N. Links to the blogposts I created for the polishes in the sheet. I kinda forget to update this column and have to work on it every 2 weeks or so to keep it updated.

The toolbar, all editing can be done from here!
All this data is just plain data. I enter it myself and there are no formulas or anything in the cells. There are a few tips and tricks on this sheet though:
Frozen toprow - You can see some sort of line between row 1 and 2. The top row is frozen to prevent it from getting mixed in with the rest when I sort columns on alphabet. You can freeze a row (or column, or multiple) by going to "view" and "freeze rows" or "freeze columns". To create the same topbar as I have, select "freeze 1 row".
Filter options - Google already allows you as the author to sort columns by using the dropdown button when you hoover over a column. But, if someone else was looking at my sheet and wanted to see only Catrice as a brand, they wouldn't have any other option than just hoping it was sorted by brand. When you add the filter options everyone can sort the sheet in any way they want. To do so, select the toprow by clicking on row 1, and use the filter button displayed next to the little chart image and left of the functions button Σ.
Editing the looks of your sheet - When you create a new sheet, it is just white and not so pretty to look at. You want to edit that! First thing I always do is turn off gridlines (this way everything that doesn't belong to my sheet is just plain white) by going to "view" and select "gridlines". This removes the gridlines. If you click it again, they'll be visible again. You can also change the font, font size, color and background color to your liking. Every button to do so is in the toolbar.

Whew! That was quite some info right there.. I hope you find this useful! I'll be preparing a second post to explain the second part of my sheet, the statistics. If you have any questions let me know in the comments, I love to help out!

20 comments :

  1. Wow, so informative! My spreadsheet is not this involved, lol.

    ReplyDelete
  2. I started one of these when I first first got into blogging and I gave it up pretty quickly. But as my collection is SO much more massive now I think I may give this a shot again! Great tutorial!

    ReplyDelete
  3. This is helpful! I need to do something like this, I keep putting it off... which is stupid because the collection keeps growing larger and more intimidating the longer I wait. I wonder, if you could put your sheet out there as a template for some of us to use! lol

    ReplyDelete
  4. I really need something like this. I started with a notebook and writing the names down, Pretty soon I had a page full. Then tow. Then three. Well, you know where this is headed. I am not not not computer literate and I grew up with a manual typewriter and so I am really lost on this computer spreadsheet thing but this is what I really really need. Thanks.

    ReplyDelete
  5. Haha yeah I can get lost in this quite quickly LOL. I've seen sheets that have even more info on them, like WAY more!

    ReplyDelete
  6. Thank you, glad to be of help!

    ReplyDelete
  7. You are welcome Amanda! And I am actually working on a basic spreadsheet that you can copy and use ;-) so hopefully that goes up with the next post.

    ReplyDelete
  8. If you go ahead and start working on it don't hesitate to contact me with any questions Cin! I know for a lot of us this is typical daily use since we've grown up with a computer, but I can understand it is a lot more tricky if you didn't. Also, if you decide to create an online version, you can always select a second person who can also make modifications. If you get lost that way I could always help you out if needed!

    ReplyDelete
  9. I love my spreadsheet! At the minute I only have brand, name, collection and possible dupe columns, but it needs to be updated. I've a separate sheet for polish lemmings. I really recommend online too, because I used to have mine in excel, fancy formulas & all, and then my hard drive failed and took my spreadsheet with it! Google Docs is so handy, I use it almost daily. Brilliant post, looking forward to seeing the next part!

    ReplyDelete
  10. Thanks for your comment! Yeah I would be sooo scared to loose it if I had a offline version. How long did it take you to recreate it O.o?

    ReplyDelete
  11. Oh my gosh SOOO LONG. I still find polishes I haven't added to it!

    ReplyDelete
  12. I'd go crazy, literally - my need for organization would go NUTS lol!

    ReplyDelete
  13. Thanks for this--I love seeing other people's spreadsheets! The sample column is a great idea; I might try going through my spreadsheet and adding that info.


    I get so frustrated when I'm in a store with a bad 4G connection and I can't check if I already have a polish. I'm so fortunate to have such problems, I know.

    ReplyDelete
  14. awesome post! I definitely need to work on my spreadsheet!

    ReplyDelete
  15. I must study this in order to improve my basic document! Thanks for taking the time to share it! <3

    ReplyDelete
  16. Thanks for your comment Karen! Yeah it is really handy because sometimes you just can't remember how a polish got in your stash :).


    Haha I know the feeling! Though here it is still 3G actually..

    ReplyDelete
  17. You are welcome my dear! I had so much fun writing this and the next post (coming tomorrow). I actually could do Helmer and spreadsheet organization for a living and be happy with it LOL.

    ReplyDelete
  18. Thanks for sharing, I love such posts :) I have compiled my spreadsheet when my stash reached 50 polishes or so, and I keep updating it. After reading your post, I added the "where did I get the polish" column (PR/bought/swapped etc.) and I also have a column with a short description of the polish behaviour, e.g. "applies well, dries ok" or "streaky mess" :) Otherwise, our spreadsheets are quite similar :)

    ReplyDelete
  19. Thanks for your comment! Glad to be of inspiration. Yes, keeping it updated on the go is the best way to maintain the thing!

    ReplyDelete

Hi loves! If you are seeing this message, it means Disqus has not loaded yet. Please wait a second, as soon as this message disappears Disqus will load and I will see your comment properly :). Thanks ♥.