Activate macros in Microsoft Word and Excel
PowerPoint: Edit the Excel link without problems
Anyone who works with Microsoft Office 2021 or Microsoft 365 has probably heard about it: you should activate macros to be able to work more effectively. Microsoft Word and Microsoft Excel in particular are supposed to benefit from this. But many people don’t know what these ominous macros actually are and how to activate them. For all those, here are our instructions.
What are macros in Microsoft Word and Microsoft Excel?
Basically, macros are commands on the PC that enormously facilitate and accelerate work processes that are carried out again and again. This means that macros in Microsoft Word or Microsoft Excel, for example, save these commands and automatically make them available as needed with a simple click of the mouse. Such recurring instructions or commands are executed together with the help of macros either via the mouse or via a key combination. In Excel spreadsheets, for example, data can be quickly organised, formatted and imported by clicking on a defined symbol or using a specific key combination. Or constantly recurring phrases in correspondence, such as „Yours sincerely“, do not have to be typed under the letter, but can be inserted automatically under every letter with a macro. A text that has been written once or a sequence of frequently used commands are therefore automatically recorded or executed by the macros. In order to be able to do this, however, they must first „learn“ which work steps they should save. You must therefore first activate the macros in order to work with them.
Activating Macros in Microsoft Office 2021
First you decide whether you want to activate Excel macros or Word macros. Accordingly, open the desired programme in Microsoft Office 2021 or Microsoft 365 and proceed as follows:
- In Microsoft Word or Microsoft Excel 2010, go to the File tab.
- Select Options.
- Go to the section Security Centre (Trust Center).
- Click on the button Settings for the Trust Center.
- Now go to the section Settings for Macros (Macro Settings).
- Click on Activate all macros (Activate VBA macros).
- Confirm changes with OK.
Now the macros can be programmed in Microsoft Excel or Microsoft Word to make work easier. To create macros, click on „View“ at the top of the Excel menu bar – on the far right you will find the macro area where you can create new macros with the function „Record macro…“. You can define each macro with the following properties:
- Macro name
- Key combination with which you want to trigger the macro
- Save macro in
- Description (optional)
Activating and programming Excel macros
First, the macros in Microsoft Excel must be activated as described above. To programme the individual automated commands, the following steps are still carried out:
- Click on „View“ in the Excel menu bar at the top.
- Create new macros on the right in the macro area with the function „Record macro…“.
- Each individual macro must be defined with certain properties
- Name of the macro
- Key combination that is to activate the macro
- Storage location for the macro
- If desired, description of the macro
Activate Word macros via button
Word macros can be activated not only via a specific key combination, but also via a defined button:
- Go to the „View“ tab in the menu bar and click on the symbol for „Macros“.
- In the context menu, select the middle option „Record macro“.
- Now define the „Macro name“.
- The selection „Save macro in“ offers the option to use the macro in all Word documents by selecting „All documents (Normal.dotm)“.
- Now select the option „Button“ under „Assign Macro“.
- Click on the newly created macro („Normal.NewMacros.MacroName“)
- click „Add“.
- With the „Change“ button, the macro can now be given a symbol and a suitable name.
- Confirm with „OK“.
From now on, the symbol given to the macro appears in the button above the menu bar. If you now want to activate this or several macros, all you have to do is click on the corresponding symbol at the appropriate place in the text.
Header Image: © Gorodenkoff, stock.adobe.com
Link Outlook, Word and Excel
Link Outlook, Word and Excel - this is how it works!
In this article we show you how to link Microsoft Outlook, Word and Excel. Whether for private or business use – the Office Suite on the PC facilitates many work processes and enables a clean presentation of a wide variety of documents. Microsoft Word can be used to create formatted texts, Microsoft Excel is the top programme for spreadsheets, calculations and data processing. And finally, Microsoft Outlook is used to manage all e-mails and organise appointments with the help of the integrated calendar.
Linking Word and Excel
In everyday office life, it is often necessary to integrate a table into a Microsoft Word document. This can either be created in the programme itself – or simply linked to a table in Microsoft Excel. To do this, first open the corresponding Excel file. Now mark the table or individual sections of the table and copy them with the keyboard command „CTRL+C“. Then open the Microsoft Word document into which the table or parts of it are to be inserted. Now direct the cursor to the place where the table is to appear. With the keyboard command „CTRL+V“ you can now place it exactly there. The disadvantage, however, is that changes to the table can now only be made with the functions of Microsoft Word. It is more convenient to be able to insert an Excel link into Word. How to link Word with Excel:
- First, select the corresponding table in Microsoft Excel and copy it using the shortcut command.
- In the Microsoft Word document, click on the arrow under „Paste“ in the „Start“ tab and select „Paste contents“ in the drop-down menu.
- In the new window, click on „Insert link“.
- Then select „Microsoft Excel Workbook Object“ in the menu window and confirm with „Ok“.
- Changes that are now made to the table in Microsoft Excel automatically appear in the Microsoft Word document.
Linking Microsoft Outlook with Word and Excel
With a Microsoft account, it is easy to harmonise the functions of all three tools. So not only Excel links can be inserted into Word. But tasks, appointments and changes can also be viewed on different end devices by different employees. The first step is to prepare the toolbar for DeepL access.
- To do this, click with the right mouse button on a symbol in the toolbar.
- Select the option „Customise the toolbar for DeepL access“. A new dialogue box opens (depending on the selected programme in Microsoft Word or Microsoft Excel).
- In the entry „Toolbar for DeepL access“ in the navigation bar in the list field „Select commands“, select the item „Commands not included in the ribbon“.
- Click on „Create Microsoft Outlook task“ in the list and activate it.
One of the advantages of this option is that the document sent via Microsoft Outlook remains on the original PC. Before it is sent, it can be „released“ or „locked“ so that editing by others is prevented or made possible. In this way, tasks can be distributed quickly and without effort by switching programmes. It makes sense to distribute tasks, for example, when working on a Word document or an Excel spreadsheet. And the employee needs to be informed about a current change of deadline, for example. Then this new task does not have to be created in Microsoft Outlook first. Instead, it can be displayed directly in the document being worked on. If there are no other tasks associated with the document, it can be sent to all or individual contacts from the address book in Outlook – without having to change the programme. New recipients can also be added at any time using the table function.
Tip: Working with highlighters in Microsoft Word documents
If several people are working together on a Microsoft Word document, it makes sense to mark particularly important text passages with markers. Such bookmarks make it possible to find certain sections of the text quickly, especially in an extensive document or a shared directory that has been shared with Outlook. Previously uninvolved contacts can then continue to work on these as a task, for example, or make changes.
Header Image: © Prima91, stock.adobe.com
Sort Excel spreadsheet automatically - this is how to do it!
Sort Excel spreadsheet automatically - this is how to do it!
The Excel spreadsheet programme – for example Microsoft Excel 2010 in Microsoft Office 365 – is incredibly versatile. The most important function is probably the creation of tables. But depending on the volume of data, this can quickly become quite confusing. To prevent this, it makes sense to let the programme sort the Excel table. There are various options available, which we will explain in more detail here.
Why should you sort an Excel table?
For a good analysis of data, it is necessary to be able to sort them in an Excel table. In Microsoft Excel 2010, various sorting options are available, such as sorting Excel rows or sorting Excel columns. A finer sorting of individual data is also possible. For example, data can be sorted by numbers, by date and time, by format or alphabetically in order to visually capture and understand data more quickly and to organise it effectively.
Sort Excel rows
If you want to sort an Excel table, you often start by sorting Excel rows. This can be done easily in just four steps:
- In an open Excel document, switch to the „Data“ tab and click on the „Sort“ button.
- A new window appears, select „Options“.
- Next, click on the option „Sort rows“ under „Alignment“ and then confirm with „OK“.
- Under „Column“, select a named column for sorting Excel rows and, if necessary, under „Order“, select which value is to be used for sorting the Excel table. Then press „OK“.
Sort Excel columns
- First select the range you want Excel to sort.
- You can now access the sort dialogue via the „Data – Sort“ tab.
- In the „Sort“ dialogue box in Microsoft Excel 2010, select the option „Data“ in the „Sort and Filter“ group.
- In the „Sort“ window click on „Options“, activate „Sort columns“ in the next dialogue box.
- In the drop-down menu under „Row“ now select the row according to which Excel should sort columns and confirm with „OK“.
Sort Excel spreadsheet alphabetically
If, for example, you are dealing with staff or member lists, you can also sort an Excel table alphabetically to improve clarity. The following steps are necessary for this:
- Select the table that Excel is to sort alphabetically. Do not mark the headings of the columns and rows!
- Select the option „Sort“ in the „Data“ menu.
- In the window, select the column that is to sort the Excel table and click on the corresponding option under „Order“.
- Confirm with „OK“.
This is also possible: Move Excel table
Normally, Microsoft Excel 2010 takes over all data including formulas and values and formats when rows and columns are moved or copied. However, the cell reference is not adjusted in the case of a formula in the cell – an error message appears in the moved or copied cell. If this happens, the references must be re-entered manually. Otherwise, however, all columns and rows can be easily moved in an Excel table with the following steps. The simplest way:
- First select the cell, row or column to be moved or copied.
- Now select either „Cut“ or „Copy“ on the „Start“ tab. The key combinations „CTRL+X“ for cutting and „CTRL+C“ for copying are quicker.
- Click with the right mouse button below or to the right of the new position of a cell or column.
- To move, click on „Paste cut cells“.
- To copy, click on „Paste copied cells“. The shortcut via the keys for this is „CTRL+V“.
Move the Excel table with the mouse
If you prefer to use the mouse to sort the Excel table, proceed as follows:
- Select a column or row to move or copy
- Move: Move the cursor to the edge of the selected rows or columns until it turns into a move pointer. Hold the mouse button and drag selected elements to the new position.
- Copy: Hold down the CTRL key and move the cursor to the edge of the selection. When the copy pointer (white arrow) appears, drag the selection to the new position. It is essential to keep the CTRL key pressed!
Header image: © Yu_photo, stock.adobe.com
Unprotect Excel sheets: This is how it works
Unprotect Excel sheets: This is how it works
It makes your hair stand on end: There you are, having painstakingly created a perfect spreadsheet in Excel at last. You send it to the team members or the boss – and they change a value here, move a cell there or even delete the important table completely. All the work was for nothing … To prevent this, individual or even all cells of such a worksheet can be locked and the entire sheet can be secured with a password. But what if you still have to work on it later? Then it makes sense to be able to unlock the Excel sheet. Here we show step by step how this works.
Unprotect Excel sheets with a few clicks
Actually, you can only unprotect Excel sheets if you know the corresponding password (exceptions to this below). If the sheet is protected with a password, it cannot be edited for the time being. In order to make this possible, five steps are sufficient to unprotect Excel sheets: 1. First open the protected file. 2. Then jump to the cash register „Check“ at the top. 3. Find the category „Changes“ and press the button „Unprotect sheets“. 4. Now the password must be entered in the corresponding prompt field. 5. Confirm with „OK“. To protect an Excel sheet with Microsoft Excel again, a new password is created via the button „Protect sheet“.
Microsoft Excel keeps a loophole open
Too bad if you have secured an Excel spreadsheet with a password as a precaution – and then forget exactly that! Do you then have to do all the work all over again and create a new Excel spreadsheet? No! Fortunately, with Microsoft Excel and an additional tool, you can remove the Excel sheet protection without using the password.
How to remove the Excel password
If you want to remove the Excel sheet protection without knowing the password, you first have to download the programme 7-Zip for free and install it on your PC. Once all this has been done, proceed as follows:
- Open the folder with the corresponding Excel file.
- Right-click on the file and open it with the command "7-Zip > Open".
- A folder with the name "xl" is shown, which must be opened.
- Select the term "worksheets" in this folder.
- Copy the file "sheet1.xml" that appears to the desktop.
- open "sheet1.xml" in a word processing programme, leaving the 7-Zip programme open.
- In Excel 2010, search for the term "password" using the "Search" command (key combination "Ctrl + F") (in Excel 2013 and Excel 2016, enter "sheetProtection" as the search term).
- In the Excel 2010 file, the entry with the password is now displayed in inverted commas.
- Delete the password (not the inverted commas!).
- The display must now show password="". (As of Excel 2013, the display must look like this "<sheetProtection>").
- Save the file "sheet1.xml".
- Next, copy the file m 7-Zip archive to the same place where it was before.
- Mark the pop-up option field with "Yes" and close the 7-Zip programme.
- Now open the file with Microsoft Excel.
- Now, as described above, the Excel sheet protection can be removed with a few clicks.
Does this work with the whole Excel file?
That would be too good – especially for people who would have unauthorised access to protected files. That’s why the steps only work if individual Excel sheets are protected with a password.
Help - the button "Unprotect sheets" cannot be activated!
If the button „Unprotect sheets“ is greyed out and cannot be clicked, the document probably has a release protection. In order to be able to edit the document, this must first be removed.
- First click on Check > Unprotect.
- Then release the Excel spreadsheet with Check > Release Workbook.
- In the window that now appears, click on "Allow multiple users to edit at the same time". This also enables you to merge workbooks.
- In the next window, simply confirm with "Yes".
Unprotect Excel sheets: This is how it works
Unprotect Excel sheets: This is how it works
It makes your hair stand on end: There you are, having painstakingly created a perfect spreadsheet in Excel at last. You send it to the team members or the boss – and they change a value here, move a cell there or even delete the important table completely. All the work was for nothing … To prevent this, individual or even all cells of such a worksheet can be locked and the entire sheet can be secured with a password. But what if you still have to work on it later? Then it makes sense to be able to unlock the Excel sheet. Here we show step by step how this works.
Unprotect Excel sheets with a few clicks
Actually, you can only unprotect Excel sheets if you know the corresponding password (exceptions to this below). If the sheet is protected with a password, it cannot be edited for the time being. In order to make this possible, five steps are sufficient to unprotect Excel sheets: 1. First open the protected file. 2. Then jump to the cash register „Check“ at the top. 3. Find the category „Changes“ and press the button „Unprotect sheets“. 4. Now the password must be entered in the corresponding prompt field. 5. Confirm with „OK“. To protect an Excel sheet with Microsoft Excel again, a new password is created via the button „Protect sheet“.
Microsoft Excel keeps a loophole open
Too bad if you have secured an Excel spreadsheet with a password as a precaution – and then forget exactly that! Do you then have to do all the work all over again and create a new Excel spreadsheet? No! Fortunately, with Microsoft Excel and an additional tool, you can remove the Excel sheet protection without using the password.
How to remove the Excel password
If you want to remove the Excel sheet protection without knowing the password, you first have to download the programme 7-Zip for free and install it on your PC. Once all this has been done, proceed as follows:
- Open the folder with the corresponding Excel file.
- Right-click on the file and open it with the command "7-Zip > Open".
- A folder with the name "xl" is shown, which must be opened.
- Select the term "worksheets" in this folder.
- Copy the file "sheet1.xml" that appears to the desktop.
- open "sheet1.xml" in a word processing programme, leaving the 7-Zip programme open.
- In Excel 2010, search for the term "password" using the "Search" command (key combination "Ctrl + F") (in Excel 2013 and Excel 2016, enter "sheetProtection" as the search term).
- In the Excel 2010 file, the entry with the password is now displayed in inverted commas.
- Delete the password (not the inverted commas!).
- The display must now show password="". (As of Excel 2013, the display must look like this "<sheetProtection>").
- Save the file "sheet1.xml".
- Next, copy the file m 7-Zip archive to the same place where it was before.
- Mark the pop-up option field with "Yes" and close the 7-Zip programme.
- Now open the file with Microsoft Excel.
- Now, as described above, the Excel sheet protection can be removed with a few clicks.
Does this work with the whole Excel file?
That would be too good – especially for people who would have unauthorised access to protected files. That’s why the steps only work if individual Excel sheets are protected with a password.
Help - the button "Unprotect sheets" cannot be activated!
If the button „Unprotect sheets“ is greyed out and cannot be clicked, the document probably has a release protection. In order to be able to edit the document, this must first be removed.
- First click on Check > Unprotect.
- Then release the Excel spreadsheet with Check > Release Workbook.
- In the window that now appears, click on "Allow multiple users to edit at the same time". This also enables you to merge workbooks.
- In the next window, simply confirm with "Yes".
Linking Word and Excel - Tips and Tricks
Linking Word and Excel - tips and tricks
If you want to write and format texts on your PC, Word is definitely the programme of choice. And if you want to create diagrams and carry out calculations, Microsoft Excel is just right. Both are available in various packages from Microsoft Office. But if you want to insert tables or diagrams from Excel into Word, it can be tedious – an error can quickly creep into the Microsoft Excel source file due to an unclean adjustment. Here we show how this can be prevented.
What Microsoft Excel can do
Basically, Microsoft Excel is a programme specialised in spreadsheets that can be installed on all major devices and operating systems. Its functions in detail:
- Calculations The most important types of calculations can be performed between several cells and supplemented with known variables.
- Diagrams and graphics Numerical tables can be converted into visual data, for example to show results more clearly in a presentation.
- Calendar Among other things, schedules and recurring events can be managed.
- Inventory management Lists of inventory and staging points can be used for automatic calculation.
- Time recording Project hours, employee times etc. are recorded in tabular form and added up in the result.
- Forms In addition to ready-made selection options, standardised formulas can be easily created for numerous areas of application.
- E-mail lists Structured lists with all relevant data are suitable for maintaining an overview, for example of newsletter recipients.
- Data analysis Detailed data records can be downloaded and saved in csv format, among other things.
- Accounting Expenditure and budget planning, records and forecasts are automatically calculated and updated.
-
Integrating Microsoft Excel into Word - this is how it works
To functionally link, for example, Microsoft Excel tables with a Word document, several steps are necessary:
- In the Microsoft Excel document, the relevant table area is first marked and copied to the clipboard.
- Then it is pasted into the Word document at the point where the table is to appear. If the table has already been created in the correct format, "Link and keep original formatting" must be selected in the insert options. If the table is still to be freely designed, the option "Link and use target format templates" is correct
- The "Table design" and "Layout" tabs can be used to adjust the table design.
Microsoft 365
With the subscription service Microsoft 365 ensures that the latest tools from Microsoft are always available A distinction is made between Microsoft 365 plans for private use and for businesses, educational institutions and non-profit organisations. The plans include the most important Office desktop apps as well as additional online storage and cloud-connected features for real-time file collaboration. The most common for personal use are:
- Microsoft 365 Family for up to 6 people. Includes AI-powered apps, 1TB of cloud storage per person and advanced security for all your devices.
- Microsoft 365 Single with the same features as Family, but only for one device.
Included inall versions are Word, Microsoft Excel, PowerPoint,OneNote, Outlook, Access and Publisher.
Microsoft 365 Mac
For Apple users, Microsoft 365 Mac isavailable.Here, too, it is easy to work with Word, Outlook, Microsoft Excel, PowerPoint or OneNote. Microsoft 365 Mac was specially developed to support typical Mac functions such as dark mode, camera transfer in macOS or Touch Bar.
Microsoft Office 2019 - the better choice?
Buy Microsoft Office instead of subscribing? No problem! That’s what Microsoft Office 2019 developed. The Office apps such as Word, Outlook or Microsoft Excel for both PC and Mac are only paid for once. After the purchase, the software can either be installed or the Microsoft Office download function can be used. Disadvantage: Upgrades are not included in the purchase. If these are desired later, a new main version of Microsoft Office must be purchased at full price.
Header image: © dennizn, stock.adobe.com
Linking Word and Excel - Tips and Tricks
Linking Word and Excel - tips and tricks
If you want to write and format texts on your PC, Word is definitely the programme of choice. And if you want to create diagrams and carry out calculations, Microsoft Excel is just right. Both together are available in various packages from Microsoft Office. But if you want to insert tables or diagrams from Excel into Word, it can be tedious – an error can quickly creep into the Microsoft Excel source file due to an unclean adjustment. Here we show how this can be prevented.
What Microsoft Excel can do
Basically, Microsoft Excel is a programme specialised in spreadsheets that can be installed on all major devices and operating systems. Its functions in detail:
- Calculations The most important types of calculations can be performed between several cells and supplemented with known variables.
- Diagrams and graphics Numerical tables can be converted into visual data, for example to show results more clearly in a presentation.
- Calendar Among other things, schedules and recurring events can be managed.
- Inventory management Lists of inventory and staging points can be used for automatic calculation.
- Time recording Project hours, employee times etc. are recorded in tabular form and added up in the result.
- Forms In addition to ready-made selection options, standardised formulas can be easily created for numerous areas of application.
- E-mail lists Structured lists with all relevant data are suitable for maintaining an overview, for example of newsletter recipients.
- Data analysis Detailed data records can be downloaded and saved in csv format, among other things.
- Accounting Expenditure and budget planning, records and forecasts are automatically calculated and updated.
-
Integrating Microsoft Excel into Word - this is how it works
To functionally link, for example, Microsoft Excel tables with a Word document, several steps are necessary:
- In the Microsoft Excel document, the relevant table area is first marked and copied to the clipboard.
- Then it is pasted into the Word document at the point where the table is to appear. If the table has already been created in the correct format, "Link and keep original formatting" must be selected in the insert options. If the table is still to be freely designed, the option "Link and use target format templates" is correct
- The "Table design" and "Layout" tabs can be used to adjust the table design.
Microsoft 365
With the subscription service Microsoft 365 ensures that the latest tools from Microsoft are always available A distinction is made between Microsoft 365 plans for private use and for businesses, educational institutions and non-profit organisations. The plans include the most important Office desktop apps as well as additional online storage and cloud-connected features for real-time file collaboration. The most common for personal use are:
- Microsoft 365 Family for up to 6 people. Includes AI-powered apps, 1TB of cloud storage per person and advanced security for all your devices.
- Microsoft 365 Single with the same features as Family, but only for one device.
Included inall versions are Word, Microsoft Excel, PowerPoint,OneNote, Outlook, Access and Publisher.
Microsoft 365 Mac
For Apple users, Microsoft 365 Mac isavailable.Here, too, it is easy to work with Word, Outlook, Microsoft Excel, PowerPoint or OneNote. Microsoft 365 Mac was specially developed to support typical Mac functions such as dark mode, camera transfer in macOS or Touch Bar.
Microsoft Office 2019 - the better choice?
Buy Microsoft Office instead of subscribing? No problem! This is what Microsoft Office 2019 developed. The Office apps such as Word, Outlook or Microsoft Excel for both PC and Mac are only paid for once. After the purchase, the software can either be installed or the Microsoft Office download function can be used. Disadvantage: Upgrades are not included in the purchase. If these are desired later, a new main version of Microsoft Office must be purchased at full price.
Header image: © dennizn, stock.adobe.com