Excel software can be used at home and in the office. Most of the time, it is used to make simple tables with pretty graphics but it allows to do an infinity of tasks. Today, we offer you 10 tips to save time if you often use the most famous of spreadsheets.
>> Download Excel
1) Select a range up to the last cell.
You are on the second line of the spreadsheet and you want to select all the cells down to the bottom of the workbook? Rather than using the mouse, which can be long and imprecise if your file has very many lines, use the key combination CTRL + SHIFT! Keep these two keys pressed then press the down arrow on your keyboard, the selection will then extend to the last non-empty cell in the spreadsheet.
Note: This manipulation also works horizontally with the combination CTRL + SHIFT + null.
Finally, to select all the cells of a row containing data. The use of the two manipulations in succession (CTRL + SHIFT + null then CTRL + SHIFT + null) will allow you to select the entire spreadsheet.
2) Copy / Paste multiple lines in a single cell
If you use Excel as a directory for example and you tried to paste an address in a cell, most often, this one was pasted on several cells / lines. To copy several lines in a single cell, double-click on the cell before pasting your lines, they will all be pasted in this cell directly.
3) Fix a cell in a formula
Do you ever "pull" formulas? Sometimes you want to have the same reference cell for all your calculations, for example when converting currencies. To do this, you must insert the $ character in front of the cell to be fixed in your formula (example $ C $ 4). To save time, in your formula, go to the cell concerned in your formula and press F4, the $ will be automatically added.
4) Keep the reproduction of the layout
You often use the roller to copy formatting, but the formatting disappears as soon as you apply the style to a cell or group of cells. To keep the formatting in the clipboard, double-click the roller. You can now chain style collages.
5) Paste without losing style or formatting
This tip concerns the use of Excel with other Office suite software (Word, PowerPoint, Outlook, etc.). When you copy your tables to another software, the formatting jumps. The simplest solution is to paste as an image, most of the time, your contacts do not need to recover the data, only to view them.
6) Double check
If you do accounting, mistakes can be expensive. A simple risk mitigation solution is to use conditional formatting. Create a formatting that checks that the sums calculated horizontally are equal to the sums calculated vertically. If a formula skips for any reason, the total cell will automatically be colored to alert you.
7) Repeat the lines when printing
Excel sometimes involves printing listings to point them out. You can repeat the row headers on each printed page. From the Layout tab, select "Print titles". In the "Rows to repeat at the top" field, click on the arrow, select your row containing the labels for your columns and press Enter to validate.
8) Import your bank accounts
Most banks offer to download your accounts in QIF, CSV, TSV or TXT format. These datasets are made to be used by Excel. From the Data tab, click Text file and open the file. An assistant will then help you import your data.
- CSV files use comma or semicolon as separator
- TSV files use tab as separator
- Other types of files are likely to use these same separators or others like spaces.
Once the wizard has passed, you will get perfectly usable and formatted tables.
9) Open 2 Excel windows
When working in Excel on a daily basis, it is not uncommon to alternate between two files. Excel opens by default all files in the same window, to open a second Excel window, right click on the Excel icon in the taskbar and click on Excel. The software will launch a second time allowing you to display the two windows side by side.
10) Macro Filter - Freeze
Finally, after all these years, Microsoft still does not allow filtering and freezing in a single operation, but macros yes. Go to View then Macro, type a macro name (FilterFreeze for example) then click Create.
Copy and paste these lines between Sub and End:
Filter Freeze Macro
Rows ( "1: 1") Select.
Selection.AutoFilter
With ActiveWindow
.SplitColumn = 0
.SplitRow = 1
End With
ActiveWindow.FreezePanes = True
Don't miss any promotions!
Receive the best offers from the ParisianI'm registering
Your email address is collected by Le Parisien to allow you to receive our news and commercial offers. Find out more
Then close the window and return to "Display" then "Macro", click on Options and assign the "N" key on your keyboard to the macro before confirming. Now when you press CTRL + SHIFT + e, the first line of the workbook will be automatically frozen and filtered.
>> Download Excel