{"text_format":"1","workbook":"xlsx","n_sheets":11,"sheets":["Title","Rounding","FourZones","AddTextToNumbers","GreenBlue","Hide","TableOfCodes","Dates","Time","RevisitAccounting","Recap"],"not_rendered":{"charts":0,"images":7,"macros":0,"external_links":1,"defined_names":17,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"Title","index":0,"n_rows":25,"n_cols":13,"rows_kept":25,"cut":false,"hidden":false,"merged":[]}
[]
[]
[]
[]
[]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,2044]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
["Image credit: PixelAnarchy / Pixabay"]

{"sheet":"Rounding","index":1,"n_rows":14,"n_cols":8,"rows_kept":14,"cut":false,"hidden":false,"merged":[]}
["Rep","Qty","Revenue","Avg Price",null,null,"Display","Format Code"]
["Andrew Spain",26850,172498.5,{"$f":"IFERROR(C2/B2,\"n.m.\")","v":6.4245251396648042},null,null,{"$f":"D2","v":6.4245251396648042},"General"]
["Geoffrey G Lilley",24458,157921.10000000003,6.456828031727861,null,null,{"$f":"G2","v":6.4245251396648042},"000.000"]
["Kevin J Sullivan",24754,160180.29999999993,6.4708855134523686,null,null,{"$f":"G3","v":6.4245251396648042},"#,##0.0000"]
["Peter Polakovic",24090,143880.5,5.9726234952262347,null,null,6.4245251396648042,"00000"]
[null,null,null,null,null,null,{"$f":"G3","v":6.4245251396648042},"#.###"]
[null,null,null,null,null,null,6.42,"#.###"]
[null,null,null,{"$f":"D2","v":6.4245251396648042},null,null,6.4,"#.###"]
[null,null,null,null,null,null,{"$f":"G5","v":6.4245251396648042},"#.???"]
[null,null,null,null,null,null,6.42,"#.???"]
[null,null,null,null,null,null,6.4,"#.???"]
[null,null,null,null,null,null,{"$f":"G9","v":6.4245251396648042},"#.000"]
[null,null,null,null,null,null,{"$f":"G10","v":6.42},"#.000"]
[null,null,null,null,null,null,{"$f":"G11","v":6.4},"#.000"]

{"sheet":"FourZones","index":2,"n_rows":27,"n_cols":23,"rows_kept":27,"cut":false,"hidden":false,"merged":[]}
["Format"]
["Format >=0;Format<0",null,1234.57]
["Positive;Negative;Zero"]
["Positive;Negative;Zero;Text",null,null,null,null,null,null,"Format Code"]
[null,null,null,null,-1,0,1,"0"]
[null,null,null,null,-1,0,1,"+0;-0"]
[null,null,null,null,-1,0,1,"+0;-0;0"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,1234.5678]

{"sheet":"AddTextToNumbers","index":3,"n_rows":32,"n_cols":9,"rows_kept":32,"cut":false,"hidden":false,"merged":[]}
["Format in C: \"Please Remit \"$0.00;\"Credit Balance of \"$0.00\" (Do not pay)\";\"No Balance\""]
[]
["Customer","Balance","Formatted"]
["Wag More Dog Store, San Antonio",-18.03,{"$f":"B4","v":-18.03}]
["How To Excel At Excel.Com",192.28,{"$f":"+B5","v":192.28}]
["SurtenExcel.com",27.72,27.72,null,-5,0,55,"Hello","Format Code"]
["University of North Carolina",-187.38,-187.38,null,{"$f":"E6","v":-5},{"$f":"F6","v":0},{"$f":"G6","v":55},{"$f":"H6","v":"Hello"},"\"Winner!\";\"Loser!\";\"Zero\";@"]
["Yesenita",120.15,120.15,null,-5,0,55,"Hello","\"Up\";\"Down\";\"Flat\";\"Enter a number!\""]
["Lake Local School District",147.49,147.49,null,-5,0,55,"Hello","\"Positive \"0;\"Minus \"0;0;@"]
["Resource Optimizer",-78.33,-78.33,null,null,null,{"$f":"SUM(G7:G9)","v":165}]
["GL Wand",-178.59,-178.59]
["The Salem Historical Society, Salem, Ohio",-110.53,-110.53]
[]
["These can be used in a code without being in quotes"]
["$","Dollar sign",null,null,"Format Code"]
["+","Plus sign",null,7854]
["(","Left parenthesis",null,{"$f":"D16","v":7854},"{(0>&"]
[":","Colon",null,{"$f":"D17","v":7854},"0^0^0^0"]
["^","Circumflex accent (caret)",null,7854,"0-0-0-0"]
["'","Apostrophe",null,7854,"=0"]
["{","Left curly bracket",null,7854,"0 0 0 0"]
["<","Less-than sign",null,7854,"~0"]
["=","Equal sign",null,7854,">>>0"]
["-","Minus sign",null,7854,"0<<<"]
["/","Slash mark",null,{"$f":"D24","v":7854},"0K"]
[")","Right parenthesis",null,7854,"0\"M\""]
["!","Exclamation point",null,7854,"0\\M"]
["&","Ampersand"]
["~","Tilde"]
["}","Right curly bracket"]
[">","Greater-than sign"]
[null,"Space character"]

