1. Defining named tables.
You can define a range as a table and give a name to it. Later you can use this name in a formula. You can also use these named tables in references.
Under formulas menu bar, select Define names. Then give a name to the tables, for example, Sales. Then select the range and select ok. After you create the named table, you can use the table name 'Sales' in the formula to calculate the sum.
6. Text to column
Suppose you have a bank statement in PDF format. If bring the statement into excel you will have a lot of options to analyze it. One example would be a BRS, Bank Reconciliation Statement. Import the statement to excel and apply formulas on the imported statement.
You can split the single line into multiple columns based on a specific delimiter. In the below example list folders and files has been extracted to a text file and it contains columns like date and time, type of the file and a name of the file and folder. Each of these columns can be split into separate columns in the excel. Once the data is in tabular form, it can be used for various analysis. In this case, you can sort the files based on their creation date or based on the names etc.
7. Autosizing a column width or row height.
By clicking on the line between 2 rows or a column, you can auto adjust the width of column or height of a row based on the content of that column or row.
|Ctrl + Space||To select the entire column|
|Shift + space||To select an entire row|
|Ctrl + Shift + Space:||The First click will select the filled range surrounded be the current cell. Second space press selects the entire worksheet.|
|Ctrl + shift + +||Insert a new row or a column|
|Ctrl + -||To delete a row or a column|