Annual Index: Year Two

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.

Printing: Nifty tricks

Tying up my series of posts on printing, I’d like to talk about a couple of nifty tricks using the printing options I’ve just discussed.

Trick #1: Repeating headers that aren’t next to each other.
Remember when I said that when setting up repeating headers you could specify as many rows or columns as you wanted, as long as they were next to each other? As far as the spreadsheet is concerned, that’s true. However, that doesn’t mean it has to be true as far as the printer is concerned. If, for example, you had a dual header with a blank row between the main header row and the sub-header row to allow for easy sorting, you wouldn’t really want to print out that blank line.

The solution? Hide the unwanted row.

It’s still there as far as the spreadsheet is concerned, but if it doesn’t show on the screen it doesn’t print, so if it’s hidden then as far as the printer’s concerned it might as well not be there.

Trick #2: Expanding to 1 page
If you have a table that’s not as wide as a printed page, and you’d like it to be, then there’s no automatic way to do it. Adjusting the printing size automatically will only shrink it, not expand it. However, there’s a pretty easy to scale it up: just use the percentages instead of the automatic “adjust to fit”. If you specify a value greater than 100% then the result will be larger than the original. However, you probably won’t know to start with how much you should expand it. Therefore I recommend this process for figuring it out.

  1. Add your best guess, or 50% if you don’t have one, to the size. The first time you do this, the size would be 150%. The second time, if it needs to be expanded again, would be 200%.
  2. Check whether the table is now too wide for the page. You can do this using print preview. If it’s expanded onto a second page, then it’s too large.
  3. If the table is too large, go to step 4. Otherwise go back to step 1 and increase it again.
  4. Decrease it halfway to the last known “too small” size.. If 150% was the largest size that was too small, and 200% was the smallest size that was too large, then the difference is 50%. Therefore you should subtract half of that, which would be 25%, from the smallest “too large” size. In this example that would make it 200% – 25% = 175%.
  5. Check whether the table is too wide for the page. If it is, go back to step 4. Continuing the previous example, 175% would now be the smallest “too large size”, and 175%-150%=25%, so you’d want to decrease it half of 25%, which is 12.5%. If it isn’t wide enough, go on to step 6. If it’s just fine then you’re done.
  6. Increase it halfway to the last known “too large” size. If 175% wasn’t large enough, then you’d want to increase it by half of 200% – 175% = 25%, so 12.5%. The end result would be 187.5%.
  7. Go to step 5. This just creates a loop — keep going until you’re satisfied.

Note: If you’re curious, this is based on one of the simplest search algorithms that computers use: the binary search.

That’s it for printing! Looking back, my first annual index was posted a year ago, so that means it’s time for another one. See you next week, with a gloriously link-filled summary of everything I’ve talked about over the last 52 weeks.