EXCEL

child pages:

page index:
pivot charts and tables 
slicers
templates

2024-01-19 unicode in Excel

"smooth scrolling" feature desired instead of "snap scrolling" so top left corner of a cell is always at the row and column origin (working in 4th quadrants)
appears to be in excel 365 as of summer 2022
not sure if that is "the beta" or "the beta" is the installable version of excel

don't make hyperlinks when URL pasted in
File->Options-> Proofing.
AutoCorrect Options->AutoFormat As You Type
Internet and network paths with hyperlinks check box.

select only visible cells    
Home > Find & Select, and pick Go To Special->Click Visible cells only

2023-01-30 compare values in multiple columns then go to How to check if multiple cells are equal
In dynamic array Excel (365 and 2021) you can also use the below syntax. In Excel 2019 and lower, this will only work as a traditional CSE array formula, completed by pressing the Ctrl + Shift + Enter keys together.
This formula will return FALSE if all cells A2 through J2 are not all the same or TRUE if they are all the same, so label the column "ALL SAME?" or similar
=AND(A2=B2:J2)
The result of both AND formulas is the logical values TRUE and FALSE.
To return your own values, wrap AND in the IF function like this:
=IF(AND(A2=B2:C2), "yes", "")
This formula returns "yes" if all three cells are equal, a blank cell otherwise.

2023-01-06 pcmag pivot tables   search "Pivot! Pivot" (several more below it)
2023-01-03 pcmag tip "Hide in Plain Sight" to hide a range of cells by formatting them as Number->Custom->;;;   (three semicolons)

2022-12-07 how to read an Excel file with .NET but see below

from ~2022-10-27
------
------ see complete c# code at RB\misc\programming\2022-10 open excel in .NET
------
I figured out the issue with Excel not closing all processes that we discussed this morning.
This line was the issue
Excel.Workbook xlWorkbook = xlApp.Workbooks.Open(sInputFilename);

Change that line to 2 lines
Excel.Workbooks xlTheNewWorkbooksVar =  xlApp.Workbooks;   // New variable
Excel.Workbook xlWorkbook = xlTheNewWorkbooksVar.Open(sInputFilename);  // changed right hand side from = xlApp.Workbooks.Open(sInputFilename);

then add these 2 lines just before the xlApp.Quit(); call
xlTheNewWorkbooksVar.Close()-;Marshal.ReleaseComObject(xlTheNewWorkbooksVar);-

For Details, see the the post by VVS (edited Oct 26, 2018 at 9:28) answered Oct 1, 2008 at 17:30 on this page
https://stackoverflow.com/questions/158706/how-do-i-properly-clean-up-excel-interop-objects
and the post by bruce barker at Apr 14, 2006 on this page
https://www.thecodingforums.com/threads/asp-net-cant-kill-excel-exe-with-office-pia.123703/#post-528575

2022-12-07 hide the little popup when pasting File->Options->Advanced->Cut, Copy & Paste->Show Paste Options Button

2022-02-25 date arithmetic

2021-10-16 find a text value in a range (or column)
=IF(COUNTIF(range,value)>0,"found","not found")
=COUNTIF(A1:A100,"*"&C1&"*")>0     to use wildcards

