You can learn how to create drop downs in excel. In addition, you can also learn how to allow unlisted entry into
the drop down field.
—————————————————————————————————————————————————-
Chaitanya is CEO of p2w2, a Spreadsheet Solutions company. p2w2 has expertise in Excel Modeling, Excel Dashboards, Profitability Analysis, Excel Invoices and
Excel Bid sheets. You can contact us by email: cs [at] p2w2.com or call us at 305.600.0950.
We use copy paste (Ctrl+C and Ctrl+V) commonly to copy data. But surprisingly, there are 17 different variations to paste data in an excel sheet! To Paste Special click on arrow below Paste and click Paste Special.
Hit Alt + E (Edit) followed by S (Paste Special) to display the paste special box. Select the appropriate options in the box.
Select the data to be copied and click Copy on Home tab.
To Paste Special, select the destination and click the arrow below Paste button on Home tab. Click Paste Special on the drop-down menu. A Paste Special dialog box appears.
Select appropriate options in the dialogue box.
Pastes everything (formulas, formatting, and text) . (Same as Paste).
Shortcut: Hit Alt + E (Edit) followed by S (Paste Special) and A (All) or simply Ctrl + V.
It enables pasting just the formulas ignoring everything else. This is useful, because it retains the formatting and comments of the destination cells.
Shortcut: Hit Alt + E (Edit) followed by S (Paste Special) and F (Formulas).
It Pastes only values without formatting. It doesn’t matter if the source cell has a formula in it. If you wish to paste data from one worksheet to another that contains links, this option is useful to
avoid links to another file.
Shortcut: Hit Alt + E (Edit) followed by S (Paste Special) and V (Values).
This option enables to paste only the formatting (and conditional formatting) from the copied cells to destination cells. This is useful if you have content formatted in a certain way that you would like to copy to another location.
Shortcut: Hit Alt + E (Edit) followed by S (Paste Special) and T (Formats).
This option enables pasting only comments from the copied cell to the new cell, without pasting any of the formats or data from the source cell. This option saves you the time of re-typing comments, while preserving the data and formats in the destination cell.
Shortcut: Hit Alt + E (Edit) followed by S (Paste Special) and C (Comments).
When creating Data Validation rules, you might want to apply those rules to other cells. You can do that with this option without copying any source data or formats, and preserving the destination data and formats.
All using Source theme:This option pastes all the information from the source cell (numbers, formulas, text, formats, comments, etc.) and applies the source worksheets theme’s colors, fonts and graphics to the destination.
All except borders:This option pastes numbers, formulas, text, comments and formats except borders from the source cell in the destination cell.
Column widths:It allows you to copy one column’s width or more and apply it to whichever columns you select. If your spreadsheet has one wide column of data, and you want that wide column look normal, select the normal column and paste Column Width, so that all columns are of equal width.
Formulas and number formats:This option pastes only the formula from the source cell and number formats applied to that cell. It only pastes number formatting, such as percentage, currency, accounting, scientific, etc., and other formats like border, fill color, font, etc., are not pasted.
Values and number formats: This option pastes the same information as the Formulas and number formats option above, except it only pastes values and not formulas. This is similar to the Values option discussed above with the addition of the number formatting.
The Operation section allows you to perform a mathematical operation with the data you have copied.
For example, let us assume that a source cell contains number 5, and the destination cell contains number 10.
Let us assume that the “All” option is selected from the Paste section with Skip blanks and Transpose left unchecked.
None:This operation performs no arithmetic, and therefore resulting number in the destination cell will be 5.
Add:By selecting 5 from the source cell and pasting it into the destination, which contains 10, Excel adds 10+5 to return 15.
Subtract:Excel subtracts 10-5 to return 5.
Multiply:Excel multiplies 10*5 to return 50.
Divide:Excel divides 10/5 to return 2.
There are three more options on the menu: Skip blanks, Transpose and Paste Link.
This option works just as it says. The Skip blanks option tells Excel to paste only where there is data in the source cell. If the cells are blank, Excel will skip over them and not paste anything, which is why cells B4 and B5 had the values of 800 and 900 respectively instead of being blank.
The Transpose option takes a copied list of numbers from a column and pastes them across a row, or copies a list of numbers from a row and pastes them down a column.
If you want your destination cell to link to the source cell you can use this option. In cell C2, you can see the formula ‘=$A$2’. If you select multiple cells to paste, it won’t have the dollar($) signs. Paste Link option saves a lot of time if you’re working with multiple worksheets or multiple files.
—————————————————————————————————————————————————-
Vani is a Business Associate with p2w2, a Spreadsheet Solutions company. p2w2 has expertise in Excel Modeling, Excel Dashboards, Profitability Analysis, Excel Invoices and Excel Bid sheets. You can contact us by email: cs [at] p2w2.com or call us at 305.600.0950.
A feature of Microsoft Excel ‘text to columns’ option under the Data menu allows information in a cell or range of cells to be split into different columns. We find this tends to occur on exported lists of address information.
Below is the data in column B which is to be split in two different columns:
Select the range of data you need to work at.
Select Data from menu and select Text to
Columns
in the Data Tools group.
Check Delimited in the dialog box (1 of 3) and click Next.
In Step 2 of 3, check the box related to the specific separator in the information.
If the separator is tab, check tab or if the separator
is semicolon, check semicolon and so on.
If the separator is other than the specified check boxes given in the wizard, check Other box and mention the separator in the box for Other:
Select Other in the dialog box (2 of 3) and enter the delimiter used in your data (-) in the box for Other: and click Next.
Enter the Destination (C3) to paste the data after splitting and Click Finish.
Click Ok in the dialogue box.
Shown below is the output:
—————————————————————————————————————————————————-
Vani is a Business Associate with p2w2, a Spreadsheet Solutions company. p2w2 has expertise in Excel Modeling, Excel Dashboards, Profitability Analysis, Excel Invoices and Excel Bid sheets. You can contact us by email: cs [at] p2w2.com or call us at 305.600.0950.
In Excel, macros are disabled by default for enhanced security. Below are the steps to
Enable Macros in two different versions of Excel:
Excel 2007 version
Excel 2010 version
The picture below shows a Security Warning in a worksheet in which macros are disabled.
Click Options button.
Check Enable this Content and Click Ok in the Security Alert-Macro box.Now the macros are enabled.
The
picture below shows a Security Warning in a worksheet in which macros are disabled.
Click Enable Editing button.Just by clicking the Enable Editing button, Excel is ready to enable all the macros.
—————————————————————————————————————————————————-
Vani is a Business Associate with p2w2, a Spreadsheet Solutions company. p2w2 has expertise in Excel Modeling, Excel Dashboards, Profitability Analysis, Excel Invoices and Excel Bid sheets. You can contact us by email: cs [at] p2w2.com or call us at 305.600.0950.
When we work
with Excel, many times you encounter a situation where you need to remove duplicate records/data, in the following article we tried explaining you ‘How to remove duplicates’ hope this articles helps you!
Data before removing duplicates
Data after removing duplicates
Copy your range of data to a blank section of the worksheet.
Name every column in your data set.
Select a cell in your data set.
From the Data menu, in the Data Tools group choose Remove Duplicates.
Excel displays a dialog box, for selecting the columns that need to be considered. Check the boxes of required columns and check My data has headers box if the data has headers included.
Click OK.Excel deletes the duplicate records from your dataset. It also displays the number of records removed and the number of records remaining in the sheet.
Click Ok.
Below is the data after removing duplicates.
—————————————————————————————————————————————————-
Vani is a Business Associate with p2w2, a Spreadsheet Solutions company. p2w2 has expertise in Excel Modeling, Excel Dashboards, Profitability Analysis, Excel Invoices and Excel Bid sheets. You can contact us by email: cs [at] p2w2.com or call us at 305.600.0950.
