Excel notes

From raju

Articles

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.

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.

tags | columnar operations

Tips

vlookup on multiple tables

=IFERROR(VLOOKUP("what?",table1,colnum,FALSE), IFERROR(VLOOKUP("what?",table2,colnum,FALSE),0))

replace blanks with 0

  • Select the data of interest
  • Home -> Find & Select -> Go To Special -> select Blanks -> ok
  • Enter 0 -> Ctrl+Enter

Ref:- See https://www.extendoffice.com/documents/excel/772-excel-fill-blank-cells-with-0-or-specific-value.html#a1 for a detailed version of this tip with screenshots etc.,

Find excel version

File -> Account

Keyboard shortcuts

  • To move quickly to the first/last row/column in a table, select a cell -> double click on the cell border in the direction you wish to go. For example, double clicking on the top border will take you to the first row, double clicking on the left border will take you to first column etc.,
  • Double clicking on a cell containing a formula will highlight all the cells referenced in that formula.
  • To cycle among cells referenced in a formula: ctrl+[, Enter, Enter, ...
  • To cycle among cells referencing a cell: ctrl+], Enter, Enter, ...
  • To convert a row to a filter: select it and then press ctrl+shift+l . Pressing ctrl+shift+l again will convert it back to regular row.

convert YYYYMM as date in

    =DATE(LEFT(A1,4),RIGHT(A1,2),1)
    

convert YYYYMMDD to YYYY-MM-DD

    =TEXT(DATE(LEFT(A1,4),MID(A1,5,2),RIGHT(A1,2)), "yyyy-mm-dd")
    

using isna in an if clause

To do

    if A1 is not #N/A
      use A1
    else
      if B1 is not #N/A
        use B1
      else
        use C1
      end
    end
    

do

    =IF(NOT(ISNA(A1)), A1, IF(NOT(ISNA(B1)), B1, C1))