Excel tricks from the daily life of a buyer – part 1 of 3
Most of us use it as a tool for everyday work: Microsoft Excel. For one curse, for another blessing, we use the software to a very different extent, for example, to collect information within a simple overview table, to design a form, to display dashboards or to compile data for analysis as part of preparing a tender.
For this reason, we have decided to compile a blog series with the most important functions and formulas that might make your life easier. In this first part we will deal with the use of shortcuts and the topic of automatic saving.
Of course, you can use the mouse in Excel as well as anywhere else. However, it is often the case that switching between mouse and keyboard leads to delays in the process and it is often advisable to train shortcuts for a smoother workflow. Copy (CTRL+C) and paste (CTRL+V) belong to the computer basics and are well known to most people. In addition, Microsoft offers a wealth of further key combinations. These can be used to navigate within workbooks as well as for other functions. Please note that the following list is intentionally only an excerpt of the shortcuts we use regularly and does not claim to be complete.
CTRL+pict Switch between table sheets to the right and left
CTRL+A Marking of the entire worksheet
SHIFT+arrow Marking of several contiguous cells – as long as you hold down the SHIFT key, you can extend the selected range as you like
CTRL+arrow If current cell is empty: Navigation to the next filled cell in direction of the arrow
If current cell is filled: Navigation to the last filled cell in direction of arrow
à A combination of SHIFT+CTRL+arrow to mark areas is possible
CTRL+plus (+) Opens the dialog box „Insert Cells“
CTRL+minus (-) Opens the dialog box “Delete Cells”
CTRL+1 Opens the dialog box “Format Cell”
CTRL+T Opens the dialog box “Create Table”
SHIFT+F2 Adds a note or opens an existing note for editing
CTRL+ALT+V Opens the dialog box “Paste Contents” to paste content from the clipboard, e.g. without formatting or „values only”
F2 Edit the content of the cell (analog double-click)
ALT+ENTER Line break within a cell
The following combinations are not exclusively related to Excel but can also be used in other Microsoft applications such as Word or PowerPoint.
CTRL+S Saves the file
CTRL+F Opens the dialog box “Find and Replace”
CTRL+Z Undoes the last action
CTRL+Y Repeats the last action
CTRL+P Opens the „Print“ menu
SHIFT+F10 Opens the context menu (analog click with right mouse button)
Automatic saving (Office 365)
This is not an Excel peculiarity, but an Office-wide function, that should also be mentioned in this blog. Many users of Office 365 have recently stumbled upon the fact that after an update, automatic saving is activated by default in the Office products. This means that if you open a file just for a briefly look up, change something by mistake and then close the file, the change is automatically saved without the program asking for confirmation.
For some people this change may be an improvement, as it reduces the risk of data loss due to not saving when the program is closed. It is also possible to restore old versions to undo changes. For other users, however, who have relied for years on Office not saving files unless the software was told to do so, this is a major source of disruption.
These users have the possibility to deactivate the default automatic saving in the options of the menu „Save“. To do this, uncheck the checkbox „Automatically save OneDrive and SharePoint online files to „Excel“ by default”. For other programs such as Word, PowerPoint or Access, the deactivation has to be carried out separately in the respective software.
In our next blog on Excel hacks, we’ll cover the topics of the status bar and monitoring windows and give a brief insight into how using recorded macros can save you work.
Consultant, ADCONIA GmbH