Differences

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

--- excel_notes [2020/10/27 22:05] – raju
+++ excel_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 ====
-This will put dollar sign on the left and right justify the number
+To put the dollar sign on the left and right justify the number, use the custom format
+<code>
+_($* #,##0.00_);_($* (#,##0.00);_($* "-"??_);_(@_)
+</code>
+Ref:-
+  * https://www.extendoffice.com/documents/excel/3679-excel-align-dollar-sign-left.html - has screenshots; more verbose; messy layout with ads in between content;\\ Afterwards
+    * Find a better reference and link to it.
+    * The above link has <nowiki>""-""</nowiki>, but I get the same result with "-". Find out if there is a difference?
-Use this custom format
+To only show the dollars (but not cents)
 <code>
-_($* #,##0.00_);_($* (#,##0.00);_($* ""-""??_);_(@_)
+_($* #,##0_);_($* (#,##0);_($* "-"??_);_(@_)
 </code>
-Ref:- https://www.extendoffice.com/documents/excel/3679-excel-align-dollar-sign-left.html
+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
+,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"]}}