One year ago yesterday, the very first post went up on Swiss Army Spreadsheets. To celebrate this milestone, it seems like a good time to post an index to everything that’s gone up so far.
When I first started, I said that I was writing this blog to help people realize some of the possibilities inherent in spreadsheets. That’s still true, but there’s another reason as well. I got heartily sick of saying “I know how to do this in Excel 97, but how do I do it in Excel 2007? How do I do this in Google Docs? If all I have is Microsoft Works on my laptop, can I still do this stuff? And can open-source alternatives like OpenOffice Calc do it?” So, by writing this blog I’m learning the answers to those questions, often one step ahead of the readers!
In case this is your first time visiting Swiss Army Spreadsheets, let me start by saying that all of the “how to” posts contain instructions for how to do a certain thing in four different programs: Microsoft Excel 97, Microsoft Excel 2007, Google Docs Spreadsheet, and OpenOffice Calc. At some point during the year I started including instructions for Microsoft Works Spreadsheet as well. I haven’t included instructions for Excel 2010 because I understand that it’s mostly the same as Excel 2007, plus I don’t happen to have it. If at any point I get regular access to another spreadsheet program that’s significantly different from the ones I’m already exploring then I’ll start including instructions for that as well.
I chose this format partially because there’s no telling what program a reader might be using. If you happen to use a program I don’t have listed, but I could get for free/cheap, just let me know and I’ll see if I can add it!
That said, on with the index! Some of the links are duplicated, because I thought readers might have multiple different reasons for wanting to read them.
What’s in every spreadsheet, and how to work with it
- Anatomy of a spreadsheet: what all the parts are called
- Moving around a spreadsheet and selecting groups of cells
- How to add/insert columns
- Why you might delete cells, rows, or columns, and what happens when you do
- How to delete/remove cells, rows, and columns
- What worksheets are, and how to navigate them
- How to work with worksheets (Inserting, deleting, renaming, copying, and moving, both within the spreadsheet and to a different one)
Changing the appearance of your spreadsheet:
- How to hide rows and columns
- How to reveal/show hidden rows and columns
- How to keep the text at the top and/or left of the screen while scrolling (freezing rows/columns)
- How to transpose / switch / flip rows and columns
- Formatting options for attractive tables (borders, backgrounds, fonts, alignment, height, width, number masks, colors, etc.)
Functions and formulas:
- What constants are, and what variables are
- What formulas are, and how they’re constructed
- What functions are, and what they can do
- How to find the name and parameters for a function
- How to find and use the help files for functions
- How to copy just the results of a formula or function
Working with text:
- My favorite functions for working with text (concatenate, left, right, mid/middle, trim, repeat, replace/substitute, upper, lower, proper)
- How to turn two pieces of text into one (concatenate)
- Example of concatenate
- How to get text from the start or end of a string, sentence, or paragraph (right, left)
- How to get text from within/inside a string, sentence, or paragraph (mid)
- Example of right, left, and middle
- How to remove excess spaces (trim)
Working with other data formats:
- What Comma Separated Values (CSV) and Tab Delimited Text formats are
- How to open downloaded financial data
- Working with tables from non-spreadsheet programs
- How to open files from other programs (Importing files)
- How to save files as older versions, or for different programs (Exporting files)
- How to paste from websites
Paste Special options:
- How to paste without formulas or formatting from spreadsheets (paste as values)
- How to paste from without formatting websites or word processors (paste as text)
- How to paste rows as columns, and columns as rows (transpose)
Defining and working with names:
- How to define a name for a group of cells
- Rules for naming groups of cells
- Why you should define names for your columns
- When is a named column treated as a single item, and when is it set of individual cells?
- How to spot and fix errors related to names
Charts and graphs:
- Common charts and graphs, and when to use them
- How to create a pie chart
- Example for pie charts
- How to create a bar or column chart
- When to use a bar chart, and when to use a column chart
- Example for bar charts
- How to create a line chart
- Example for line charts
- Adding or removing titles and axis labels in graphs and charts
Sorting and filtering data:
- How to sort data in a spreadsheet
- Creating one-step sorting shortcuts for complex sorts
- How to filter data in a spreadsheet
- Removing filtering by showing hidden rows
- When to sort, and when to filter
Handling dates:
Finding and fixing problems:
- Common errors (names, circular references, deleted cells, division by zero)
- How to spot and fix errors related to names
- What a circular reference is, and how to spot it
- How to fix circular references
- Spotting and fixing errors caused by deleting cells
Using spreadsheets to help with organization:
- Visualizing available shelf space in libraries
- Why spreadsheets can be good for detailed lists/inventories
- Making an inventory in a spreadsheet
- Example of spreadsheets as databases (detailed lists)
Using spreadsheets to help with budgets and money:
- Making a budget in a spreadsheet
- Organizing and visualizing household budgets from a snapshot
- Calculating the impact of extra payment on debt
- Google Doc templates for budgets and debt payments
By the way, if anyone’s curious… in making this post I created a spreadsheet that had brief descriptions for each post in column A, and their URLs in column B. Then I used the right() function to extract the unique parts of the addresses so I could use relative links, followed by the concatenate() function to build the HTML for the links and insert the descriptions I wanted to use.
If I decide to re-use this data I’ll probably insert two columns before column A, and put the various categories in the first one, and the order within the groups in the second one. That will make it easy to sort the data so that I can just copy whole groups of links and paste them into the post, rather than pasting them one-by-one as I did in this one.
Anyway…one year done! Thanks for reading! I hope you’ve found it helpful, and will continue to do so in the future. :}