Table of Contents
commands I came across
| ||start excel in safe mode.|
|excel.exe /autumation||do not automatically open files and do not auto-run macros.|
- https://docs.microsoft.com/en-us/office/troubleshoot/excel/files-open-automatically - low information density.
- sort values in a pivot table - https://support.microsoft.com/en-us/office/sort-data-in-a-pivottable-or-pivotchart-e41f7107-b92d-44ef-861f-24430830450a
sum values based on another column
will compute sum(D2:D21) where F2:F21 are N and E2:E21 are Y.
- https://support.microsoft.com/en-us/office/sumifs-function-c9e748f5-7ea7-455d-9406-611cebce642b - talks about other ways of defining the criterion (ex:- “=A*”, “<>Bananas”, 32, “>32”, B4, “apples”, “32”)
- using wild cards, ex:- =sumifs(D2:D21,C2:C21,“*”&F6&“*”)
- the sumif function, ex:- =sumif(range,“*”&A1&“*”,sum_range)
concatenate with a delimiter
This will combine B3, C3 and D3 with '_' as the delimiter.
To put the dollar sign on the left and right justify the number, use the custom format
_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)
- https://www.extendoffice.com/documents/excel/3679-excel-align-dollar-sign-left.html - has screenshots; more verbose; messy layout with ads in between content;
- Find a better reference and link to it.
- The above link has ""-"", but I get the same result with “-”. Find out if there is a difference?
To only show the dollars (but not cents)
_($* #,##0_);_($* (#,##0);_($* "-"??_);_(@_)
tags | dollar sign left justified
You can bring up the “Format Cells” dialog by clicking on the small arrow button in the bottom right hand corner of the “Number” section.
shortcuts I came across
|insert multiple lines in excel cell||Alt + Enter|
clickable links from csv file
The idea here is to create a csv file that has clickable links when imported into excel. I prefer csv files as opposed to tab separated or space separated files since the excel importing wizard does not pop up if it is a .csv file but pops for other types such as .tsv and .txt.
% cat junk.csv 5,6,"=HYPERLINK(""http://raju.shoutwiki.com/wiki/Blog"",""Raju Blog"")",8
There is one problem with this approach. Any links longer than 255 characters will be chopped off after 255 characters. This is a limitation of xlsx file format which is what excel uses when trying to open a csv file. So if you are generating the csv file through a perl script and try to open it in excel, long links will not be imported correctly.
As a work around, use the Spreadsheet::WriteExcel module and directly write the xls file. In any case, do not use the Excel::Writer::XLSX which generates a .xlsx file.
- Bug report on the 255 character limit - https://github.com/jmcnamara/excel-writer-xlsx/issues/26
Sample code to write .xls file
#! /usr/bin/perl use strict; use warnings; use autodie; use Spreadsheet::WriteExcel; # The advantage of using Spreadsheet::WriteExcel is that it can write xls files # which can contain arbitrarily large hyperlinks. If you use # Excel::Writer::XLSX module and create xlsx file instead, the # hyperlinks can't be larger than 255 characters. # Create a new Excel workbook my $workbook = Spreadsheet::WriteExcel->new( 'clickable_link.xls'); # Add a worksheet my $worksheet = $workbook->add_worksheet(); my $row = 0; my $col = 0; my $format = $workbook->add_format( color => 'blue', underline => 1 ); $worksheet->write_url( $row, $col, 'https://www.google.com', 'google', $format); $workbook->close();
The @ symbol that precedes the column name of a table represents “this row”. So [@foo] means “the value in this row and column foo.”
See “Using Formulas in Tables” section of Excel 2016 Bible which explains this syntax.