rounding to specific fractions (1/32nd, 1/64th, etc. Generally, to round E2 to the nearest Nth (example, N is 64) and show in lowest terms, format cell as # #/####### (Excel is not reliable showing fraction values from decimals when allowing more than 7 digits in the denominator), and use:
=ROUND(E2/(1/N),0)*(1/N)

Refresh data connected to another workbook   2  3

Fill in a column's missing values with previous populated cell:  make a new columm, fill with =LOOKUP("zzzzz",A$1:A2)  formulas where "zzzzz" is not in the dataset

array formuals for things such as "Counting the number of differences between two ranges of cells", "Finding the location of the maximum value in a range" and "Sum a range that contains error values"

chart the frequency of a data set into buckets using array formulas

modify named cell range
create dropdown list in cell

templates:   light green lines   bolder green lines   histogram with second y axis cumulatirve percent     frequency count using subtotals     slanted headings

macros:    highlight changed cells       highlight current cell - put in sheet

date format "yyyy/mm/dd hh:mm AM/PM ddd"   gives "2013-01-01 10:00 AM Fri" 14 tips for 2010   

shift +F5 to get excel focus back to find dialog     second vertical and horiztal axes    turn on 2007 Developer tab 

first element in a column =COUNTIF($K$1:$K5,$K6) for row 6

hide images during filtering   Size and Properties -> Properties -> Move and Size with Cells  [multiples by Home->Editing->Find & Select->Select Objects then CTRL + A  OR Home->Editing->Find & Select->Go To (Ctrl+ G), then select "Special", then select "Objects"]

2010 open files in new window In Windows Explorer->Tools/Folder->Options->File Types tab->.xlsx->Advanced->Uncheck the "Browse in same window" option; Open->Edit->"Application used..."->change to end with ...EXCEL.EXE" /e "%1" ; turn off "use DDE"   More Info   MY NOTES   

###.000E+00 for engineering notation

format painter hotkey macro:
Sub ExecFormatPainter()
Application.CommandBars.FindControl(ID:=108).Execute
End Sub   
another technique

search for 'keyboard shortcuts' in the answer wizard of excel 2000    office & excel 2003 shortcuts    more excel shortcuts

outlining and summaries    my summaries example      

sample worksheet with cumulative % histogram   
sample worksheet showing green lines like continuous feed computer paper

very cool excel addins       editing hyperlinks in cells with code   2   3         Excel Web App   and example

Paste into Non-sequential Cells in Excel
You can use the CTRL key for multiple selections. Sometimes, you want to copy a formula or piece of data into a series of non-sequential cells in Microsoft Office Excel. You can do this quickly without having to paste into each cell individually. 
1. Copy the data from the source cell. 
2. Hold down the CTRL key as you click to select each destination cell. 
3. After all the cells are highlighted, paste the data by pressing CTRL+V. You have to paste only once.

Type data into a series of cells simultaneously
1. While holding down the CTRL key, click all the cells that you want to type the same text (or value) into. 
2. Type the entry, and then press CTRL+ENTER. The text will be added to all the selected cells.

Customize how you sort your records by using a filter. 
To activate a filter: 
1. In Microsoft Office Excel 2007, click Filter on the Data tab.
2. To choose your sorting options, click the filter arrow on the column that you want to sort, point to Sort by Color, and then click Custom Sort.
3. Create a customized sort order. 
a. In the Sort dialog box, under Column, click the drop-down arrow next to Sort by, and choose the field that you want to be sorted. Values should appear in the Sort On field. 
b. In the Order field, click Custom List. This will open a window that offers an entirely custom sort list. 
c. In the left pane, list the values (each separated by a comma) in the order that you want them to be sorted, and then click Add. 
d. In the right pane, select the list you've created, and then click OK. 
e. In the Sort dialog box, click OK.
From now on, your records will be sorted according to your customized list.

See all sheet names:
Right click on any of the buttons to the left of the tabs with the sheet names.  Choose "More Sheets" to get a list box with all sheet names.

Conditional Formatting

to see if there is a gap of more than one between two adjacent vertical cells:

1) select 2 particular cells

2) choose Home->Conditional Formatting->Manage Rules

3) click New Rule

4) choose Use a formula to determine which cells to format

5) if the 2 cells happened to be C2 and C3, enter the formula "=($C3-$C2)>1" Note no $ before numbers

6) change the format to red or yellow or whatever

7) click OK

8) change the "Applies to" range to whichever cells you are interested in

undeletable macros: 
for macro in Personal.xlsb that can't be deleted, choose View->Unhide (in Window box) and select personal.xlsb, then delete the macro
In Windows Vista, the Personal.xlsb is stored at C:\Users\user name\AppData\Local\Microsoft\Excel\XLSTART

Moving chart elements: [origianlly from here - in archive.org here]
To reposition your chart title

• Click the Chart Title text box

• Move the cursor to the border of the text box so it displays a four-headed arrow

• Click and drag the text box to the desired location

• Release the mouse button
  The chart title is repositioned.

Custom autofill lists
If you have a series of words or names that you frequently enter by hand, create a custom autofill list.
Click on File, then Options, and then Advanced. Scroll down almost to the foot of the Advanced menu. Under the General heading, click Edit Custom Lists..., and in the Customs List dialog, click New List and enter your list of words.

Transpose a table into a copy
Select a table that you want to transpose; move the cursor to a cell in a blank part of your worksheet. On the Home tab, click the down arrow below the Paste icon, move to the third icon under Paste Values. A tooltip tells you that the icon is labeled Transpose. Click on it, and a transposed version of the original chart gets pasted in. Unfortunately, the two charts aren't linked. If you change data in one chart, nothing gets changed in the other. To create linked transposed charts, read on.

