User Tools

Site Tools

Trace:
excel_notes

Differences

This shows you the differences between two versions of the page.

Link to this comparison view

Both sides previous revisionPrevious revision
Next revision		Previous revision
excel_notes [2020/10/27 22:23] – rajuexcel_notes [2024/01/04 22:42] (current) – [format as numbers] raju
Line 1: Line 1:
 +===== tasks =====
 +
 +==== commands I came across ====
 +^ command ^ description ^
 +| <WRAP>
 +  * excel.exe /s 
 +  * excel.exe /safe
 +</WRAP> | start excel in safe mode. |
 +| excel.exe /autumation | do not automatically open files and do not auto-run macros. |
 +
 +Ref:-
 +  * https://docs.microsoft.com/en-us/office/troubleshoot/excel/files-open-automatically - low information density.
 +
 +===== useful links =====
 +==== dummy ====
 +  * refresh pivot table data - https://support.microsoft.com/en-us/office/refresh-pivottable-data-6d24cece-a038-468a-8176-8b6568ca9be2
 +  * sort values in a pivot table - https://support.microsoft.com/en-us/office/sort-data-in-a-pivottable-or-pivotchart-e41f7107-b92d-44ef-861f-24430830450a
 +
 +==== sum values based on another column ====
 +<code>
 +=sumifs(D2:D21,F2:F21,"N",E2:E21,"Y")
 +</code>
 +will compute sum(D2:D21) where F2:F21 are N and E2:E21 are Y.
 +
 +Ref:
 +  * https://support.microsoft.com/en-us/office/sumifs-function-c9e748f5-7ea7-455d-9406-611cebce642b - talks about other ways of defining the criterion (ex:- "=A*", "<>Bananas", 32, ">32", B4, "apples", "32")
 +
 +See also:
 +  * https://exceljet.net/formula/sum-if-cell-contains-text-in-another-cell - talks about
 +    * 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.
 +<code>
 +=B3&"_"&C3&"_"&D3
 +</code>
 +
 ==== format dollars ==== ==== format dollars ====
 To put the dollar sign on the left and right justify the number, use the custom format To put the dollar sign on the left and right justify the number, use the custom format
Line 15: Line 53:
  
 tags | dollar sign left justified 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.
 +
 +{{:format_numbers_in_excel.png|}}
 +
 +==== 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
 +
 +{{:before_applying_number_format.png|}}
 +
 +Select columns A and B. Press ''Ctrl + Shift + !''. We will then get
 +
 +{{:after_applying_number_format.png|}}
 +
 +See also:
 +  * https://www.myonlinetraininghub.com/excel-shortcuts - shows many short cuts. The ''Ctrl + Shift + !'' shortcut is listed in the 'Number Formatting' section.
 +===== 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.
 +
 +<code>
 + % cat junk.csv
 +5,6,"=HYPERLINK(""http://raju.shoutwiki.com/wiki/Blog"",""Raju Blog"")",8
 +</code>
 +
 +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://search.cpan.org/~jmcnamara/Spreadsheet-WriteExcel-2.40/lib/Spreadsheet/WriteExcel.pm | Spreadsheet::WriteExcel]] module and directly write the xls file. In any case, do not use the [[http://search.cpan.org/~jmcnamara/Excel-Writer-XLSX-0.86/lib/Excel/Writer/XLSX.pm | Excel::Writer::XLSX]] which generates a .xlsx file.
 +
 +Related links:-
 +  * Bug report on the 255 character limit - https://github.com/jmcnamara/excel-writer-xlsx/issues/26
 +  * http://search.cpan.org/~jmcnamara/Spreadsheet-WriteExcel-2.40/lib/Spreadsheet/WriteExcel.pm
 +  * http://search.cpan.org/~jmcnamara/Excel-Writer-XLSX-0.86/lib/Excel/Writer/XLSX.pm
 +
 +Sample code to write .xls file
 +<code>
 +#! /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();
 +</code>
 +
 +==== [@] ====
 +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 [[http://apprize.info/microsoft/excel_9/10.html | Excel 2016 Bible]] which explains this syntax.
 +
 +{{tag>["columnar operations"]}}
  
excel_notes.1603837388.txt.gz · Last modified: 2020/10/27 22:23 by raju