Whether professionally or privately, many work more or less regularly with Excel. But certain processes when creating an Excel file are repeated. Wouldn’t it be wonderful if there were ways to do this faster?
Luckily there is. Because Excel comes with many functions that make working with the program much easier. No more stupid copying of any values that then have to be pasted into individual cells in a time-consuming manner. TECHBOOK presents 11 helpful tips for using Microsoft Excel.
Create line breaks in Excel spreadsheet
The Enter key is extremely annoying, especially when you are typing longer text with paragraphs into a cell. When pressed, the cursor moves to the next cell. If you want to create a line break, press the keys Alt & Enter. If you have an Apple computer, please enter the key combination Control & Option & Return to press.
With longer texts, it makes sense to always define a line break for cells. Set it up as follows: Right-click, select “Format Cells”, click the “Alignment” tab, check the “Text Control” option for “Wrap”.
Expand status bar
The ability to add some data to the status bar is also very useful. The status bar is the area below the table. A list appears when you right-click. Simply tick the values that should be displayed in the status bar below. This saves you unnecessary formula calculations.
Also read: How did Microsoft come about? The history of the technology giant
insert date
Time is always precious. It helps if, for example, the current date does not have to be typed in, but is automatically inserted using a key combination. For that easy Ctrl & . to press. So you don’t even have to know what day it is.
The current date also appears through the formula =Today(). You create the date and time by entering =Now().
Sort & Filter
With larger databases, the overview is quickly lost. Excel offers a sorting function in the “Data” menu item. Here data can be sorted according to certain criteria, for example numbers according to size and name or designations according to letters.
Once sorted, the data can be further filtered. This can be done in the same tab by clicking on “Filter”. A drop-down menu appears in the cell. There you can filter the values according to your needs, for example hide empty columns or data that is not required. Important: No content will be deleted in the process.
Hide cells in Excel
In order to keep an overview in the mass of data, rows can also be hidden in an Excel spreadsheet. Simply select rows or columns, click the right mouse button and select “Hide” from the menu that appears. In this way, the database remains untouched and the focus is on the essential data.
Also Read: 25 Windows Keyboard Shortcuts You Should Know
transfer format
You have formatted a cell and want to apply that format to any number of other cells. To do this, mark the formatted cell and select the “Transfer format” function in the “Start” main menu item. Then simply click on the cells to be formatted. As if by magic, the cells transform into the new format.
Remove empty cells
There is a simple Excel function to remove empty cells in one fell swoop. Especially when data and information from other programs are transferred to an Excel spreadsheet, such unsightly gaps appear. To do this, go to “Start” – “Search and Select” – “Select Content”, click and select “Empty Cells” in the menu window. Excel then displays the empty cells with a colored background. Right-click an empty cell. Here you can decide whether the other cells should be moved up or to the side after deletion.
text in columns
For example, if you want to create and continue an address list for the club in Excel, but the data record comes partly from another program, you must first export this data from the third-party program, for example in CSV format. Depending on the program, the individual data in CSV format are separated by commas, semicolons, tabs or spaces. This is something you need to be aware of when importing into Excel. To do this, select the sub-item “Text in columns” under the menu item “Data”. Click on this dialog and select the appropriate separator. Excel then shows a preview of how the data will be distributed across individual columns. If everything fits, click on “Finish”. You can now continue to maintain the address list in Excel.
remove duplicates
Especially when data records are maintained by different team members, it is very easy for duplicate entries to occur. This is particularly annoying for address distributors. Duplicates also arise when, for example, several distribution lists are combined in an Excel spreadsheet. So that you don’t have to laboriously go through line by line, Excel has the “Remove duplicates” function. This point is in the “Data” area and there as an icon in the “Data Tools” area. To avoid deleting incorrect entries, you should first sort the columns alphabetically. Only then click on “Remove duplicates”. In the following menu you select which columns should be checked for duplicate entries. Confirm the selection by clicking on “Ok”. Excel will then tell you the number of duplicates. If everything is OK, confirm the removal by clicking the “Ok” button.
Also read: Sign digitally – edit documents easily and directly on your PC or smartphone
round numbers up
With large numbers, it is often easier to work with rounded values. If there are a lot of numbers, use a simple Excel function. To do this, right-click on a cell with a number. Select the menu item “Format Cells” and then “Custom”. If you want to hide whole thousands and round up the number, type the combination in the “Type” field ###.”k”. Then, for example, the number 33,967 in the display becomes 34k. It works similarly with numbers in the millions. The combination applies here ###..”m”. For even larger values, you must add a period after the # sign for each additional thousands digit.
Same content in multiple cells
Our last tip is about pasting the same data into multiple cells. This makes sense if, for example, product labels have to be printed. Of course, you can fill each cell with Copy&Paste. It is more clever to mark and define the cell area beforehand by dragging it with the mouse. Then type the product name in the formula bar and click Ctrl & Enter. All cells in the previously selected area are automatically filled with the name or text.