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.
- 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.
tags | columnar operations
vlookup on multiple tables
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
- 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
convert YYYYMMDD to YYYY-MM-DD
using isna in an if clause
if A1 is not #N/A use A1 else if B1 is not #N/A use B1 else use C1 end end
=IF(NOT(ISNA(A1)), A1, IF(NOT(ISNA(B1)), B1, C1))