tips: copy and paste as value/formula/transpose(row to column, column to row)
Sheet and cell: =Sheet1!$A$2:$A$8
1.How to convert column list to comma separated list in Excel?
Dim rng As Range
Dim InputRng As Range, OutRng As Range
xTitleId = "KutoolsforExcel"
Set InputRng = Application.Selection
Set InputRng = Application.InputBox("Range :", xTitleId, InputRng.Address, Type:=8)
Set OutRng = Application.InputBox("Out put to (single cell):", xTitleId, Type:=8)
outStr = ""
For Each rng In InputRng
If outStr = "" Then
outStr = rng.Value
outStr = outStr & "," & rng.Value
OutRng.Value = outStr
2.How to quickly apply formula to an entire column or row with/without dragging in Excel?
here I recommend copy the formula and then select a starting cell and scroll to the last cell you want to apply and hold down shift+select the last cell, paste,
because for excel’s nature of infinite scrolling in vertical direction, if you do fill down to apply formula, it may apply to lots of empty cells you don’t want to apply to, that will cost high memory usage, and in some case excel may get crashed.
How to remove prefix / suffix from multiple cells in Excel?
3.Use AutoSum to sum numbers
4.Find mismatch value comparing two column
7. Match Index
quickly find corresponding values based on comparing the other columns.
8.Transpose/convert multi column to single column
column to table: http://www.cpearson.com/EXCEL/ColumnToTable.aspx
Scatter Chart – Format Data Label – Label options – select Range
How to use SUMIF in Excel – formula examples to conditionally sum cells https://www.ablebits.com/office-addins-blog/2014/11/04/excel-sumif-function-formula-examples/
10. Group and Subtotal
11. value (long number string or date time) as Text
method 1: import as csv, select the column, change the type to text
method 2: Text()
12 group by sum
=IF(A2=A1,””,SUMIF(A:A,A2,B:B)), (A2 is the relative cell you want to sum based on, A1 is the column header, A:A is the column you want to sum based on, the B:B is the column you want to sum the values.) Press Enter key, drag fill handle down to the cells to fill the formula.
#to prevent possible loss of data excel cannot shift non-blank cells off of the worksheet
remove the formats
#before copy paste, make sure that you want to copy the function or value only, otherwise excel by default will apply functions if you have to all cells you paste to, that will cost much longer time and more memory
#stop excel converting the value
1). Change the file extension from .csv to .dif
2). Double click on the file to open it in Excel.
3). The ‘File Import Wizard’ will be launched.
4). Set the ‘File type’ to ‘Delimited’ and click on the ‘Next’ button.
5). Under Delimiters, tick ‘Comma’ and click on the ‘Next’ button.
6). Click on each column of your data that is displayed and select a ‘Column data format’. The column with the numeric value should be formatted as ‘Text’.
7). Click on the finish button, the file will be opened by Excel with the formats that you have specified.
#whitespace, trim not work ?
remove stubborn spaces and characters in a cell
DATA->Text to Columns