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.