Exploring Microsoft Excel and spreadsheets in general. See also Financial Equations, which has notes on Excel and financial equations.
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
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.
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
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:
|Billy Bob Smith|
|James T. Kirk|
|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:
That needs a bit of hand tweaking in order to make this:
|Billy Bob||Bob Smith|
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
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.
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.
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.
A common task might be to take a column of values and concatenate them with commas.
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())
// 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))))
// 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 that lead to off-site pages about MS Excel and spreadsheets.
- Bill's Conversion Spreadsheet [elivermore.com/conversions.htm]. The page itself has conversion info but the Excel spreadsheet is excellent.
- Contextures.com/TipTech.html. 'Excel Tips and Techniques'
- DotXLS.com. 'A growing collection of VBA and Excel Help resources including our Excel consulting services, Excel tips and tricks, Excel software, free Excel templates, and Excel recovery solutions.'
- ExcelEverywhere.com. 'Using this unique software, everyone can create interactive and calculating web pages!'
- ExcelTip.com. By Joseph Rubin.
- Microsoft.com. They keep changing their URLs, so it's hard to point to anything there.
MS Excel [http://www.greggriffiths.org/webdev/both/excel/]
- "This tutorial is designed to explore the ways in which a web developer can produce a Microsoft Excel file and pass that file to the client. Although this tutorial is written in VBScript, it should be simple to convert it into any other scripting language that you wish to use.".
- I've exported simple HTML tables out as Excel simply by setting the MIME Type in ASP with this code on top:
Response.ContentType = "application/vnd.ms-excel". You can put in equations and use CSS to format color, borders, etc.
- My Excel Pages -- David McRitchie [http://www.mvps.org/dmcritchie/excel/excel.htm].
- USD.edu/trio/tut/excel/. An Excel tutorial.
- Using the File() and Match() functions in Excel
- Spreadsheet apps
- DanBricklin.com/visicalc.htm. 'VisiCalc: Information from its creators, Dan Bricklin and Bob Frankston'
- Google Documents [docs.google.com]. For online spreadsheets, word processing documents, etc. I use Google Sheets a lot. These days I do more with Google Sheets than Excel or any other spreadsheet. There are things you can do with one that you can't do with the other.
- Lotus 1-2-3: The Basics