User Tools

Site Tools


excel_notes

tasks

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:-

dummy

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:

See also:

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

format as numbers

Ctrl + Shift + ! will apply number format.

For example, let's say we have

Select columns A and B. Press Ctrl + Shift + !. We will then get

See also:

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.

Related links:-

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.

excel_notes.txt · Last modified: 2024/01/04 22:42 by raju