5 shortcuts that will make analyzing data a little bit easier:
1. ALT =
Let Excel do the math for you with this shortcut! ALT = detects data in adjacent cells and automatically sums it using the SUM function. Just select an empty cell adjacent to the data that needs to be added and press ALT =.
2. CTRL H
Finding values in your workbook and replacing them one by one could take hours, but not with CTRL H. The CTRL H shortcut opens the find and replace dialog box, which finds a value and replaces all instances with a new specified value.
3. CTRL T
Start getting into the routine of using Tables with CTRL T. CTRL T converts data to an Excel Table. Tables are a powerful tool that cleans up formatting, auto-fill formulas down columns, automatically expand and update linked charts when new rows are added, and more!
4. ALT F1
If you spend too much time creating charts to visualize your data, meet ALT F1. These two magical keys automatically generate a bar chart using the selected data and insert it right into the active worksheet!
5. CTRL SHIFT L
Make analyzing large data sets a little easier with the CTRL SHIFT L shortcut. CTRL SHIFT L adds the Sort&Filter toggles to the top row of the data set, so you can quickly sort and filter data.
To learn all of Excel’s shortcuts and productivity hacks like this, make sure to sign up for my new course! #excel
One of the best ways to avoid errors when writing formulas is by locking reference cells. Instead of typing out the dollar sign in the cell reference, try the F4 shortcut. F4 quickly toggles through absolute/relative referencing.
2. Alt =
Do you feel like you spend too much time summing data in Excel? Try the Alt = shortcut instead. Alt = automatically sums data detected around the cell. Just select the cells that you want to add totals to, hit Alt =, and let Excel do the work for you!
Say goodbye to manually summing data in 2023 and hello to AutoSum. AutoSum automatically detects surrounding data and sums it using the SUM function. Just select the cell you want to calculate the sum in, and press ALT =.
2. Flash Fill
Thanks to Flash Fill, you can officially retire writing complex text functions to manipulate data in 2022. Flash Fill automatically fills data down a column based on detected patterns. Just enter how you want the data to appear and just hit CTRL E!
You're unable to sort data if it contains merged cells. Below we have historical sales data with rows without data merged into one cell. So watch what happens when I try to sort the total sales in descending order.
Merged cells can make copying and pasting data difficult. So let’s say we received Taylor’s sales data and wanted to copy and paste it in. Again, it doesn’t work.
Being able to quickly drill down into data is critical when analyzing. Instead of applying filters manually, add slicers to the data by navigating to the Insert tab > Slicers > select what you want to filter the data by and hit OK. Now just click any button to filter!
2. Power Query
Importing data into Excel never is as easy as it seems. Luckily, Power Query is here to fix that. Power Query imports data from various sources into Excel. So instead of copying data from the web, go to Data > From Web > enter URL > select the table and hit load.
Idk what’s worse: Not being able to see messy data or fixing it by manually adjusting the columns. Instead, clean up messy data in seconds by selecting it and pressing ALT H O I. ALT H O I automatically adjusts the column widths to the length of the data.
2. ALT =
If you don’t like math, luckily, Excel’s AutoSum tool can do the math for you. AutoSum automatically sums detected data. Just select the cell you want to calculate the sum in, and press ALT =.
The LEFT function extracts a specified number of characters from the left of a text value. Just enter the text string as the text argument and the number of characters you want to extract as the num_chars argument.
2. RIGHT
Similarly, the RIGHT function extracts a specified number of characters from the right of a text value. You can just enter the text string as the text argument and the number of characters you want to extract as the num_chars argument.