Swiss Army Spreadhseets has now been up for two years, so it’s time for another index!

While this index includes links to all of the Year 1 posts, it doesn’t necessarily group or label them the way they were before. So if you want to go back to the first index you can find it here.

This year I’ve tried to divide the posts into two rough groups: “Projects, Suggestions, and Ideas”, and “Tools and Techniques”.

I tried to put all of the program-independent posts into the first group, “Projects, Suggestions, and Ideas”, along with any templates I’d posted for them. If this blog were about driving they’d be things like “How to drive cross-country” and “How to pick the fastest route”, and “How to get the best gas mileage”.

The second group, “Tools and Techniques” has all the nitty-gritty details, all the things you need to know that vary from one program to another. Again, if this were a driving blog it would be things like “How to read your dashboard”, “How to start your (automatic/stick shift/hybrid) car” or “How to check your oil”.

Thanks for joining me these past two years, and I hope you’ll stick around for a third! If you ever have any questions or things you’d like me to address, feel free to email me at gwenexner@gmail.com.

Projects, Suggestions, and Ideas:

Art
	Making pictures and designs (checkerboards, spectrums, and 8-bit style)
	Making regular geometric figures (equilateral triangles, squares, hexagons, octagons, etc.)
	Pictures and effects created using text formatting
Formatting tables & lists
	Formatting tables with grouped data
	Improving readability with text formatting
	Changing column width for optimal readability when printed
Inventories
	Why spreadsheets can be good for detailed lists/inventories
	Book inventories
Money
	Tracking and planning a household budget (Template)
	Evaluating and illustrating household spending (Template)
	Calculating and illustrating the impact of increased debt payments (Template)
	Template which automatically calculate how to best allocate the money you have available to pay off debts, with a discussion of the formulas and formatting used.
	Second debt payment template, but with the option to spread payments evenly over all debts
Printing
	Fixing printing issues (too large, too small, stuff you don’t want printed on the same page as stuff you do want… )
	Making printing easier by creating table copies that automatically update from the original
Other
	Illustrating available shelf space (Template)
	Sorting: making repeating or complex sorts easier using concatenate
	Parsing dates that are in text format
	Calculating your GPA (Grade Point Average)
	Automatically calculating teaching statistics (with template)

Tools and Techniques

Spreadsheet basics
	Anatomy of a spreadsheet: what all the parts are called
	What constants are, and what variables are
	Moving around a spreadsheet and selecting groups of cells
	What worksheets are, and how to navigate them
Manipulating parts of a spreadsheet
	How to add/insert columns
	How to hide rows and columns
	How to reveal/show hidden rows and columns
	Removing filtering by showing hidden rows
	How to filter data in a spreadsheet
	How to sort data in a spreadsheet
	When to sort, and when to filter
	How to keep the text at the top and/or left of the screen while scrolling (freezing rows/columns)
	Why you might delete cells, rows, or columns, and what happens when you do
	How to delete/remove cells, rows, and columns
	How to work with worksheets (Inserting, deleting, renaming, copying, and moving, both within the spreadsheet and to a different one)
	How to transpose / switch / flip rows and columns
Moving data from one format to another
	Non-spreadsheet sources of table information: .csv, .txt, .tsv, a.k.a. Comma Separated Values, Text Delimited Format, and old text files
	How to open files from other programs (Importing files)
	How to save files as older versions, or for different programs (Exporting files)
	Copying data from websites into spreadsheets
Names and Naming
	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
Functions and Formulas
	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
Functions: 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)
	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)
	How to remove excess spaces (trim)
Functions: Statistics
	How many (count), how much (sum), and average, median, & mode
	How to count almost anything (# rows, columns, cells with numbers, with anything, with nothing, or with specific things)
	Counting and identifying rows and columns in a range
	Counting cells that have numbers in them
	Counting cells that are not empty
	Counting the cells that are empty and (sometimes) the ones with no visible text.
	Counting the number of cells that meet a criteria you’ve specified.
	Counting the number of rows, columns, or sets where multiple cells meet specified criteria
Charts and graphs
	Common charts and graphs, and when to use them
	How to create a pie chart
	How to create a bar or column chart
	When to use a bar chart, and when to use a column chart
	How to create a line chart
	Adding or removing titles and axis labels in graphs and charts
Common errors
	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
Copy/Paste
	How to copy and paste just the results of a formula or function, with no formulas or formatting (paste as values)
	How to paste from websites or word processors without formatting (paste as text)
	How to transpose / switch / flip rows and columns
Formatting
	Formatting options for attractive tables (borders, backgrounds, fonts, alignment, height, width, number masks, colors, etc.)
	Automatic formatting templates
	How to use automatic formatting templates (autoformat)
	Conditional formatting (formats that are applied only when conditions you specify have been met)
	How to automatically change format depending on the contents of the cell (conditional formatting)
	How conflicts between multiple conditional formats are resolved.
	How conflicts in conditional formatting are handled
	Changing the color or design of the cell’s background (Instructions)
	Various options for changing the color or pattern in the background of a cell (Instructions)
	Various options for changing the color or pattern of the borders around a cell (Instructions)
	Right, left, and centered horizontal alignments (Instructions)
	Top, bottom, and middle vertical alignments (Instructions)
	Cell margins a.k.a. how far the text is from the edges of the cell (Instructions)
	Wrapping text (Instructions)
	Shrinking text (Instructions)
	Merging cells (Instructions)
	Text direction & orientation (Instructions)
	Changing the color, size, typeface, and other attributes of fonts (Instructions)
	Changing the column width and/or row height (Instructions using row/column headers, Instructions using menus, and various shortcuts/tricks)
Printing
	Seeing how it will look when printed
	Automatically shrinking it fit on a specific number of pages (or manually expanding it to fill up a page)
	Making table headers print on every page (without unwanted rows/columns between them)
	Setting what columns and rows should fit on a page
	Setting which cells will print (and which won’t)
	Setting the order that pages will print in
	Adding spreadsheet formatting to the printout, so it looks exactly like the screen
	Printing formulas, instead of the results of formulas

In going through all these I found several posts that I’d promised to write, but never gotten around to. So the next several weeks are going to seem a bit random, as I go in and fill in holes.