Welcome to the 23rd edition of North Star Blueprints! In this weekly newsletter, we embark on a journey of personal and professional growth, offering observations, strategies, and inspiration to help you navigate life’s challenges and reach new heights of success. This edition will provide a summary of the daily insights I posted throughout the week, packed with valuable tips, thought-provoking ideas, and actionable advice on how to better use your sheets, work more effectively with Microsoft Excel, and some time-saving tips and tricks I learned over the years to make working on slides easier, in the hopes of helping you to unleash your better self.
July 22: Multiple Excel Screens
A couple weeks ago (Insights 112-116) focused on PowerPoint related tips and tricks. Time to explore the same for another tool we all use – Excel. With data all around us, mastering your ability to work with it and effectively analyze and present it is one of the most important skills you can invest in. I want to dedicate this week’s insights into someone interesting things I learned about Excel. These will not be your typical basics but rather less known functionalities that will make your work with Excel sheets considerably easier.
Have you ever worked on an Excel file with multiple sheets? Having to constantly jump between them, especially if formulas are involved can be quite confusing and time consuming. Use this shortcut to open a new window for the current workbook, essentially duplicating the view and allowing you to see both at the same time (especially useful if you’re using multiple screens): “Alt + W + N”.
This feature allows you to view and work on different parts of the same workbook simultaneously. For instance, you can keep one window focused on your data entry sheet while another window displays your summary or analysis. This is incredibly useful for comparing data, cross-referencing information, and improving overall workflow efficiency.
July 23: Importing Data to Excel from Websites
Have you ever needed to import data from a website into your sheet, but the formatting kept getting messed up when you copy paste directly? Importing web data into Excel facilitates data manipulation and analysis using Excel’s tools and functions, it reduces manual data entry errors, unfortunately it also requires a lot more work to make sure it fits your file. How can we automate the process?
1. Go to the Data Tab: Click on the “Data” tab in the ribbon at the top of the screen.
2. Get Data: Click on “Get Data”, choose “From Other Sources” and then “From Web”. Or directly select “From Web” icon on the ribbon.
3. Enter the Web URL: In the dialog box that appears, enter the URL of the webpage containing the data you want to import. Click “OK.”
4. Navigator Pane: Excel will connect to the webpage and display a list of tables found on the page in the Navigator pane. Select the table you want to import. Click “Load” to load the data directly into Excel or “Transform Data” to open the Power Query Editor for more advanced data shaping.
5. Load Data: After clicking “Load,” the data will be imported into a new worksheet in Excel.
Check out this video for an example of what this looks like. Copying data from online sources will now take only a few seconds and you will get it in a simple to work with format!
July 24: Formula Review and Audit
Once in a while we have to work with complicated Excel sheets that have many formulas that need to be reviewed. Or perhaps, we’re trying to troubleshoot someone else’s sheet and need a better view on the formulas and references they’ve made… Clicking on each cell individually to see the formulas is tedious and time consuming. What can we do to be more efficient?
One of the quickest ways to achieve this is by using the shortcut Ctrl + ~. This simple command instantly toggles the view of your worksheet from showing the results of formulas to displaying the formulas themselves. This feature is particularly useful for auditing, troubleshooting, and ensuring the accuracy of your data.
You can quickly see if all your formulas are entered correctly, identify any inconsistencies, or spot potential errors. This view also makes it easier to understand the relationships between different cells and how data flows through your spreadsheet. For instance, if you’re working on a financial model, you can easily check that all necessary calculations are accounted for and that there are no references to empty or unintended cells. Additionally, this feature can be helpful when sharing your workbook with others, allowing them to see the underlying calculations and ensuring transparency in your work.
July 25: Repeat Actions with Ease
One of the best ways to make your work in Excel more efficient is to reduce mouse movements and clicks. Enter the F4 Key! A shortcut that can significantly enhance your productivity by allowing you to duplicate your last action.
Whether you’ve just formatted a range of cells, inserted a new chart, or applied a specific formula, pressing F4 immediately repeats that last command. This feature is particularly beneficial when performing repetitive tasks, such as changing the font style of multiple sections or filling down formulas.
By eliminating the need to manually repeat steps, the F4 key helps streamline your workflow, making it faster and more efficient to apply consistent actions across your Excel workbook.
Check out this video for some examples.
July 26: Customize Excel
Work long enough on Excel and you’ll develop your own style and favourite tools. How about creating a ribbon tab with all of your most used functions? Streamlining your workflow and tailor the interface to fit your specific needs.
By placing your most-used commands in easy-to-reach locations, you can significantly reduce the time spent navigating through menus. This is particularly useful for repetitive tasks, as you can group related commands together in custom tabs or groups. Excel allows you to export your ribbon customizations to a file, which can then be imported on other devices. This feature ensures that you have the same setup whether you’re working on a different machine or sharing your configuration with colleagues.
1. Open Ribbon Customization: Right-click the ribbon and select “Customize the Ribbon.”
2. Create New Tabs / Groups: Click “New Tab” or “New Group” and rename them for easy identification.
3. Add Commands: Select commands from the list on the left and click “Add” to include them in your new tab or group.
4. Reorder: Drag commands or tabs to rearrange them as needed.
5. Save / Share: Use “Import/Export” to save your setup or revert to defaults.
You’re good to go now! There should be a new personalized tab in your ribbon with all your selected tools. Check out this video for further information.
Thank you for joining me on this journey of growth and discovery. I hope these insights illuminate your path forward as you pursue your aspirations with purpose and determination. See you in next week’s edition!