{"sheet":"GreenBlue","index":4,"n_rows":23,"n_cols":14,"rows_kept":23,"cut":false,"hidden":false,"merged":[]}
["Custom format: [Green]0;[Blue]-0;"]
[]
[3,-4,-1,-1]
[-4,-4,0,-3,null,null,null,"[Color 1]","[Color 21]","[Color 41]","[Black]"]
[3,1,1,2,null,null,null,"[Color 2]","[Color 22]","[Color 42]","[Green]"]
[1,-3,2,1,null,null,null,"[Color 3]","[Color 23]","[Color 43]","[White]",null,null,"To change color 1 through color 56"]
[-3,-3,0,-3,null,null,null,"[Color 4]","[Color 24]","[Color 44]","[Blue]",null,null,"Excel Options, Save, Colors"]
[5,-3,-3,0,null,null,null,"[Color 5]","[Color 25]","[Color 45]","[Magenta]"]
[-1,0,-1,-3,null,null,null,"[Color 6]","[Color 26]","[Color 46]","[Yellow]"]
[null,null,null,null,null,null,null,"[Color 7]","[Color 27]","[Color 47]","[Cyan]"]
["[Black]",null,null,null,null,null,null,"[Color 8]","[Color 28]","[Color 48]","[Red]"]
["[Green]",null,null,null,null,null,null,"[Color 9]","[Color 29]","[Color 49]"]
["[White]",null,null,null,null,null,null,"[Color 10]","[Color 30]","[Color 50]"]
["[Blue]",null,null,null,null,null,null,"[Color 11]","[Color 31]","[Color 51]"]
["[Magenta]",null,null,null,null,null,null,"[Color 12]","[Color 32]","[Color 52]"]
["[Yellow]",null,null,null,null,null,null,"[Color 13]","[Color 33]","[Color 53]"]
["[Cyan]",null,null,null,null,null,null,"[Color 14]","[Color 34]","[Color 54]"]
["[Red]",null,null,null,null,null,null,"[Color 15]","[Color 35]","[Color 55]"]
["[Color 1]",null,null,null,null,null,null,"[Color 16]","[Color 36]","[Color 56]"]
["[Color 56]",null,null,null,null,null,null,"[Color 17]","[Color 37]"]
[null,null,null,null,null,null,null,"[Color 18]","[Color 38]"]
[null,null,null,null,null,null,null,"[Color 19]","[Color 39]"]
[null,null,null,null,null,null,null,"[Color 20]","[Color 40]"]

{"sheet":"Hide","index":5,"n_rows":9,"n_cols":4,"rows_kept":9,"cut":false,"hidden":false,"merged":[]}
["Custom format: ;;;"]
[]
[3,-4,-1,-1]
[-4,-4,0,-3]
[3,1,1,2]
[1,-3,2,1]
[-3,-3,0,-3]
[5,-3,-3,0]
[-1,0,-1,-3]

{"sheet":"TableOfCodes","index":6,"n_rows":10,"n_cols":4,"rows_kept":10,"cut":false,"hidden":false,"merged":[]}
["Value","Display","Format Code"]
[123.45,123.45,"**0.00","Checkwriter"]
[123.45,123.45,"*!0.00","Bizarre Checkwriter"]
[123456,{"$f":"A4","v":123456},"0,K","Thousands"]
[4565789,4565789,"0.0,,\"M\"","Millions"]
["Hello","Hello","0;-0;0;\"Enter a number!\"","Scolding text"]
[62,62,"[Red][<70]0;[Blue][>90]0;0","3 Conditions"]
[85,85,"[Red][<70]0;[Blue][>90]0;0","3 Conditions"]
[99,99,"[Red][<70]0;[Blue][>90]0;0","3 Conditions"]
[2345,2345,"0_W_W0_N0_i0","Changing the spacing"]

