The handling of formulas, e-mail addresses and formatting always causes trouble when working with Excel. We have summarized useful advice. […]
If Excel users are at a loss, KPMG’s auditors know better. You have put together the following suggested solutions.
1. Copy and paste an Excel spreadsheet as an extended meta file: This feature helps to avoid different letter and number sizes. It is important that you set the correct table size beforehand.
- Copy an Excel spreadsheet;
- Press Shift key and go to “Edit”;
- Copy/Select image;
- switch to Word or PowerPoint;
- insert the content via the “Edit” menu item.
2. Drag data with the right mouse button: If you simply want to create links or hyperlinks, proceed as follows:
- Select a data area;
- drag with the right mouse button;
- choose from the possible options.
3. Format imported numbers as text: This tip will help in working with cells that are numbers.
- Write the number “1” in an empty line;
- Copy a cell;
- Select cells in which the numbers formatted as text are located and click on “Edit” – “Insert content;
- Select “All”, multiply and click OK.
4. Drag formula down: Sometimes Excel is just too fast if the user makes the “Drag Formula down marker” selection too far down. Instead:
- Click on the first line and copy;
- Go to the next line, highlight and press “Shift”;
- Use the arrow to go to the desired cell and release “Shift;
- Click “Enter”.
5. Select hyperlinks in Excel spreadsheets without executing them: If you want to select a cell with a hyperlink without executing it with a mouse click, the following trick is recommended:
- Click on the cell with the mouse and hold down the left mouse button a little longer.
6. Changing cell references in formulas: If you want to change the cell references from “absolute” to “relative”, the following procedure is recommended:
- Select a reference in the formula bar;
- Press the “F4” key repeatedly until the cover fits.
7. Annoying green triangles in Excel: You are bothered by the small green triangle in the upper left corner of the cell? Then do the following:
- Click on “Tools – Options – Error Checking”;
- Uncheck “Unlocked cells contain formulas”.
8. Do not turn an email address into a hyperlink: Normally, Excel simply converts an entered e-mail address into a hyperlink. How to prevent this:
- after confirming the entry with “Enter”, immediately press the “Undo” button or “CTRL+Z”.
9. Reverse the search direction in the Excel spreadsheet: Usually you search from top to bottom via “CTRL+F”, which can sometimes be annoying. Therefore, proceed as follows to reverse the search direction at any time:
- Enter search term;
- Press (and hold) the key combination “CTRL+Shift”;
- click on “Continue Searching”.
10. Creating a Datasheet Diagram: To get a quick overview of the development of the number series, do this:
- Mark the data area;
- Press “F11”.
*Sascha Alexander has been working for many years as an editor, specialist author, press spokesman and expert for content strategies in the market for business intelligence, big data and advanced analytics. Stations included the market research and consulting company BARC, the “Computer Week” and the portal and magazine for CFOWORLD CFOs founded by him. His main topics are: Business Intelligence, data warehousing, data management, big Data, advanced Analytics and BI Organization.