Exploring Microsoft Excel and spreadsheets in general. See also Financial Equations, which has notes on Excel and financial equations.

Intro

Before the browser came along, the 2 "killer apps" (i.e. the most useful, popular, and flexible apps) were word processing apps and spreadsheet apps. Dan Bricklin and Bob Frankston co-created VisiCalc in 1979, the first computer spreadsheet. Since then others spreadsheet apps have come along including the following:

  • Lotus 1-2-3. Lotus 1-2-3 outsold VisiCalc when it was released 1983-01-26 by Lotus (which is now owned by IBM). Lotus 1-2-3 is currently sold as part of the Lotus SmartSuite. In college I had a job where and I did the budgets for the whole university in Lotus 1-2-3.
  • Quattro Pro. From Borland in 1988 but currently sold by Corel as a part of the WordPerfect office suite. Note that "Quattro" is a play on "1-2-3". It was the first to use the "tabbed notebook" or worksheet interface.
  • Microsoft Excel. The precursor to Excel was the 1982 Multiplan but Lotus 1-2-3 kicked its ass. Excel v1 came out in 1985 for the Mac and v2 came out for Windows in 1987. By 1988 Excel was outselling Lotus 1-2-3. Excel was the first spreadsheet that enabled users to style their documents. Excel was also the first spreadsheet that recalculated only the required portions.
  • ClarisWorks/AppleWorks. This suite for Macs came out in 1988 and it had a spreadsheet too.
  • gnumeric. gnumeric is the spreadsheet for the free GNOME desktop.
  • KSpread. KSpread is a spreadsheet for the free KOffice suite for the KDE desktop.
  • Calc. Calc is the spreadsheet portion of the free, cross-platform, open-source OpenOffice suite.
  • Google Sheets. Google Sheets are the spreadsheets in Google Drive / Google Apps.

Spreadsheet apps have a basic interface of a rectangular grid of cells. Each cell can be referenced by concatenating row (usu. 1,2,3,...) and column (usu. A,B,C,...,AA,AB,...), i.e. the top and left most cell is usually referenced A1. Each cell may have a value or and equation. An equation may use cell references as variables in their equations. EG: Cell A3 may have the equation of =A1+A2.

Miscellany

There are so many little spreadsheet tricks and tips that I've used over the years. Many of them I can redo on the fly but I'm sure there were a number that I wish I had recorded somewhere. Better late than never I guess.

Inserting Excel into MS Front Page

When you want to place a table from MS Excel into your web page in MS Front Page, it creates a lot of HTML, esp. CSS. One work around is to insert the Excel document into your web page with Insert > Insert File. This will create much cleaner HTML that is easier to clean up with the usual search and replace.

Excel macro popups

If an Excel file has a macro or has had a macro then when it is opened a popup window appears that asks to enable or disable macros. There is a security risk for Excel files with macros that you are not familiar with.

If you have an Excel file that shouldn't have any macros the popup window might appear if the file used to have a macro. To prevent the popup from appearing you could go to Tools > Macro > Security and lower the security (not recommended) or you could right-click on a worksheet, select View Code, and delete all traces of old modules and code.

See also these offsite links:

Macro to make text files from rows

I haven't verified these macros (because I haven't had the need to do this) but supposedly it will take an Excel file with rows of content, and make a text file for each row.

Solution 1:

Range("A1", Range("A1").End(xlDown)).Select

Dim cell As Object
Dim counter, i As Integer
Dim cellArray() As Variant

'generate cell array

counter = 1

For Each cell In Range(ActiveCell, ActiveCell.End(xlDown))
ReDim Preserve cellArray(counter)
cellArray(counter) = cell.Value
counter = counter + 1
Next cell

'copy array to another worksheet

ActiveWorkbook.Sheets.Add After:=Worksheets(Worksheets.Count)
ActiveSheet.Name = "Destination"

i = 1

For i = 1 To counter - 1
Worksheets("Destination").Cells(i, 1).Value = cellArray(i)
Next

From there you can add a command for exporting the Destination worksheet to a text file. EG:

