Whether you are a Data Analyst, Financial Analyst, or any other Analyst, chances are you use Microsoft Excel in some capacity. Excel is one of the most popular and powerful spreadsheet programs available, and it is constantly being updated with new features and functionality. In 2023, Microsoft released a number of new Excel features that are specifically designed for analysts and data scientists. Here are 5 of the most important new Excel features that every analyst should know:
1. Python integration:
Excel now allows you to run Python code directly within the Excel interface. This means that you can use Python to perform complex data analysis tasks, such as machine learning and natural language processing, without having to switch to a different programming environment. This integration makes Excel a powerful tool for data scientists who want to leverage Python’s capabilities seamlessly (Microsoft official Video release)
I also found that of several videos I audited, the below 2 videos were very helpful to get started with using Python (Pandas)at the time of writing this article. One from “Leila Gharani” (20 Mins) and the other from “My Online Training Hub” (10 Mins)
2. Keyboard Shortcut for Paste as Values
“Ctrl” + “Shift” + “V” is the new keyboard shortcut for “Paste as Values.” This may seem like a small improvement, but for analysts who frequently work with data, it can greatly speed up the process of pasting data as values, ensuring that formulas and formatting don’t get carried over.
3. Vstack/Hstack
These are new formulas in Excel that allow you to vertically stack or horizontally stack ranges of data. Vstack combines data from multiple ranges vertically, while Hstack combines data horizontally. This is particularly useful when you need to merge data from different sources or organize it in a specific way.
4. AI Based Tools & tricks:
A lot of you are eagerly awaiting Microsft’s ChatGPT integration into office tools popularly known as “CoPilot”. What if I tell you that there are already some built-in AI-powered tools into Excel even before CoPilot release? Yes, you may have used some of these already without realizing it
- Data Cleaning using Flash fill ( Web Version ): Excel’s Flash Fill feature helps automate data cleaning tasks by intelligently recognizing patterns and formatting in your data.
- Recommended PivotTables & Recommended Charts: Excel can now suggest PivotTables and charts based on your data, making it easier to create meaningful visualizations
- Analyze Data: This feature uses machine learning and AI to automatically identify patterns, correlations, and outliers in your data. It gets better with usage, making data analysis more efficient.
- Where to find: Home Tab> Far Right > “Analyze Data”
- Office 365:Analyze Data in Excel Application
- Web Version: Get Insights with Analyze Data
- Forecasting: For analysts who need assistance with forecasting, Excel offers a Forecast Sheet feature. You can use this feature if you have historical time-based data in a tabular format. It allows you to adjust parameters such as Confidence Interval; seasonality, and Fill Missing Points to fine-tune your forecasts.
- Data Tab> Forecast Sheet
- Parameters that can be adjusted: Confidence Interval; Seasonality, Fill missing Points, etc
- Documentation: Create a forecast in excel for Windows
5. Excel Labs (Formerly Advanced Formula Generator)
Excel Labs is particularly important if you’re scared to write your own LAMBDA functions, as you’ll see it can write them for you! It’s available as an Add On and so you will need to install the Add-On first. Once installed, it will be available on your Hoem Tab. I have used this to write some tricky Formulas but have found it hard to write formulas referencing different Sheets.
- Where to find: Add-On Install needed; Home Tab > Excel labs
- Resources: My Online Training Hub Article & Video; Tech Community Blogpost
Other Honorable Mentions for “New Features to Excel in 2023”
- Insert Pictures in Cells in Excel (Documentation)
- Image Function (Documentation)
- Image to text (Highly useful when your coworker sends that important data in Image/Picture format. (Documentation)
- TOCOL & TOROW: Similar to VSTACK & HSTACK (Documentation)
- TextSplit Formula (Documentation)
- Textbefore & Textafter (Documentation)