excel_notes
Differences
This shows you the differences between two versions of the page.
Both sides previous revisionPrevious revisionNext revision | Previous revision | ||
excel_notes [2022/01/07 17:36] – [tasks] raju | excel_notes [2024/01/04 22:42] (current) – [format as numbers] raju | ||
---|---|---|---|
Line 1: | Line 1: | ||
===== tasks ===== | ===== tasks ===== | ||
+ | |||
+ | ==== commands I came across ==== | ||
+ | ^ command ^ description ^ | ||
+ | | < | ||
+ | * excel.exe /s | ||
+ | * excel.exe /safe | ||
+ | </ | ||
+ | | excel.exe /autumation | do not automatically open files and do not auto-run macros. | | ||
+ | |||
+ | Ref:- | ||
+ | * https:// | ||
+ | |||
+ | ===== useful links ===== | ||
+ | ==== dummy ==== | ||
+ | * refresh pivot table data - https:// | ||
+ | * sort values in a pivot table - https:// | ||
==== sum values based on another column ==== | ==== sum values based on another column ==== | ||
< | < | ||
=sumifs(D2: | =sumifs(D2: | ||
- | < | + | </code> |
will compute sum(D2:D21) where F2:F21 are N and E2:E21 are Y. | will compute sum(D2:D21) where F2:F21 are N and E2:E21 are Y. | ||
Line 12: | Line 28: | ||
See also: | See also: | ||
* https:// | * https:// | ||
- | * using wild cards (ex:- =sumifs(D2: | + | * using wild cards, ex:- =sumifs(D2: |
- | * the sumif function | + | * the sumif function, ex:- =sumif(range," |
==== concatenate with a delimiter ==== | ==== concatenate with a delimiter ==== | ||
Line 37: | Line 53: | ||
tags | dollar sign left justified | tags | dollar sign left justified | ||
+ | |||
+ | ===== tips ===== | ||
+ | ==== format numbers ==== | ||
+ | You can bring up the " | ||
+ | |||
+ | {{: | ||
+ | |||
+ | ==== shortcuts I came across ==== | ||
+ | |||
+ | | insert multiple lines in excel cell | Alt + Enter | | ||
+ | |||
+ | ==== format as numbers ==== | ||
+ | '' | ||
+ | |||
+ | For example, let's say we have | ||
+ | |||
+ | {{: | ||
+ | |||
+ | Select columns A and B. Press '' | ||
+ | |||
+ | {{: | ||
+ | |||
+ | See also: | ||
+ | * https:// | ||
+ | ===== 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, | ||
+ | </ | ||
+ | |||
+ | 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 [[http:// | ||
+ | |||
+ | Related links:- | ||
+ | * Bug report on the 255 character limit - https:// | ||
+ | * http:// | ||
+ | * http:// | ||
+ | |||
+ | Sample code to write .xls file | ||
+ | < | ||
+ | #! / | ||
+ | use strict; | ||
+ | use warnings; | ||
+ | use autodie; | ||
+ | use Spreadsheet:: | ||
+ | |||
+ | # The advantage of using Spreadsheet:: | ||
+ | # which can contain arbitrarily large hyperlinks. If you use | ||
+ | # Excel:: | ||
+ | # hyperlinks can't be larger than 255 characters. | ||
+ | |||
+ | # Create a new Excel workbook | ||
+ | my $workbook = Spreadsheet:: | ||
+ | |||
+ | # Add a worksheet | ||
+ | my $worksheet = $workbook-> | ||
+ | |||
+ | my $row = 0; | ||
+ | my $col = 0; | ||
+ | my $format = $workbook-> | ||
+ | $worksheet-> | ||
+ | $workbook-> | ||
+ | </ | ||
+ | |||
+ | ==== [@] ==== | ||
+ | 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" | ||
+ | |||
+ | {{tag> | ||
excel_notes.1641577015.txt.gz · Last modified: 2022/01/07 17:36 by raju