excel_notes

### commands I came across

command description
• excel.exe /s
• excel.exe /safe
start excel in safe mode.
excel.exe /autumation do not automatically open files and do not auto-run macros.

Ref:-

### sum values based on another column

=sumifs(D2:D21,F2:F21,"N",E2:E21,"Y")

will compute sum(D2:D21) where F2:F21 are N and E2:E21 are Y.

Ref:

• 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.

=B3&"_"&C3&"_"&D3

### format dollars

To put the dollar sign on the left and right justify the number, use the custom format

_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_) Ref:- To only show the dollars (but not cents) _($* #,##0_);_($* (#,##0);_($* "-"??_);_(@_)

tags | dollar sign left justified

## tips

### format numbers

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

## Articles

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.

Sample code to write .xls file

#! /usr/bin/perl
use strict;
use warnings;
use autodie;

# 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.