Duplicate table transposed and linked
The same table in two parts of your worksheet, with one of them a transposed version of the other, and with any data that you change in the first version instantly changed in the other: This takes a bit of forethought, but it's easy when you get the idea. Select a table; count its rows and columns—for example, if the table is at A1:H11, then it has 11 rows and 8 columns. Select a blank region of your worksheet with the dimensions transposed—in this example, 8 rows and 11 columns. In the upper-left cell enter the formula =(TRANSPOSE(A1:H11), but with the addresses of your actual table. Press Ctrl-Shift-Enter, which is the little-known Excel keystroke that creates an array formula. Excel will put curly braces around the formula to indicate that it's an array formula. Any change you make in the original table will also appear in the transposed version, but you can't make changes directly to the transposed version.
- To remove the transposed table, you have to select the whole thing—and that can be tricky if you don't remember the exact dimensions of the array. The answer is to put the cursor in the array, press Ctrl-G to bring up the Go To dialog, click Special..., and click Current Array.

Limit data types in a cell  (c.f here)
Prevent entering the wrong type of data by using Excel's Data Validation feature. In a table, select the cells that should only contain one kind of data. On the Table Tools tab, click Data Validation and specify the kind of data that can go into the cells. How do you alert the user who tries to enter the wrong data?

Format more than one sheet in a worksheet exactly the same way
Excel's grouped worksheets feature makes this easy. Ctrl-click the tabs of the sheets that you want to group together, and the grouped tabs will all turn white. (Normally, only the current sheet has a white tab, while the others are gray.) While sheets are grouped, anything you enter in one sheet also gets entered into the others. In the example shown here, I've applied blue background fill to a row in one sheet, and the same color gets applied to the same row in the other sheets in the group. After formatting grouped sheets, and perhaps entering the same headings in each, remember to click on the tabs to ungroup them, so that you don't accidentally insert or remove data in multiple sheets when you insert or remove data in one.

Save Workspace
View menu and click Save Workspace near the right side of the Ribbon. In the Save Workspace dialog, enter a name and location for your saved view settings

Negative number formatting
Excel's built-in conditional formatting gets new powers in Excel 2010. Now you can apply one of the pre-built color-coded conditional formatting options to data that includes negative numbers (not only positive numbers, as in Microsoft Office 2007). This can give you quick graphic clues to the way in which profits and losses, for example, fit into a pattern that's easier to detect graphically than by looking at a column of numbers. You set this up this kind of conditional formatting by clicking Home, Conditional Formatting, Data Bars, and then choosing a color set in the Gradient Fill gallery

continuous form paper green lines
Select cells of interest, Conditional Formatting->New Rule->Use a formual to determine which cells to format. Enter formula
=MOD(ROW(),2)=1
and select the desired color.  Replace ROW() with COLUMN() for vertical stripes
THESE SETTINGS LOOK DECENT:
=AND(MOD(COLUMN(),2)=1, MOD(ROW(), 2) = 1)  // RGB (146,220,195) FOR OPTIONAL AQUA CELLS AT INTERSECTIONS
=AND(MOD(COLUMN(),2)=1, MOD(ROW(), 2) = 0)  // RGB (220,230,241) FOR VERTICAL DRAB PERIWINKLE COLUMNS THAT APPEAR TO BE IN LAYER UNDER ROW HIIGHLIGHTS
=MOD(ROW(),2)=1 // RGB COLOR (216,228,188)  FOR GREEN HORIZONTAL HIGHLIGHTS; NEEDS TO BE BOTTOM ELEMENT IN LIST

count unique values    count occurances of values     

pivot charts & tables

to get a graph showing frequency of text entries in a column:   c.f. here

1. add a heading to the column.
2. select the data including the column, choose Insert->Pivot Chart, click OK button of the dialog
3. In the new sheet, check the checkbox next to your header in the “Choose fields to add to report” area of the PivotTable Field List on the right side. 
4. Drag and drop the checkbox from step 3 to the “Values” area of the PivotTable Field List on the right side.  The counts now appear next to the text entries
5. Optional: To filter the data, click on the Dropdown list in the lower left corner of the chart
1. Label Filters are for filtering out particular bars whose text matches a particular pattern
2. Value Filters are for filtering out bars that have a particular number of entries

 pivot table to count occurances        pivot tables 

Slicers

2022-03-02

Slicers make it easier for non technical people to see data is being filtered by putting up a dialog

to avoid Existing Connections dialog (that appears when the legacy slicer is selected):
make a table with Insert->table,
select a cell within the table
use table design->Tools->Insert Slicer

don’t use the legacy one:

Microsoft help page on slicers Existing Connection dialog

Templates

green lines template
similar to old green lined printer paper but different colors for columns too
uses Home->Conditional Formatting->Manage Rules

last updated:    Fri 2024-01-19 4:59 PM