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 [2022/01/07 17:38] – [sum values based on another column] rajuexcel_notes [2024/01/04 22:42] (current) – [format as numbers] raju
Line 1: Line 1:
 ===== tasks ===== ===== 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 ==== ==== sum values based on another column ====
Line 37: 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.1641577107.txt.gz · Last modified: 2022/01/07 17:38 by raju