{"sheet":"Dates","index":7,"n_rows":14,"n_cols":10,"rows_kept":14,"cut":false,"hidden":false,"merged":[]}
["Display","Format Code"]
["2018-07-03","m/d/yy",null,null,null,"M","MM","MMM","MMMM","MMMMM"]
["2018-07-03","m/d/yyyy",null,null,"2018-01-01","2018-01-01","2018-01-01","2018-01-01","2018-01-01","2018-01-01"]
["2018-07-03","mm/dd/yyyy",null,null,"2018-02-01","2018-02-01","2018-02-01","2018-02-01","2018-02-01","2018-02-01"]
["2018-07-03","YYYYMMDD",null,null,"2018-03-01","2018-03-01","2018-03-01","2018-03-01","2018-03-01","2018-03-01"]
["2018-07-03","mmm",null,null,"2018-04-01","2018-04-01","2018-04-01","2018-04-01","2018-04-01","2018-04-01"]
["2018-07-03","mmmm",null,null,"2018-05-01","2018-05-01","2018-05-01","2018-05-01","2018-05-01","2018-05-01"]
["2018-07-03","mmmmm",null,null,"2018-06-01","2018-06-01","2018-06-01","2018-06-01","2018-06-01","2018-06-01"]
["2018-07-03","ddd",null,null,"2018-07-01","2018-07-01","2018-07-01","2018-07-01","2018-07-01","2018-07-01"]
["2018-07-03","dddd",null,null,"2018-08-01","2018-08-01","2018-08-01","2018-08-01","2018-08-01","2018-08-01"]
["2018-07-03","dddd\" the \"d\" of \"mmm",null,null,"2018-09-01","2018-09-01","2018-09-01","2018-09-01","2018-09-01","2018-09-01"]
[null,null,null,null,"2018-10-01","2018-10-01","2018-10-01","2018-10-01","2018-10-01","2018-10-01"]
[null,null,null,null,"2018-11-01","2018-11-01","2018-11-01","2018-11-01","2018-11-01","2018-11-01"]
[null,null,null,null,"2018-12-01","2018-12-01","2018-12-01","2018-12-01","2018-12-01","2018-12-01"]

{"sheet":"Time","index":8,"n_rows":13,"n_cols":8,"rows_kept":13,"cut":false,"hidden":false,"merged":[]}
["Display","Format Code"]
["14:15:00","h:mm:ss AM/PM",null,null,null,null,"Display","Format Code"]
[{"$f":"A2","v":"14:15:00"},"h:mm",null,null,null,null,"2016-10-09T06:00:00"]
[{"$f":"A3","v":"14:15:00"},"h:mm AM/PM",null,null,null,null,{"$f":"G3","v":"2016-10-09T06:00:00"},"yyyy"]
["14:15:00","hh:mm AM/PM",null,null,null,null,{"$f":"G4","v":"2016-10-09T06:00:00"},"mmmm"]
["14:15:00","h:mm:ss",null,null,null,null,"2016-10-09T06:00:00","d"]
[{"$f":"A6","v":"14:15:00"},"mm:ss.0",null,null,null,null,42652.25,"h:mm"]
[{"$f":"SUM(A2:A7)","v":3.5625},null,"<-- sum the times"]
[]
[null,null,null,null,null,null,"06:00:00"]
[null,null,null,null,null,null,{"$f":"G10","v":0.25},"[h]"]
[null,null,null,null,null,null,{"$f":"G11","v":0.25},"[m]"]
[null,null,null,null,null,null,0.25,"[s]"]

{"sheet":"RevisitAccounting","index":9,"n_rows":27,"n_cols":24,"rows_kept":27,"cut":false,"hidden":false,"merged":[]}
["Format"]
["Format >=0;Format<0",null,1234.57]
["Positive;Negative;Zero"]
["Positive;Negative;Zero;Text"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,null,1234.5678]

{"sheet":"Recap","index":10,"n_rows":28,"n_cols":2,"rows_kept":28,"cut":false,"hidden":false,"merged":[]}
["Episode Recap"]
[]
[null,"Number formatting is a façade: Excel stores one thing, shows us another."]
[null,"For example: use decrease decimals to show a rounded version of the number."]
[null,"Excel still stores all of the decimals, but simplifies the display."]
[null,"There are 11 number formats in the drop-down on the home tab."]
[null,"Click the dialog launcher to get to Format Cells dialog."]
[null,"Today, we are talking about the formatting codes in the Custom group."]
[null,"I don't have all the knowledge here today, so if you have better tips, put them in the YouTube comments."]
[null,"A zero in the format code says Excel must display the digit."]
[null,"A # sign says they may display the digit if there is enough precision, but they don't have to when there is not."]
[null,"A ? will leave space for the digit to keep the decimal place lined up."]
[null,"There can be 1, 2, 3, or 4 formats, separated by a semi-colon."]
[null,"When there are 2 formats, the first is for zero and up. The second is for negative."]
[null,"When there are 3 formats, the third if for zero."]
[null,"4th format is for text."]
[null,"Using the number format to display text with number or to display text instead of number."]
[null,"Specifying a color for a zone."]
[null,"To change [Color 5] visit Excel options."]
[null,"*x to repeat x up to the numbers"]
[null,", at end to divide by 1000"]
[null,",, divides by millions"]
[null,"Using conditions in the zone"]
[null,"_x will leave space the size of \"x\""]
[null,"m mm mmm mmmm mmmmm"]
[null,"d dd ddd dddd"]
[null,"[h]:mm"]
[null,"Thanks to Dave Baylis, Brad Edgar, Mike Girvin, and @best_excel for suggesting this feature."]