eIS Business Solutions

Tag: excel

  • 6 Simple ways to recover lost Excel files

    6 Simple ways to recover lost Excel files

    Your Microsoft Excel spreadsheets are likely the lifeblood of your small business. Whether it’s your monthly budget, an upcoming payroll sheet, or your entire inventory list, seeing hours of hard work vanish in an instant is terrifying. Before you start retyping everything from scratch, take a deep breath. Your data is probably still hiding on your computer. Work your way down this list of simple methods to rescue your lost work.

    1. Check the recovery panel

    If your computer restarts out of nowhere or Excel suddenly freezes and closes, the program usually tries to save your progress automatically.

    • Step 1: Open a brand-new, blank Excel spreadsheet.
    • Step 2: Look at the left side of your screen. A panel called “Document Recovery” should pop up.
    • Step 3: Look for your lost file, click the version with the most recent time next to it, and go to File > Save As to save it securely.

    2. Recover unsaved workbooks

    We’ve all done it: you close a document and accidentally click Don’t Save instead of Save. Excel actually keeps a hidden stash of files for this exact scenario.

    • Step 1: Open Excel, click File in the top left, and choose Info.
    • Step 2: Click the button that says Manage Workbook, then select Recover Unsaved Workbooks.
    • Step 3: A folder will pop up. Click the “Date Modified” header to sort the files from newest to oldest.
    • Step 4: Open the most recent draft. If it’s your missing work, click File > Save As to save a permanent copy.

    3. Search AutoRecover

    If the first two options didn’t work, Windows might still have a backup tucked away in a hidden system folder.

    • Step 1: Hold down the Windows key on your keyboard and press R.
    • Step 2: In the little box that appears, copy and paste this exact text: %AppData%\Microsoft\Excel\</code> and hit Enter.
    • Step 3: Sort the files by the date they were last modified so the newest ones are at the top.
    • Step 4: Click through the files to find your draft. Open it, then go to File > Save As to save it to your normal business folders.

    4. Use Version History

    If you save your business files to OneDrive or SharePoint, you have a built-in time machine. This is incredibly helpful if you made a huge mistake on a sheet and need to go back to its previous state.

    • Step 1: Open your spreadsheet in Excel.
    • Step 2: Click the file name at the very top middle of the screen and choose “Version History.” (You can also find this by going to File > Info > Version History).
    • Step 3: A panel will open on the right showing previous times the document was saved. Click on them to preview what the file looked like at that exact moment.
    • Step 4: Found the right one? Click Restore to bring it back.

    5. Look at Previous Folder Versions

    If your computer has Windows File History turned on, it regularly takes snapshots of your folders. This is perfect if you completely deleted a file by mistake.

    • Step 1: Open your file folders and find the exact folder where your lost Excel sheet used to live. Right-click that folder and choose Properties.
    • Step 2: Click the tab at the top labeled “Previous Versions.”
    • Step 3: Pick a folder version from a date before you lost the file, and click Open.
    • Step 4: A window showing exactly what that folder looked like in the past will open. Find your Excel sheet, copy it, and paste it back onto your current desktop.

    6. Search the temporary files

    When all else fails, fragments of your spreadsheet might be sitting in your computer's Temp folder — a place where Windows stores leftover data.

    • Step 1: Press the Windows key + R, type in %temp%, and hit Enter.
    • Step 2: In the search bar at the top right, type .tmp and sort the results by date. If nothing shows up, try searching for ~$ instead.
    • Step 3: Look for files created around the time you lost your work. Right-click one, choose Open With, and select Excel.
    • Step 4: If your missing numbers and text magically appear, immediately copy the data, paste it into a new Excel sheet, and save it.

    Pro tip: Don't want to go hunting for files ever again? Take 30 seconds right now to change this one setting:

    1. Open Excel and go to File > Options > Save.
    2. Look for the setting that says “Save AutoRecover information every 10 minutes.” Change that 10 to a 2.
    3. Ensure the box underneath it, “Keep the last AutoRecovered version if I close without saving,” is checked.

    Whenever possible, try to save your business documents to a secure cloud service such as OneDrive. It automatically backs up your keystrokes as you type, giving you total peace of mind to focus on what really matters: growing your business.

    Reach out to our experts today for more valuable tech tips!

    Published with permission from TechAdvisory.org. Source.

  • Achieve more with Excel: A productivity guide for Microsoft 365 users

    Achieve more with Excel: A productivity guide for Microsoft 365 users

    Excel for Microsoft 365 is packed with new features that can significantly improve your productivity. From real-time collaboration to dynamic arrays, discover the essential tools that can help you get the most out of this powerful spreadsheet tool.

    Get things done faster with SEARCH

    If you’ve ever struggled to find a command in Excel’s Ribbon, the SEARCH feature is a game-changer. Simply type what you need, such as “pivot table” or “sum,” and Excel will show you the best options to get started. It also remembers your previous searches, making frequent tasks more accessible. Managing complex spreadsheets then becomes significantly easier and more efficient, helping you save valuable time.

    Explore advanced chart types

    Visualizing data is crucial; that’s why Microsoft has introduced a range of new chart types to help you display your information more effectively. From treemaps (perfect for hierarchical data) to waterfall charts (ideal for financial statements), Excel now offers a variety of ways to see your data come to life. These chart types can help you uncover insights that might otherwise go unnoticed, such as revenue trends or sales patterns.

    Real-time collaboration

    The era of exchanging Excel files through endless email chains is over. With Microsoft 365, your team can collaborate simultaneously on the same spreadsheet. As long as the file is saved in SharePoint or OneDrive, your team can work together in real time. You’ll see exactly what others are editing, and you can even leave comments inside cells to discuss changes.

    Linked data types

    Excel’s linked data types are a powerful feature that lets you pull in real-time, updated information from the web directly into your spreadsheets. For example, if you’re tracking stock prices, you can enter a company’s ticker symbol, and Excel will automatically fetch the latest stock price. The data in these cells updates regularly, so there’s no need to manually update figures.

    Dynamic arrays for smarter formulas

    Dynamic arrays are one of the most exciting additions to Excel for Microsoft 365. These formulas allow you to return multiple results from a single calculation. The FILTER function, for instance, can be used to pull only certain data from a list. As your data changes, the dynamic arrays adjust automatically, making them perfect for ever-changing datasets.

    Version History and AutoSave

    Losing your work is probably the most nerve-wracking thing about working on spreadsheets. Fear no more. With AutoSave enabled on files stored in OneDrive, SharePoint, or OneDrive for Business, your changes are saved automatically. If anything goes wrong, Version History lets you easily restore an earlier version of your file. You can review, compare, and recover past versions whenever you need to, giving you complete control and peace of mind.

    Sheet Views for custom filtering

    Team members often need to look at data in different ways because of their unique roles or perspectives. With Sheet Views, Excel allows you to create custom filtered or sorted views of your worksheet without affecting the rest of your team’s view. Whether you’re analyzing sales data or project timelines, this feature makes it easy to personalize your experience while keeping everyone else’s work intact.

    3D maps to visualize geospatial data

    For those working with geographical data, Excel’s 3D Maps feature provides an interactive way to display and analyze information on a three-dimensional globe. This feature is great for presenting sales or demographic data across regions and allows you to see patterns that are much harder to spot in flat spreadsheets.

    Microsoft 365 Copilot

    For users who subscribe to Microsoft 365 Copilot, Excel now includes an AI assistant that can help you generate charts, summarize data, and even suggest formulas. While it’s still in its early stages, Copilot can offer guidance for users who may not be Excel experts. However, don’t expect it to replace the experience of manually crafting detailed analyses just yet.

    For more Microsoft 365 tips and technology insights, contact our knowledgeable team. We’re always ready to assist you.

    Published with permission from TechAdvisory.org. Source.

  • How to create charts and sparklines in Excel

    How to create charts and sparklines in Excel

    Visualizing data is key to making informed business decisions, and Excel’s chart and sparkline tools make it easy to turn numbers into clear visuals. In this guide, you’ll learn the basics of using charts and sparklines in Excel to highlight trends, compare data, and better understand your spreadsheet insights.

    What are Excel charts?

    Excel charts are visual representations of data that help you quickly identify trends, make comparisons, and gain insights without needing to analyze rows and columns. They come in a variety of types, each with specific benefits for different kinds of data.

    Types of charts in Excel

    Excel provides many chart types to choose from, so you can pick the one that best suits your data and goals. Here are some of the most popular options:

    • Column and bar charts – Ideal for showing changes in data over time or making quick comparisons, column charts display values vertically, while bar charts show them horizontally.
    • Line and area charts – Great for tracking changes over periods, line charts show trends with lines across a grid, while area charts use colored spaces between lines to highlight the relationship between parts.
    • Pie and donut charts – These charts represent parts of a whole and are useful when dealing with positive values only. Use pie charts to display a single data series, and donut charts to show multiple series in concentric rings.
    • Scatter and bubble charts – Both these charts are best for showing relationships between two variables using points, with the main difference being bubble charts have points of varying sizes to indicate different values.

    How to create a chart in Excel

    You can create a chart in Excel with just a few clicks:

    • Select your data – Highlight the data range you want to visualize.
    • Use recommended charts – Go to the Insert tab and select Recommended Charts. Excel will analyze your data and suggest the most suitable chart types.
    • Choose a specific chart – If you have a chart in mind, choose it directly from the Insert tab. Each chart type will give you several style options to choose from.

    Customizing your chart

    Once you’ve created your chart, you may want to customize it to make it more readable and visually appealing. Here’s how to do that:

    • Change the chart title – Click on the chart title to edit it.
    • Add or modify the legend – Legends explain what each color or symbol represents. You can add or remove them by selecting the Chart Design tab, then clicking Add Chart Element > Legend.
    • Add data labels – Data labels display the numeric values for each data point. Go to Chart Design > Add Chart Element > Data Labels.
    • Switch chart colors and styles – You can change colors or try different design options in the Chart Design tab. Select the layout that suits your preferences and makes your data easier to understand.

    Editing your chart

    Excel makes it easy to make changes to your chart, even after it’s created. Here are some common edits:

    • Change the chart type – If you want to try a different chart type, go to Chart Design and select Change Chart Type. Choose a new option that might better display your data.
    • Swap data axes – Switching the X and Y axes can sometimes make data easier to understand. Go to Chart Design and select Switch Row/Column to see if a new layout helps.
    • Add a trendline – To show data trends over time, add a trendline. In the Chart Design tab, select Add Chart Element, then Trendline, and choose your preferred trendline style.

    What are sparklines?

    Sparklines are tiny, in-cell charts that offer a quick glimpse of trends within a row or column of data. Unlike regular charts, sparklines only represent a portion of data in one cell, so they’re useful for showing compact visual insights. They’re often used to spot patterns or changes in data such as sales performance across months.

    Adding sparklines to your data

    Follow these steps to add sparklines to your spreadsheet:

    • Select a blank cell – Choose an empty cell where you want the sparkline to appear.
    • Insert sparklines – Go to the Insert tab, select Sparklines, and choose a type (e.g., Line, Column, or Win/Loss).
    • Choose your data range – Excel will ask you to select the data range you want the sparkline to represent. Click OK to add the sparkline.

    Once created, sparklines can be copied across rows or columns to apply the same trend visualization to other data points.

    Customizing sparklines

    You can customize your sparklines to suit your specific needs:

    • Change colors – In the Sparkline tab, you can adjust colors to better match your spreadsheet style.
    • Highlight points – To emphasize specific data points, such as the highest or lowest values, select options in the Sparkline tab under Show.
    • Adjust type – Switch between line, column, or Win/Loss sparkline styles to see which best displays your data trends.

    Tips for effective data visualization in Excel

    To create clear and impactful charts and sparklines, choose the right chart type for your data. You should avoid using pie charts with negative or zero values as they can distort the visualization.

    It’s best to also keep your visuals simple. Avoid overcrowding charts to ensure they’re easy to interpret. Moreover, use sparklines to quickly highlight trends and key insights without the need for a full chart.

    For more tips on getting the most out of Excel and other productivity tools, feel free to reach out to our IT experts.

    Published with permission from TechAdvisory.org. Source.

  • What is an Excel slicer, and how can It help simplify your data?

    What is an Excel slicer, and how can It help simplify your data?

    Spreadsheet software such as Microsoft Excel are incredibly useful, but they can become overwhelming, especially when managing large datasets. Functions like filtering are helpful, yet not always easy to master. Fortunately, Excel offers slicers, a simple yet effective way to filter complex data visually. With slicers, navigating and analyzing your data becomes much more efficient, even for those unfamiliar with advanced Excel techniques.

    Understanding Excel slicers and how they improve data filtering

    An Excel slicer is a powerful yet simple tool that helps users filter and interact with data based on selected criteria. It’s especially useful for drilling down into specific information within larger datasets. Once a slicer is set up in a worksheet, it allows users to quickly filter data by clicking buttons that correspond to particular groups of information. This makes it easy to isolate key insights without needing to scroll through long rows of data.

    Excel already offers built-in filtering options for both tables and PivotTables, but these can sometimes feel cumbersome, particularly with larger datasets. Slicers provide a more intuitive, visually appealing way to filter information, streamlining the process.

    Slicers are most commonly used with tables and PivotTables, enabling users to create multiple slicers for the same dataset. This allows viewers to easily see which subsets of data are being filtered and adjust the view according to their needs. With just a few clicks on the slicer buttons, specific datasets can be highlighted, offering a clearer focus.

    In addition to tables and PivotTables, slicers can also be applied to charts, further enhancing the ability to interact with and filter graphical data representations. This versatility makes slicers a go-to tool for anyone looking to manage complex datasets more effectively.

    How to create and customize slicers in Excel

    Before you can start using slicers in Excel, you should first organize your data into a table. Begin by selecting the entire dataset you want to work with. Then, navigate to the Ribbon toolbar at the top of the screen, select Insert, and choose Table. Ensure the option for “My table has headers” is checked in the popup window before clicking OK.

    Once your data is formatted as a table, you can easily add slicers. Simply click on any cell within the table, go back to the Ribbon, and select Insert > Slicer. In the dialog box that appears, you will be prompted to select which fields (based on your headers) you want to use as slicers. You can select one or multiple fields, depending on what data you’d like to filter. Once you’ve selected the fields, click OK to generate the slicers.

    The slicers will appear on your worksheet, and each slicer will contain buttons that correspond to the data in the chosen fields. You can rearrange the slicers on your sheet for clarity and ease of use, ensuring they are neatly spread out and easy to read.

    You can also enhance the slicers by changing their appearance. Excel allows you to customize the color of each slicer for visual distinction or to match the overall style of your sheet. To change the slicer’s color, click on the slicer you want to modify, go to the Slicer tab on the Ribbon, and choose a new color from the available options in the gallery.

    Finally, repeat the color customization process for each of your slicers to keep them visually organized and user-friendly.

    Beyond slicers, there are countless other features in Excel and the Microsoft Office suite that can help streamline your workflow. If you’re interested in exploring more of these functions or need assistance in setting up the best Office tools for your business, don’t hesitate to contact our team. We’re here to help you unlock the full potential of Microsoft Office.

    Published with permission from TechAdvisory.org. Source.

  • How Microsoft 365 can help your SMB grow

    How Microsoft 365 can help your SMB grow

    As the owner of a small- or medium-sized business (SMB), you may feel like you’re always stretched thin. You have to manage tight budgets, juggle multiple roles, and make sure your employees have the tools and resources they need to do their jobs. It can be tough to find time to focus on growing your business. But what if there was an all-in-one solution that could help you do it? Enter Microsoft 365.

    Do more with less

    Microsoft 365 is a comprehensive suite of tools and services designed to help SMBs get more done. It delivers the full power of Office apps, including Word, Excel, and PowerPoint, with the convenience of cloud-based collaboration. It also gives you access to features like secure file sharing, document workflow automation, and real-time communication. Best of all, Microsoft 365 is designed to work with the tools and services you already use.

    With Microsoft 365, you can save time and money by eliminating the need for additional software or hardware. It’s easy to set up and manage, and it’s available at affordable price points. You can even use a single subscription to cover all the devices in your business, from PCs to phones to tablets.

    Stay connected, wherever you are

    By leveraging Microsoft 365, you can keep your team connected and engaged, regardless of their location. With cloud-based services like Microsoft Teams, you can have virtual meetings, chat with colleagues, and collaborate on projects in real time. And when you need to access files, emails, or other data while away from the office, you can do so securely with the Microsoft 365 mobile app.

    Gain valuable insights with AI

    Tools like Power BI and Azure AI enable you to gain valuable insights into your business. With Power BI, you can quickly create interactive dashboards that provide up-to-date information about the performance of your company. This can help you make better decisions and identify trends that could be a source of new opportunities.

    Meanwhile, Azure AI allows you to leverage the power of artificial intelligence (AI) to improve customer service, automate processes, and optimize your business operations.

    Secure your data

    Finally, Microsoft 365 offers a range of advanced security features that help protect your data and keep your business safe. These include multifactor authentication, data loss prevention, and encryption to keep sensitive information secure. And if you ever need assistance with any of these functions, Microsoft’s world-class support team is always on hand to help.

    In today’s competitive world, it’s essential for SMBs to have the tools and resources they need to succeed. Microsoft 365 can help you do just that. With its easy setup, affordable pricing, and powerful features, it’s a great way to give your business an edge. Try it today and see the difference Microsoft 365 can make in your SMB’s growth.

    Don’t know where to get started? Contact us today! We’re here to help you discover the full potential of Microsoft 365.

    Published with permission from TechAdvisory.org. Source.

  • How to improve your Excel skills

    How to improve your Excel skills

    Microsoft Excel is a popular office application used by countless businesses around the world. Unfortunately, many employees are not using it to its full potential. If you’re struggling with Excel, these tips will help improve your spreadsheet skills.

    SUM and AutoSum

    SUM is a function that allows you to add individual ranges, cell references, values, or all three. To do this, follow these steps:

    1. Select a cell next to the values you want to add.
    2. Type =SUM(.
    3. Select or highlight the cells you want to add.
    4. Type ) then press Enter.

    Another way to add numbers in a row or column is to click on the AutoSum button on the Home tab, select the cells you want to add, then press the Enter key. Here’s a short video with examples of how to use SUM and AutoSum.

    COUNT and COUNTA

    The COUNT function lets you determine how many cells in a spreadsheet contain numbers. To use this function, click on an empty cell and type in the formula =COUNT(B1: B50).

    In this example, Excel will check column B, rows 1 to 50, for any numbers. If there are 25 cells with numbers, Excel will return a value of 25. The COUNT function only looks at numbers and will ignore text. If you use COUNT in a column full of text, it will return a value of zero.

    The COUNTA function is similar to COUNT, but it looks at cells containing text. So if you want to count how many cells in a column contain text, simply type this formula into an empty cell: =COUNTA(P1: P25). If there are five cells with text under that range, Excel will return a result of 5.

    Pivot tables

    A pivot table lets you summarize large amounts of information, analyze related totals, and present detailed reports in a simplified and user-friendly way.

    For example, you want to determine how many sales each person in your team has made during the past six months. Using a pivot table, you can easily display all the data grouped together under different categories (a task that would be time-consuming if you were just using functions and formulas).

    Here are the steps for creating a pivot table:

    1. Select the data on the spreadsheet you want to include in the table.
    2. On the Excel ribbon go to Insert > Tables > Pivot table.
    3. A Create PivotTable dialog box will appear, with the data range you selected. You can manually edit the data range as you see fit. This dialog box will also give you the option to create the pivot table in a new or in an existing worksheet.
    4. Once you’re done editing, click OK.
    5. After clicking OK, a blank table will appear along with a PivotTable Fields dialog box. In this box, you can select which fields in the data range you selected earlier will appear in the table.

    You can check out this short video if you want to learn more about creating a pivot table.

    Filter

    Filtering allows you to temporarily hide irrelevant data so you can focus on the ones you need. This feature is useful if you’re working on a spreadsheet with a large amount of data. For instance, you want to know how many students in Section 3 of your class got a midterm score of 90 and above. You can have Excel filter out the students in Section 3 who scored below 90, leaving you with a list of those who got a score of 90 and above.

    Here are the steps to use filtering in Excel:

    1. Highlight the data set you want to filter. This should include the headers of the chosen columns.
    2. On the Excel ribbon, go to Data > Filter. A dropdown arrow will appear on the headers of the column you’ve selected.
    3. You can use the dropdown menu to filter the data by text or number depending on the conditions you set.

    Check out this video clip about filtering data on a spreadsheet.

    Mastering these features will significantly improve your Excel skills and your productivity. To find out more about Microsoft Excel, give our experts a call today.

    Published with permission from TechAdvisory.org. Source.

  • 3 Excel tips that make life easier

    3 Excel tips that make life easier

    Microsoft Excel is now better than ever, thanks to its extremely user-friendly features. Pull yourself up skillswise by following our three tips below.

    Take advantage of Ideas in Excel

    If you’re not sure which type of chart best tells a particular story out of a table of data, don’t worry — Excel’s artificial intelligence has got your back. Just click any cell in a data range, then go to the Home tab and click the Ideas button. A task pane will appear, showing recommendations for what data visualization you can use. Here’s a quick reference for what some of the most popular charts are for:

    • Column/Bar chart – Compares categories with one another by depicting their numerical standing in terms of column height (or bar length, in the case of bar charts) side by side (think of lining children up and looking at how tall they are)
    • Line chart – Shows how data changes over time or another variable that continuously increases at a regular rate
    • Pie chart – Sets items as being parts of the same whole to depict and compare how much each contributes to or partakes of that whole
    • Scatter diagram – Sets two variables as axes on a Cartesian plane and plots data as points on that plane to show whether a relationship exists between the two variables, and if so, what kind of relationship they have with one another

    Intelligent answers for questions about your data

    Thanks to natural language processing, users are able to ask a specific question or make a visualization request to Ideas, and Ideas will respond by providing a chart that answers that question or fulfills that request. For instance, if you have sales data for shirts, sweaters, boxers, briefs, jeans, and socks, and you ask for “total sales for boxers, briefs, and socks,” Ideas will show you a pie chart showing how much those three items contributed to their total sales. You can thereafter revise the label of that chart to “Total sales for underwear.”

    Save time by using dynamic array formulas

    For the longest time, Excel users had to enter one formula in a cell to have something returned in that particular cell only. If, for instance, you opted to use formulas (instead of the Remove duplicates command) to obtain unique values from a list containing repeating names, you’d have had to nest all sorts of functions that only increase in complexity for every succeeding cell that you use. This is no longer the case, thanks to dynamic array formulas.

    Simply put, dynamic array formulas let users obtain multiple results that “spill” over multiple cells by just executing a single formula. Plus, just like how a single-cell formula result changes when that formula’s source reference changes, the results of the entire array also stay in sync with the changes in the source reference.

    To illustrate, let’s say that you use the Unique function to obtain a list of non-repeating names from a list that mentioned the name “Eric” a lot. The resulting array will be a list that mentions “Eric” only once, but if all instances of “Eric” were deleted from the source list, then “Eric” will be taken out of the results array.

    Besides Unique, these are some of the other functions that showcase the dynamic array feature:

    • Filter – Extracts all records that match the criteria that the user set (e.g., a list of names of people with green eyes)
    • Randarray – Generates a table full of random whole numbers or decimals between two values that are provided by the user
    • Sequence – Creates a table full of numbers that begin at a specified value and increase by an increment set by the user
    • Sort – Extracts unique values from an array and rearranges them into a new array according to the ascending or descending order of a chosen column index (e.g., a list of billionaires and their respective net worth, sorted from lowest to highest net worth)

    Make data extraction easier with XLOOKUP

    Let’s say you have a table that records the meals that Mark, Martha, and Marion ate for breakfast, lunch, and dinner on May 5, 2020. The names of these three individuals are in Column A, while what they had for breakfast, lunch, and dinner are in Columns B, C, and D, respectively.

     

    May 5, 2020
    NAME Breakfast Lunch Dinner
    Mark Scrambled eggs, bacon, and home fries Pork chop and mashed sweet potatoes Shrimp and vegetable tempura with rice
    Martha Avocado toast Aglio olio Salad Nicoise
    Marion Pancakes and sausages Fried chicken and cheese grits Roast beef with baked potato

     

    Finding out what Mark ate for lunch is easy enough to do manually, but now imagine his name among those of 20,000 other people, in a list that’s randomly ordered, in a table spanning the year 2020. Instead of driving yourself crazy, you can retrieve that information efficiently through Excel’s XLOOKUP function.

    Extracting data that corresponds to other pieces of data is what lookup functions are for. The very first to be rolled out was Vertical Lookup or VLOOKUP, a function that goes row by row to look from left to right to retrieve your desired information. Next came Horizontal Lookup or HLOOKUP, which did the same thing, except it went column by column to look from top to bottom.

    The latest and best iteration of the lookup function is XLOOKUP. It combines both VLOOKUP and HLOOKUP and improves them by being able to go right to left and bottom to top as well.

    Let’s go back to our 2020 meals table example above. With XLOOKUP, you can find out who ate roast beef with baked potato on April 2. However, if many people ate that meal that day, you’ll only be able to retrieve either the first or last match in the table depending on whether you specify that the search be done from first to last or in reverse order. Returning multiple matches is possible, but only by integrating other functions with XLOOKUP.

    Despite XLOOKUP’s inability to return multiple matches on its own, it can return multiple values from a single match (a la dynamic array). To illustrate, let’s limit our example table to just one day, and we want to extract what Marion ate for breakfast, lunch, and dinner. If you choose to use the older VLOOKUP function, you have to perform it three times — once for every meal. With XLOOKUP, you only have to do it once. This is such a huge time-saver, especially if you want to extract entire rows or columns of data from your source table.

    Excel is now more user-friendly than ever before. To take full advantage of the best features Excel has to offer your business, contact us today.

    Published with permission from TechAdvisory.org. Source.

  • Office 365 is now Microsoft 365: New features, same price

    Office 365 is now Microsoft 365: New features, same price

    Microsoft recently launched its new slate of consumer service plans: Microsoft 365 (M365) Personal and Family. This will replace Office 365 (O365) Home and Personal, signaling the shift of the company’s consumer bundles from just a set of tools to powerful solutions that help you manage both work and life. But Microsoft isn’t simply rebranding — it’s also introducing new capabilities to its apps. Here’s what you can look forward to.

    #1 Microsoft Editor

    Powered by artificial intelligence (AI), this new editing feature helps you write better by providing advanced grammar and style refinements. It will alert you if you’re using a word too often or constructing overly complex or unclear sentences. It can also make language and style suggestions, rephrase full sentences, and check for plagiarism.

    Microsoft Editor is now available in 20 languages in Word and as a browser extension in Outlook. Soon, you can also install it as a Microsoft Edge and Chrome plug-in so you can always submit great writing anywhere on the web.

    #2 Presentation Coach and updated Designer

    Create better presentations using the AI-driven Presentation Coach feature that helps you avoid filler words, grammar mistakes in your speech, and a monotone pitch that may put your audience to sleep.

    What’s more, the enhanced PowerPoint Designer gives you access to over 8,000 images and 175 looping videos, 300 new fonts, and 2,800 new icons. It also has the capability to convert text into a timeline and suggest potential slide layouts whenever you add an image.

    #3 Money in Excel

    If you use Excel to manage your personal or family budget, then you’ll love the upcoming new feature called Money in Excel that lets you link your bank and credit card accounts and import your data into your spreadsheets. You can also track your transactions line by line, categorize your expenses, and create a monthly spending chart so you can analyze your habits.

    #4 Integrated personal and work calendars

    Outlook now allows you to look at just one interface for both your personal and work calendars. This prevents your coworkers from setting meetings with you during your blocked personal events. You can also import your child’s school calendars so you’ll be alerted of their conferences, plays, and other academic events.

    #5 Teams for consumers

    Microsoft Teams will soon have new consumer-centric features that let you connect and collaborate with your friends and family. So if you already use Teams for work, you will be able to connect it with your Teams personal account and easily switch between those two accounts, just like in Outlook.

    #6 Family Safety app

    Aside from adding new features to their existing apps, Microsoft will also roll out its new Family Safety app for iOS and Android later this year. Similar to Apple’s Screen Time, the app is made to keep your family safe in both the digital and physical worlds.

    It lets you see what your children are doing on their Windows PCs, Xboxes, and Android and iPhone devices, and give you an analysis of their time spent on each device, so you can manage their screen time. You can use it to set limits for apps and content filters by age and even block specific websites and games on Xbox. The Family Safety app will also provide you with location sharing and driving reports.

    Despite all these new features and upcoming new app, the M365 consumer bundles are still priced identically with its O365 counterparts: M365 Personal at $6.99/month or $69.99/year for one user, and M365 Family at $9.99/month or $99.99/year for up to six users.

    To stay updated with the latest Microsoft news and see how these changes can help improve your business, get in touch with our IT experts today!

    Published with permission from TechAdvisory.org. Source.

  • 3 Excel level-up tips

    3 Excel level-up tips

    Microsoft Excel’s new features now make it easier than ever to process data and present information visually. Help yourself to our three useful tips below.

    Take advantage of Ideas in Excel

    If you’re not sure which type of chart best tells a particular story out of a table of data, don’t worry — Excel’s artificial intelligence (AI) has got your back. Just click any cell in a data range, then go to the Home tab and click the Ideas button. A task pane will appear, showing recommendations for what data visualization you can use. Here’s a quick reference for what some of the most popular charts are for:

    • Column/Bar chart – compares categories with one another by depicting their numerical standing in terms of column height (or bar length, in the case of bar charts) side by side (think of lining children up and looking at how tall they are)
    • Line chart – shows how data changes over time or other variable that continuously increases at a regular rate (think of someone tracking their weight weekly during the entirety of their three-month diet)
    • Pie chart – sets items as being parts of the same whole to depict and compare how much each contributes to or partakes of that whole (think of a statistician showing a town’s composition by race)
    • Scatter diagram – sets two variables as axes on a Cartesian plane and plots data as points on that plane to show whether a relationship exists between the two variables, and if so, what kind of relationship they have with one another (think of recording the temperature at noon every day and noting sales of bottled water to see if increasing temperatures lead to increasing sales)

    Coming soon: Intelligent answers for questions about your data

    Thanks to natural language processing (NLP), users will soon be able to ask a specific question or make a visualization request to Ideas, and Ideas will respond by providing a chart that answers that question or fulfills that request. For instance, if you have sales data for shirts, sweaters, boxers, briefs, jeans, and socks, and you ask for “total sales for boxers, briefs, and socks,” Ideas will show you a pie chart showing how much those three items contributed to their total sales. You can thereafter revise the label of that chart into “Total sales for underwear.”

    Save time by using dynamic array formulas

    For the longest time, Excel users had to enter one formula in a cell to have something returned in that particular cell only. If, for instance, you opted to use formulas (instead of the Remove duplicates command) to obtain unique values from a list containing repeating names, you’d have had to nest all sorts of functions that only increase in complexity for every succeeding cell that you use. This is no longer the case, thanks to dynamic array formulas.

    Simply put, dynamic array formulas let users obtain multiple results that “spill” over multiple cells by just executing a single formula. That’s the array part, but why is it called “dynamic?” Well, just like how a single-cell formula result changes when that formula’s source reference changes, the results of the entire array also stay in sync with the changes in the source reference.

    To illustrate, let’s say that you use the Unique function to obtain a list of non-repeating names from a list that mentioned “Eric” a lot. The resulting array will be a list that mentions “Eric” only once, but if all instances of “Eric” were deleted from the source list, then “Eric” will be taken out of the results array.

    Besides Unique, these are some of the other functions that showcase the dynamic array feature:

    • Filter – extracts all records that match the criteria you set (e.g., a list of names of people with green eyes)
    • Randarray – generates a table full of random whole numbers or decimals between two values that are provided by the user
    • Sequence – creates a table full of numbers that begin at a specified value and increase by an increment set by the user
    • Sort – extracts unique values from an array and rearranges them into a new array according to the ascending or descending order of a chosen column index (e.g., a list of billionaires and their respective net worth, sorted from lowest to highest net worth)

    Make data extraction easier with XLOOKUP

    Let’s say you have a table that records the meals that Mark, Martha, and Marion ate for breakfast, lunch, and dinner on July 4, 2019. People’s names are in Column A, while meal names for breakfast, lunch, and dinner are in Columns B, C, and D respectively.

    JULY 4, 2019
    NAME Breakfast Lunch Dinner
    Mark Scrambled eggs, bacon, and home fries Pork chop and mashed sweet potatoes Shrimp and vegetable tempura with rice
    Martha Avocado toast Aglio olio Salad Nicoise
    Marion Pancakes and sausages Fried chicken and cheese grits Roast beef with baked potato

    Finding out what Mark ate for lunch is easy enough to do manually, but now imagine his name among 20,000 other people, in a list that’s randomly ordered, in a table spanning the entire year of 2019. Instead of driving yourself crazy, do it programmatically via Excel’s XLOOKUP function.

    Extracting data that corresponds to other pieces of data is what lookup functions are for. The very first was Vertical Lookup or VLOOKUP, a function that goes row by row to look from left to right to retrieve your desired information. Next came Horizontal Lookup or HLOOKUP, which did the same thing, except it went column by column to look from top to bottom.

    The latest and best iteration of the lookup function is XLOOKUP. It combines both VLOOKUP and HLOOKUP and improves them by being able to go right to left and bottom to top as well.

    Let’s go back to our 2019 meals table example above. With XLOOKUP, you can find out who ate roast beef with baked potato on April 1. However, if many people ate that meal that day, you’ll only be able to retrieve either the first or last match in the table depending on whether you specify that the search be done from first to last or in reverse order. Returning multiple matches is possible, but only by integrating other functions with XLOOKUP.

    Despite XLOOKUP’s inability to return multiple matches on its own, it can, however, return multiple values from a single match (a la dynamic array). To illustrate, let’s limit our example table to just one day, and we want to extract what Marion ate for breakfast, lunch, and dinner. If you choose to use the older VLOOKUP function, you have to perform it three times — once for every meal. With XLOOKUP, you only have to do it once. This is such a huge time-saver, especially if you want to extract entire rows or columns of data from your source table.

    Excel is now more user-friendly than ever before. To take full advantage of the best features Excel has to offer your business, contact us today.

    Published with permission from TechAdvisory.org. Source.

  • Help yourself: use these Office 365 features

    Help yourself: use these Office 365 features

    Need help getting some work done? Microsoft constantly releases new Office 365 features that can give you an edge over the pile of work on your computer. With some of the new Office 365 features, you can Skype over a document with your coworkers, plot charts into Excel with ease, and even sign for a document electronically with any device. So treat yourself and use these features to increase your productivity in the workplace.

    Simultaneously edit a document with other people

    With Office 365, people can work together on any Word, PowerPoint, or Excel document in real time. This means multiple people can edit a document and figure out what changes have been made and who made them.

    So if your employees are still creating documents by themselves, sending them over to their coworkers by email, and silently waiting for a reply, then this Office 365 feature can help cut down on unnecessary email waiting time.

    Skype over a document with your coworkers

    In addition to being able to work together on a single document, you can also verbally discuss the edits over Skype. This application lets you talk to everyone who is working on the document with a simple click of a button. You can also keep talking to your coworkers even after you’ve left the Office document session.

    Link to files, don’t attach them

    Is the file you’re sending too big? With the business editor of Office 365, you won’t have to worry about compressing your documents before sending them over email. You can simply copy a link to the cloud file in your email and people with access to the link can immediately begin editing.

    Let Bing find presentation images for you

    Sometimes you just need that second opinion when you’re picking images for your presentation. That’s where Office Sway comes in. Basically, the application employs Bing image search to locate possible images that you can use for your presentation based on the words in the document.

    Laser pointer mouse icon

    Forgot your laser pointer on the day of your presentation? Simply go into presentation mode and hold Ctrl and the left mouse button to replace your cursor with a laser pointer.

    Change your electronic scribbles into text

    This OneNote feature allows you to convert your electronic scribbles into text in just a few steps. Simply choose the Lasso Select tool, circle the area you want edited, and click the Ink to Text option.

    Electronic signatures

    Printing out a document to sign it and then scanning it back to digital format is a bit outdated. With the Docusign app, you can set up your own electronic signature and sign documents easily from any device.

    Create Excel charts with ease

    Office 365 has built in new features that will allow Excel to better present and format your data. Simply select the cells that you would want in a graph or table, open the Quick Analysis tool, and pick how you would like to visually represent your information. Voila! Instant chart.

    Turn data from Excel into a map

    Provided that your data takes note of geographic locations, the Power Map feature can immediately distribute your recorded facts and figures over a 3D map.

    Create and convert PDF Files

    You have a variety of choices when it comes to creating PDF files from Word documents. Exporting your Word documents into PDF files can be done by going to File > Export > Create PDF/XPS. You also have the option of saving just a portion of a file by indicating the pages you want to be converted into a PDF.

    On the other hand, if you would like to edit a PDF, just open the file as a Word document. From there, you can copy and edit any content, images, or diagrams.

     

    Consider utilizing some or all of these features to make the most out of Office 365 and make your life a bit easier. If you’d like to know more about other features that Office 365 has to offer, give us a call.

    Published with permission from TechAdvisory.org. Source.