' Creating a Flat Text File from an Excel Spreadsheet
'  Let's not forget that we can create a Flat File (i.e., simple
'   Text File) with Excel just as we could directly from VB6 !
' *************************************************************

Sub FlatFiler()
    Dim Addr, Space, CCC, SCC, CBN, SC, CurrRow, LastRow
    Sheets("Revised").Select
    Space = Chr(32)
    Close #1
    Open "FlatFile" & Application.Text(Now(), "mmdd") & ".TXT" For Output As #1
    LastRow = Application.CountA(ActiveSheet.Range("A:A"))
    For CurrRow = 1 To LastRow
        Addr = Right(Cells(CurrRow, 1), 1)
        CCC = Space & Space & Cells(CurrRow, 3)  'Pad with 2 spaces
        SCC = Space & Space & Cells(CurrRow, 5)      'Pad with 2 spaces
        CBN = Cells(CurrRow, 7)
        SC = Cells(CurrRow, 9)
      Print #1, Addr; Space; CCC; Space; SCC; Space; CBN; Space; SC
    Next
    Close #1
    MsgBox "Done ------------------>"
End Sub

Solution 2:

Filepath = InputBox("Enter a filename, including full path (row number will be appended)", "", "C:\xceldata")
For i = 1 To Selection.Rows.Count
  Filename = Filepath & i & ".txt"
  Open Filename For Output As #1 ' Open file for output.
  Print #1, Selection.Cells(i, 1).Value ' Write data.
  Close #1 ' Close file.
Next i

These are all from http://monkeyfilter.com/link.php/5939.

Split column into separate columns

A column with text can be split into separate columns.

Let's say you have data like this:

FullName
John Smith
Billy Bob Smith
James T. Kirk
Jane Smith-Wesson
Sue Smith Wesson

Simply select the data, go to the menu for Data > Text to Columns, and have it split by the delimiter of space. This will make new columns like this:

John Smith  
Billy Bob Smith
James T. Kirk
Jane Smith-Wesson  
Sue Smith Wesson

That needs a bit of hand tweaking in order to make this:

FirstName LastName
John Smith
Billy Bob Bob Smith
Jane Smith-Wesson
Sue Smith Wesson

or this:

FirstName MiddleName LastName
John   Smith
Billy Bob   Smith
James T. Kirk
Jane   Smith-Wesson
Sue   Smith Wesson

Note that if the original data was a LastFirstName column with data like "Kirk, James T.", then a split by comma into LastName and FirstName would require no manual tweaking. There are of course many tricky name variants such as  "Smith, Reginald, Jr.".

Macro to output selected cells as HTML

Here is VBScript code for a macro in Excel that takes the selected cells and outputs them the plainest HTML possible. I got this code from http://www.techimo.com/forum/showthread.php?t=106893. Note that I commented out certain parts so I get as simple a table as possible.

Sub Export2HTML()
    'Select the cells before running this macro
    Dim Printstring As String
    Dim r As Long
    Dim c As Long
    Dim WebpageName As Variant
    Dim WebpageFile As String

    WebpageName = Application.GetSaveAsFilename(fileFilter:="Web pages (*.htm), *.htm")
    If WebpageName = False Then Exit Sub
    WebpageFile = Replace(WebpageName, ".html", "")
    WebpageFile = Replace(WebpageFile, ".htm", "")
    WebpageFile = WebpageFile & ".htm"
    Open CStr(WebpageFile) For Output As #1
    'Print #1, "<html>"
    'Print #1, "<body>"
    Print #1, "<table>"
    'Print #1, "<tbody>"
    For r = Selection(1).Row To Selection(1).Row + Selection.Rows.Count - 1
        Print #1, "<tr>"

        For c = Selection(1).Column To _
            Selection(1).Column + Selection.Columns.Count - 1
            Print #1, "<td>" & Cells(r, c).Text & "</td>"
        Next

        Print #1, "</tr>"
    Next
    'Print #1, "</tbody>"
    Print #1, "</table>"
    'Print #1, "</body>"
    'Print #1, "</html>"
    Close #1

    ActiveWorkbook.FollowHyperlink WebpageFile
End Sub

Excel dates and times

Dates and time in Excel is stored as days. EG: 1:00 = one hour is stored as 1/24 = 0.041666.

Excel number formatting

There is, of course, a multitude of number formatting options in Excel. A very common one is something like this: #,###.0;[Red](#,###.0);0.00;"BEGIN TEXT:"@":END TEXT", for positive numbers, negative numbers, zero, and text. However you can also do stuff like this: [<>1]#" item"; [=1]#" items"; General.

Count items that match criteria

One fun Excel function is COUNTIF(range, criteria). Criteria can be items like 32, "32", ">32", "apples", or B4. SUMIF((range, criteria, sum_raange) is similar. Don't for get all the loverly IS functions.

Keyboard shortcuts for Date and Time

The keyboard shortcut to enter the current date is CTRL+;. The keyboard shortcut to enter the current time is CTRL+:. As of 2009-07-04 this works for Google Docs spreadsheets on Firefox but not on Chrome.

Hiding rows or columns

You can selet a row or column header, then right-click and select "Hide". This will hide it from the screen as well as from print.

Zebra striping

One quick way to apply zebra-striping, i.e. shade alternate rows, is to select a range, then Format, then Conditional Formatting, then Formula. Enter the formula as =mod(row(),2)=0. Then select Format and change the Pattern.

Add drop-down lists to a cell or a range

Add drop-down lists to a cell or a range, then select Data, then Validation, then Settings. In the Allow field, select List. In the Source field, select the range where the list is.

Go To

The Go To (either CTRL+G, F5, or Edit then Go To) function is powerful. The Special button in particular can be used to go to cells that are formulas, or not-formulas, or the like.

Concatenate

A common task might be to take a column of values and concatenate them with commas.

In Excel:
Copy the column
Go to a new sheet
Edit > Paste Special > Transpose
File > Save As > CSV

In Google spreadsheets:

=ARRAYFORMULA( CONCATENATE( (A1:A99) & "," ))
=JOIN(",", A1:A99)
Sub the "A1:A99" with the column you want. The JOIN version is easier but as of 2011-03-02 has some bug where you have to use double quotes for the delimiter.

Single equation tips

// Generating IDs

// Makes 6 digit 0-padded ID based on row number:
=RIGHT(CONCATENATE("00000",FIXED(ROW()-1,0,TRUE)), 6)

// Makes 6 digit 1-padded ID based on row number:
=RIGHT(FIXED(ROW()-1+111111110,0,TRUE), 9)

// Makes a random 6 digit ID between 111111111 and 999999999:
=RIGHT(FIXED(RAND()*(999999-111111)+111111,0,TRUE), 6)
// Show counts of non-empty rows in column header:
="Passed (" & COUNTA(E2:E34) & ")"
// Show counts of yes:
=COUNTIF(P2:AN2, "*yes*")
=CHOOSE(WEEKDAY(A2), "Sun", "Mon", "Tue", "Wed", "Thu", "Fri", "Sat")
=IF(A2=0, ".", CHOOSE(WEEKDAY(A2), "Su", "Mo", "Tu", "We", "Th", "Fr", "Sa"))
// Days to go:
=IF(A2=0, ".", A2-TODAY())

Via http://office.microsoft.com/en-us/excel-help/count-the-number-of-words-in-a-cell-or-range-HA001034625.aspx:

// Words in a cell.
=IF(LEN(TRIM(D15))=0,0,LEN(TRIM(D15))-LEN(SUBSTITUTE(D15," ",""))+1)
// Rounds A1 to A2 significant digits.
// EG: If A1=123456 and A2=3, then 123000.
=ROUND(A1,A2-1-INT(LOG10(ABS(A1))))

Via http://webapps.stackexchange.com/questions/40031/concatenating-only-filled-cells:

// Concatenate only non-empty cells.
=JOIN(" vs ",SPLIT(JOIN("%",C10:C14),"%",0))
// Get the value of the last row in a column:
=INDEX(A2:A, COUNT(A2:A))

Links

Links that lead to off-site pages about MS Excel and spreadsheets.



GeorgeHernandez.comSome rights reserved