Office 2021 and 2024 cheat sheet – Computerworld
Creating a chart from a dynamic array in Excel 2024.
Howard Wen / Foundry
Changes made to the original table data that update the dynamic array will also be reflected in the dynamic chart automatically.
To customize the appearance of your chart, click on the chart to select it. At the top of your spreadsheet, select the Chart Design tab. You can then add a title to your chart, adjust its axes, or change its colors. For more about working with charts in Excel, see our charts and sparklines tutorial.
Excel: Create custom-filtered views for a spreadsheet
Using Excel’s Sheet Views feature, you can create a copy of a sheet and then apply filtered or sorted views of the data to the duplicate sheet. This can be helpful when you’re collaborating on a spreadsheet, where someone would like to create a customized view without altering the original sheet.
You and your collaborators can create several such custom-filtered/sorted views for a sheet. Then each person can select among them, switching from one view to another.
Note: In order for this feature to work, the Excel file must be stored in OneDrive.
Create a sheet view
Along the bottom of your spreadsheet, click the tab for the sheet that you want to create a sheet view for.
Then at the top of the spreadsheet, click the View tab, and on the toolbar, select Sheet View > New. You’ll be switched to a new sheet view of this sheet.
Creating a new sheet view. Here you can filter or sort the data without affecting the original data set.
Howard Wen / Foundry
Apply whatever filters and sorting you like to the data on this sheet — your actions won’t affect the original version of the sheet.
When you’re finished making your customizations, save the new sheet view and give it a unique name: On the View tab, click Temporary View (this is the name given to a new sheet view by default). You’ll be prompted to type in a new name for your new sheet view. Then click the Keep button.
When you click Exit on this toolbar, the sheet view is dismissed, and the spreadsheet returns to its original, default state.
Switch among sheet views
Once you and any of your collaborators have created sheet views for the spreadsheet, you can easily switch from one to another. At the top of the spreadsheet, click the View tab. Then at the left of the toolbar, select the name of the current view (it says Default if you’re viewing the spreadsheet without a sheet view applied) to open a dropdown listing the names of sheet views that have been created for the spreadsheet. Click the name of one of these sheet views to switch to it.
Click the dropdown on the toolbar to switch to a different sheet view.
Howard Wen / Foundry
Manage your sheet views
You can delete, duplicate, or rename your sheet views. At the top of your spreadsheet, click the View tab. Then toward the left of the toolbar, click Options.
On the “Sheet Views options” panel, click to select the name of a sheet view. Then click the Rename, Duplicate, or Delete button to perform that action on the selected sheet view.
Excel: Place images in cells with the IMAGE function
This feature requires Excel 2024.
Using the IMAGE function, you can insert and display web-based images in your spreadsheets to create visually engaging dashboards, inventories, and so on. Instead of floating on top of a cell, images inserted with the IMAGE function live within a cell and will remain there when you sort, filter, calculate, or otherwise manipulate a table.
The formula for the IMAGE function that you enter into a cell is:
=IMAGE(source, [alt_text], [sizing], [height], [width])
source: Insert the web link to an image here, whether it’s on the internet or stored in your OneDrive. You can also reference another cell that contains an image or web link. BMP, JPG/JPEG, GIF, TIFF, PNG, ICO, and WEBP image formats are supported.
alt_text: Optionally, you can type in text that names or very briefly describes the image. This is useful if the image cannot be displayed, such as if the link becomes inactive.
sizing: This is also optional and determines how the image will be sized inside the cell. You can enter one of the following numbers:
0: This is the default. The image will be sized to fit inside the size of the cell, while maintaining the image’s original aspect ratio.
1: The image’s length and width will be stretched to fill the cell, which may distort its original aspect ratio.
2: The image’s width will be stretched to fill the cell’s width, but not its height, thus maintaining the image’s original aspect ratio.
3: The image’s height will be stretched to fill the cell’s height, but not its width, thus maintaining the image’s original aspect ratio.
height: This optional value specifies the height of the image in pixels.
width: This optional value specifies the width of the image in pixels.
Here’s an example that uses all the optional settings above:
=IMAGE(“https://cdn.subscribers.com/uploads/setting/modal_image/44546/CW-favicon-600px__1_.png”, “Computerworld Favicon”, 3, 200, 300)
The image file at the web link will display “Computerworld Favicon” if this link doesn’t work. It will be sized to fill the cell’s height (“3”), but not its width. However, the image will be restricted to a height and width of 200 x 300 pixels. (Normally you’d use either the sizing or the height and width controls, not all of them.)
In this example, the square CW favicon has been distorted to a height of 200 pixels and a width of 300 pixels.
Howard Wen / Foundry
For more about working with the IMAGE function, see Microsoft’s IMAGE function support page.
PowerPoint: Create internal hyperlinks in a presentation
You can now create hyperlinks within a presentation: When you click linked text or a linked image on a slide, you will jump to another slide in the same presentation.
Go to the slide that contains the object (text or image) that you want to link from. Right-click on this object, and on the menu that opens, select Hyperlink…. Alternatively, you can click on the object to select it. Then, above the slide, click the Insert tab, and on the ribbon toolbar, click Link.
Either action will open the Insert Hyperlink panel. In the left column of this panel, click Place in This Document. In the second column, select the slide that you want the new hyperlink to jump to when it’s clicked. Then click OK.
Adding an internal hyperlink to a presentation.
Howard Wen / Foundry
To test the hyperlink, go to the slide that contains the hyperlinked object. Above the slide, click the Slide Show tab. On the ribbon toolbar, click From Current Slide. Now click the object that you created a hyperlink for — this action should jump to the slide that you selected.
PowerPoint: Create a video recording of a presentation
A presentation is about more than the slide deck. Equally important are the things you say that aren’t written on the slides — and how you say them. That means colleagues who aren’t able to attend a live session may miss out on the full impact of your presentation.
In PowerPoint 2021 and 2024, you can record both audio and video of a presentation and export it as a video to share with others.
Above your presentation, click the Record tab. On the ribbon toolbar, click From Beginning or From Current Slide. This will open a recording studio panel over the screen.
PowerPoint’s recording tools appear at the top of the screen.
Howard Wen / Foundry
Along the top of this panel, you can click the webcam or microphone icons to turn these devices on or off for recording. If you have more than one camera and/or mic connected to your computer, you can switch among them by clicking the three-dot icon on this toolbar.
Before you start the recording, take a moment to review the slides in your presentation — at the lower-left corner, click the left and right arrows to scroll through them. In the area above the slide thumbnail, you can optionally type in notes or a script to read during your recording.
Click the red circle button at the top to start recording. It will turn into a red square, indicating that your actions are now being recorded. Click through the slides and speak aloud, highlighting key points on the slides and providing more context for those who will be watching this video.
You can click the laser pointer, pen, or highlighter icons on the toolbar below the large thumbnail of the slide to point out, draw on, or highlight things on the slide. These gestures will be included in the recording.
At the top of the screen, click the Pause button (to the left of the webcam icon) to pause recording. Click it again to resume.
Click the red square at the top of the screen to stop recording. On the large thumbnail of the slide, click the triangle icon to play back the recording. If you want to redo the recording, click the circular arrow icon at the top of the screen.
When you’re satisfied with the recording, click Export at the top of the screen. On the page that opens, you can type in a file name for the recording and optionally click Browse to select where on your PC you want to save the video file. Then click the Export Video button to save the recording to that location.
Exporting a video of a presentation.
Howard Wen / Foundry
Depending on its length, the video may take a few minutes to export.
PowerPoint: Insert live webcam video into your presentation
This feature requires PowerPoint 2024.
With PowerPoint’s Cameo feature, you can embed live video from your webcam into one or more slides in your presentation. This can be useful when you’re collaborating with your co-workers on a PowerPoint presentation so you can talk with them directly. Or when you’re presenting a slideshow to others, you might want to insert your live webcam feed into a specific slide to explain the information on it.
Go to the slide where you want to add live video. Above the slide, click the Insert tab. On the ribbon toolbar, click Cameo to insert your webcam feed. Select This Slide or All Slides. A large headshot icon will appear on the lower right of the slide. (If you selected All Slides, this icon will appear on all the other slides in the presentation.) Click the crossed-out camera icon on it to switch on a live feed of your webcam.
Your live webcam feed appears at the lower right of the slide.
Howard Wen / Foundry
You can reposition your webcam feed on the slide by clicking-and-holding onto it and dragging it to another area on the slide. You can resize it by dragging one of the nine points bordering it inward or outward.
There are also several ways that you can customize your webcam feed, such as applying styles and visual effects. These options are listed on the ribbon toolbar in the “Camera Styles” area.
When you’re satisfied with the way you appear on the webcam feed, click the Slide Show tab above the slide. On the ribbon toolbar, select to start the presentation From Beginning or From Current Slide (the slide that’s open in the main area of PowerPoint).
As you advance through the presentation, your webcam feed will appear on the slide that you set it on. When the presentation is finished and you exit out of Slide Show mode, the webcam feed will turn off. You can also turn it off by simply clicking on the live feed image.
PowerPoint: Add captions to video or audio on a slide
This feature requires PowerPoint 2024.
If a video or audio file embedded on a slide has a closed captioning file that accompanies it, you can add this caption file so that the captions appear when the media is played. This can be helpful for viewers who are hard of hearing or who aren’t native speakers of your language.
Adding a closed captioning file makes video playback more accessible.
Howard Wen / Foundry
(If a video or audio file isn’t already on a slide, click the Insert tab at the top of the screen. On the ribbon toolbar, click Video or Audio. The interface will prompt you to select a video or audio file on your PC.)
Right-click on the embedded video or audio file, and on the menu that opens, select Insert Captions. Alternatively, click on the video or audio file to select it. Above the slide, click the Playback tab. On the ribbon toolbar, click Insert Captions and select Insert Captions again on the menu that opens.
Either way, the interface will prompt you to upload the caption file (in WebVTT or SRT format) that goes with the embedded video or audio file. Once the captions are uploaded, you can click the Play button below the video or audio clip to see the captions.
Outlook: Find emails quickly with Instant Search
Outlook 2021 and 2024 offer much more powerful search features than their predecessors. Inside the search box at the top of the screen, type keywords related to an email you’re looking for, such as the name of the sender or recipient, a subject line, or words or phrases that may be in the email. As you type, the Instant Search feature will generate results, if any, in a dropdown below the search bar.
You can click the Files or People tabs to narrow the search to emails with a file attachment or a sender described by your keywords. To the left of the search box, clicking All folders will open a dropdown where you can select a specific folder to narrow the results to emails in that folder only.
To search for emails, simply start typing in the search box at the top of the screen.
Howard Wen / Foundry
In the dropdown, click on the email that you want to open. Or you can press the Enter key on your keyboard, and the full list of search results will appear below in the main window of the Outlook app.
When you hit Enter, the full list of results appears below.
Howard Wen / Foundry
Refine the search results using filters
On the toolbar just above the results, you can click options such as Has attachments, Unread, @ Mentions me, and so on to filter the results by those criteria.
Another way to filter results: Back inside the search box at the top, click the Filters icon (three horizontal lines that vaguely resemble a funnel) to the right to open a panel listing various filtering options.
Outlook offers several advanced search options, including searching within a specific date range.
Howard Wen / Foundry
Perform more complex searches with search operators
An even quicker way to refine a search is by using search operators inside the search box. For example, you can type from: and then an email address to find emails that were sent from that address. Type subject: or body: followed by a single word or a phrase in quotes to narrow results down to emails that contain the word or phrase in the subject line or body of an email.
The operators received: and sent: narrow emails down to those that you received or sent on a specified date. For example, received:>=07/01/2024 will narrow the search to emails you received on or after July 1, 2024. And received:<=07/01/2024 will narrow results to emails you received before this date.
For more info about searching in Outlook, including searching for calendar items and contacts, see Microsoft’s Outlook search support page.
Outlook: Translate emails from and to other languages
Outlook can translate emails you receive written in foreign languages into your native language — and it can translate emails you write in your own language into other languages.
When you receive an email written in a foreign language, you’ll see a prompt above its message body. If English is your native tongue and you get an email written in Spanish, click Translate to English and the Spanish text in the body will be translated into English. Outlook automatically recognizes several languages that it can translate for you.
You can translate an email into your own language with a single click.
Howard Wen / Foundry
Alternatively, you can click the three-dot icon at the upper right of the email. On the menu that opens, click Translate.
Translate emails into another language
You can also use Outlook’s translator to translate your text to another language. So, for example, you can translate an email originally written in English into Spanish or another language.
If you’re using classic Outlook: Click the Home tab at the top, then click the Translate button on the toolbar. On the menu that opens, click Translation Preferences…. This will open a panel where you can select the language you want to translate your email to.
If you’re using new Outlook: Click the View tab at the top, then click View settings on the toolbar. On the panel that opens: in the first column, click Mail. In the second column, click Message handling. And in the third column, scroll down to the subheading “Translation.” Below it, you can select the language that you want to translate your email to.
Translate highlighted text in an email
This feature is currently supported in classic Outlook only. Highlight portions of text that you want to translate in an email — it can be an email you’ve received, one you’ve sent, or one you’re composing.
Right-click the highlighted words. On the menu that opens, select Translate > Translate Message. If the highlighted words are your own that you typed in an email draft, you’ll see them automatically translated on this second menu.
Learn more
Now that you’ve familiarized yourself with the notable new features in Office 2021/2024, take a look back at our prior cheat sheets for Word 2016/2019, Excel 2016/2019, PowerPoint 2016/2019, and Outlook 2016/2019; most of the tips outlined in those articles still apply in the later releases.
Or take a look at all of our Microsoft cheat sheets, which cover Windows as well as the Office apps.
Source link
