Search

Excel Tricks
24
Jan

How To Create Error Message Alert in Excel
  • David Ringstrom
  • 953
Summary: This post aims to explain how to create Microsoft Excel data validation custom messages to help people enter worksheet data correctly and show special error messages to help them fix invalid entries without being frustrated.-----------It is no secret that almost all certified public accountants use Excel on a daily basis. Excel is an invaluable tool that allows accounting professionals to work with complex data sets and communicate vast amounts of information to clients and co-workers.But, you can’t deny that working with huge and complex data with multiple sheets can lead to errors and a significant time suck. However, Excel allows users to define custom input or error messages to prevent data entry errors. Imagine that you're responsible for creating, maintaining, and assigning usernames and passwords for your company's employees. You can easily accomplish your task using an excel spreadsheet. After that, you transfer the data to your colleague. At this point, someone else needs to handle this.So, how will he/she manage the data? The best way to ensure the job is done right is to create input and error messages using data validation.  What are Data Validation Messages?Data validation allows you to display instructions to your spreadsheet users.Types of Validation messages:When a cell is selected, an Input Message is displayedAn Error Alert can be displayed if invalid data is entered in a cellHow To Create an Input Message?When the cell is selected, an Input Message can be displayed to help people know what data should be entered.Here's how to display a short message when a cell is selected.Validate the data in the selected cellsSelect the Data tab from the RibbonClick Data Validation under the Data Tools groupPick the data validation options from the Settings tabChoose the Input Message tab, then check the box next to Show input message when the cell is selected.In the Title box, type the headline of your message. At the top, the heading text will appear in bold.In the Input message box, type a short message. You can create line breaks if you want them by pressing the Enter key.For help adding an error alert, click OK or follow the steps below.You will now see the Input Message when you click the cell.Point To Note: The limit of message text is 255-character What Are The Data Validation Error Alerts?The Error Alert feature of the cell is automatically turned on when you add data validation.People cannot enter invalid data in the data validation cell by default because of the error alert settings.Excel displays its default error message if you try to enter an invalid value into a cell with those default settings. It includes the following:Title: Microsoft ExcelIcon: Red circle with white XText: This value doesn't match the data validation restrictions defined for this cellButtons: Cancel, Retry, Close, HelpIn the data validation error message, the following will happen when you click any of the command buttons or on the Close button.Retry: Click on the Retry button to close the message box. You may type a different value in the validation cell contents by selecting them and typing it in.Cancel/ Close: Click on the Cancel button to remove Data validation cell contentsHelp: Click Help button to stay the message box open. The web browser will automatically open the Data Validation page on the Microsoft website.You won't be able to enter the invalid data with that default message, but it doesn't disclose why the data is invalid. Refer to the sections below for more information about changing the error alert message and settings.Show Error Alert SettingIf data validation is enabled by default, you'll receive an alert when you enter invalid data. These steps explain how to disable this setting.You will need to select the cells you would like to apply data validation.Select Data Validation from the Ribbon's Data tabSelect the Error Alert tab within the Data Validation dialog boxIn Excel, you can easily create error messages using this method.If you want to learn more about excel or how to create an error message alert, please visit Excel-Accountant. Here, you will get live webinars and other study materials that will help you to sharpen your skills.Register now for the latest updates!
  • Read More
29
Oct

Excel at Excel with 13 awesome tips!
  • David Ringstrom
  • 99
Microsoft Excel is an indispensable tool for all accounting and tax professionals. Even though you may be aware of the basics of Excel, having some quick shortcuts can be quite handy. It can save you considerable time and rocket your efficiency. We have gathered 13 tips that will prove helpful to you. 1. Auto-fill cells The power of Excel goes beyond just shortcuts. Its agility is based on its hyper functionality to follow what you type, for instance in the column to the right of your data. It then gathers suggestions to fill the rest of your cells after you have typed some examples. This saves you from the onerous task of info-typing. You need to hit Enter to accept Excel’s suggestions. 2. Spreadsheet Shortcuts There are plenty of formatting options when it comes to spreadsheets. It enables you to add special formatting without much effort. You can use the format painter function to mirror that formatting on other cells. You simply need to double-click on the format painter icon to lock the function and then single-click on any part of the spreadsheet that you want to format. Hit Escape to unlock the cursor. There are some nifty shortcuts if you have to deal with large data. Give these a shot: Format numbers to include two decimal places: Ctrl+Shift+1 Format as time: Ctrl+Shift+2 Format as date: Ctrl+Shift+3 Format as currency: Ctrl+Shift+4 Format as percentage: Ctrl+Shift+5 Format in scientific/exponential form: Ctrl+Shift+6 3. Highlight Data using Sparklines As some of you may already know, Sparklines come as a built-in feature. With the help of Sparklines you can display small charts inside individual cells such as- line charts, bar charts, simple win/loss charts and so on. To create one, select the range of numbers you want to display, go to the Insert menu and select the chart option you want. Then, choose a location range along a single row/column in the same worksheet. With Sparklines, you can display trends in your data in a compact and neat manner. 4. Splice data easily You can filter data with ease with this function. To do so, select any range in a table or PivotTable, and then go to Insert > “Slicer,” in the top right corner. Then, select the column you want to filter by. 5. Manipulate data with pivot tables Pivot tables are a great tool when dealing with intricate and large data. Not only do they allow you to analyze data, but you can also detect patterns and make comparisons with these interactive tables. To make a pivot table, simply go to the Data menu and select PivotChart Wizard or PivotTable. The wizard helps you select the data in your PivotChart and format it in a convenient way. 6. Awe‘sum’! A feature that brought respite to every Excel user. It gives you the total of a data set without the need for an equation. You need to simply click anywhere in the tables and press Ctrl+Shift+T. You will be furnished with the total of the total row that you created. 7. Check formulas and results with ease When dealing with a large spreadsheet, you may want to check some formulas swiftly. To rapidly shift between the cell data and formula, press Ctrl+(~) keystroke. 8. Use the status bar without a formula You can check the statistics of data in a table without typing any formula. All you need to do is select the cells you wish to see the stats on. Look at the bottom right of your window and you will find instant stats of the same. 9. Hide zero values Often, users wish to hide zero values when dealing with huge data sets. Fortunately, Excel offers the option to hide zero values. Go to ‘File’ and choose ‘Options’. There, go to Advanced and uncheck the box for “Show a zero in cells that have zero value.” (Mac users: Go to the “Excel” drop-down menu and choose “Preferences,” then uncheck “Show zero values.”) 10. Excel templates All versions of MS Excel come with many templates. To create an amortization table with a template, right-click on a blank worksheet. Gain access to it with ‘Insert’. Under ‘Spreadsheet Solutions’, there are several templates, including amortization schedule. Excel also offers you the option to make your own template by saving a worksheet as an Excel template. 11. Instant Chart To make an instant chart, just select the cells you want to include and press Alt+F1. 12. Find the worksheet you want You can get a list of the most recently used workbooks by going to ‘File’ or ‘Office Button’, depending on the version you use. There, you will find ‘Recent’. This will save you time which would have otherwise spent looking for the worksheet you want. Furthermore, in the ‘Advanced’ section, go to ‘Options’ and you can alter your view as well as adjust the number of recent documents shown. You may want to change the number to 50 for more convenience. 13. Create tables in a jiffy First, click anywhere in your data. Then press Ctrl+T. This will produce an instant table displaying your data. These tips and shortcuts can definitely help you become a wizard of Excel. Share them with your colleagues and friends and help them up skill too!
  • Read More
11
Aug

Best Microsoft Excel Keyboard Shortcuts
  • Lynn Fountain, CPA
  • 1678
While using Excel, using keyboard shortcuts is one of the basic excel skills that save lots of time every day. There are more than 500 keyboard shortcuts, but not all of those are important. Here are some important and useful shortcuts. Basic Keys Key Description Control + Up Arrow move to the top end Control + Down Down Arrow move to the down end Control + Right Right Arrow move to the right end Control + Left Left Arrow move to the left end Control + C copy Control + V paste Control + X cut Control + S save Control + P print Control + F4 save as Control + Z undo the last action Control + Y redo the last action Control + A select all Control + Spacebar select entire column Shift + Spacebar select entire row Delete delete F2 edit cell Esc close edit mode F1 help Menu Button right-click menu Formatting Keys Key Description Ctrl + B bold Ctrl + I italic Ctrl + U underline Ctrl + ! open format menu Ctrl + Shift + @ format as time value Ctrl + Shift + # format as a date Ctrl + Shift + $ format as currency Ctrl + Shift + % format as percentage Alt → H → O → I / A adjust cell width to content Alt → H → B → A apply border Alt H F C change font color Alt H H change cell color Alt H A C align text to the center Alt H M C merge cell Alt H W wrap text Alt H F F change font style Alt H F S change font size Alt H L N add conditional formatting Alt H T format as table Alt H J style cell Insert & Layout Keys Key Description Alt N V insert pivot table Alt N T insert table Alt N P insert picture Alt N S H insert shape Alt N S C insert charts Alt N I insert hyperlink Alt N X insert text box Alt N J insert object Alt N U insert symbol Alt N H insert header & footer Alt N Z K open sparklines Alt W V F hide/unhide formula bar Alt W V H hide/unhide heading Alt W V G hide/unhide gridlines Alt W Q C change zoom Alt W F F freeze pane Alt W F I page break view Alt W F C custom view Alt W F P page layout view Alt W A arrange windows Functions and Data Shortcuts Key Description Shift + F3 insert function Alt M R recently used functions Alt M I financial functions Alt M L logical functions Alt M T text functions Alt M E date and time functions Alt M O lookup functions Alt M G math and trig functions Alt M Q more functions Alt + = auto sum Alt M N name manager Alt D F F add filters Alt A V V insert data validation Alt A M remove duplicates Alt A R A refresh all Alt A E open text to column Alt A G group rows and columns Alt A U ungroup rows and columns Alt A B add subtotal Alt A S S open sort Favorite Keyboard Shortcuts Key Description Alt H O R rename a worksheet Alt + F1 insert the default chart
  • Read More
01
Jul

Deliver Your Presentations Like A Boss - 3 Practical Steps
  • David Ringstrom
  • 399
Making impactful presentations does not have to be very complicated. Microsoft Excel provides many options to create powerful presentations that convey your audience effectively. These simple tips will make it possible for you to present better whether you are a beginner, intermediate or advanced MS Excel user. Stick to a theme uniform formatting is the best way to engage your audience. Don’t use random fonts and other characteristics; it can make it difficult for your audience to follow you. By using a simple set of rules can make your presentation more effective. Too much use of “USING.” Here are some guidelines: Use just one font. Bold all headings for groupings of data/formulae. Put comments on your analysis in italics. Shade cells with a color key – e.g., raw data shaded grey, calculations green, results yellow. Keep things coherent Your Worksheet must follow a natural and coherent progression throughout the spreadsheet (left-right, top-bottom). Otherwise, your data will look quite incompetent. For example, you can accomplish a Worksheets flow from inputs (assumptions and raw data) through to calculation, followed by results.  Highlight necessary data To make it easier for your audience to see essential data, you need to make it stand out using some easy steps: Visually soften the non-data elements of graphs and tables. For example, make axes and gridlines the same color but lighter than the data points. Set the ‘Print Area’ so that the critical areas (and only the critical areas) of a spreadsheet are printed. Make sure page boundaries are set sensibly, so each page of printing makes sense in itself. Microsoft Excel offers many ways to make your work smoother and more accessible. Sign up to Excel Accountant for more tips, tricks, shortcuts, and automation techniques to make your Worksheet more productive.
  • Read More
29
Jun

How to Make a Budget in Excel: Our Simple Step-by-Step Guide
  • Fedrica Hanks, Accountant
  • 1987
If you are not frequently using excel, then making a budget in excel is like an unnerving task. Using an Excel budget template, no need to be an accountant for that. This step-by-step guide will help you to create a budget in excel whether your budget is simple or extremely complex. The fastest and easiest way to build an excel budget is by using the library of premade budget templates. simply go to File>New, then search for the term “budget.” Several Excel budget templates will pop up, such as a family budget, personal expense calculator, vacation budget, and more. Customizing a premade template To customize the existing template, simply select where you want to add a box and right-click. Scroll down to “Insert” and choose to add either “Table Columns to the Left” or “Table Rows Above”. With the existing tabs, you can now automatically sync the new information. How to Make a Budget in Excel from Scratch You can create excel budget spreadsheet from the scratch. Step 1 – Open a Blank Workbook – Create “Blank Workbook” in excel. Step 2 – Set up Your Income Tab – after creating blank workbook, create Income and source tab. Step 3 - Step 3: Add Formulas to Automate- Make a cleaner look to your budget spreadsheet by adding border to entire area. Once you like the look of your budget spreadsheet, it’s time to add the formulas that will automatically calculate everything for you. Step 4- Add Your Expenses – After figuring out income section, now it’s time to calculate expenses. You can either do this on the same sheet or start a new sheet. To keep expenses on the same sheet, create a new area under the “Income” section and customize it according to you. Then, use the same column headings – Due Date, Planned, Actual, and Difference. Step 6 - The Final Balance - After creating all the sections you want to track. Now it’s important to know your running balance. Basically, you need not to keep any calculator with you. It’s become easy for you when you are keeping everything on one sheet. Simply create another section at the bottom of the sheet. Then, label one row “Total Spending” and another “Final Balance.” This is an easy way to help you track your spending.
  • Read More
17
Jun

Excel Accounting Latest Tips For 2021
  • Lynn Fountain, CPA
  • 1763
As per the report, 63 percent of U.S. companies rely on Excel, down slightly from a year ago. While the program is used by organizations of all sizes, smaller companies (69 percent) find accounting with Excel especially valuable. Even the American Institute of CPAs has replaced its generic spreadsheet with Excel beginning with the 2018 CPA exam.Excel is the preferred tool for budgeting and planning across accounting and finance functions, according to the Benchmarking report. As one of the CFOs surveyed said, “For anything that is innovative or creative or requires that you bring some gray matter to the table, the spreadsheet cannot be beaten."Here are 13 tips and features to bring more agility, functionality, and usability to your Excel spreadsheets. Share this with your team as you work on upskilling your employees and temporary workers.1. Fill cells quicklyExcel now has the power to finish your tedious info-typing session. Just start typing in the column to the right of your data.Excel will then gather suggestions to fill the rest of your cells after typing a few examples. To accept Microsoft’s suggestions, press enter.2. Format spreadsheets fasterWhen working on a spreadsheet, there are ways to add special formatting to different cells, columns, and rows with less effort.After applying any formatting to the spreadsheet — such as making a cell yellow or adding borders to a group of cells — you can use the format painter function to copy that formatting to other cells. If you double-click on the format painter icon, you can lock the function and single-click on any parts of the spreadsheet that you want to format. When you’re done, hit escape to unlock your cursor.For circumstances when you need to format a large amount of data, Excel offers time-saving shortcuts for many common formatting functions. Experiment with these handy ones:Format numbers to include two decimal places: Ctrl+Shift+1Format as time: Ctrl+Shift+2Format as a date: Ctrl+Shift+3Format as currency: Ctrl+Shift+4Format as a percentage: Ctrl+Shift+5Format in scientific/exponential form: Ctrl+Shift+63. Use Sparklines to display dataSparklines are a built-in feature of Excel that allows you to display small charts inside individual cells. These can be line charts, bar charts or simple win/loss charts. To create a Sparkline chart, select the range of numbers you’d like to include, click the “Insert” menu, then choose one of the chart options. Select a location range, which must be located along a single row or column in the same worksheet as your data range. Sparklines can help you easily display trends in your data in a compact format.4. Splice data easilySlicing allows you to filter data easily.To do so, select any range in a table or PivotTable, (refer to tip #5 for more information about PivotTables), and then go to Insert > “Slicer,” in the top right corner. Then, select the column you want to filter by.5. Manipulate data with pivot tablesWhen you have a large, detailed data set, pivot tables allow you to easily manipulate your data. These tables are interactive and can help you analyze data, detect patterns, and make comparisons. Creating a pivot table is as easy as using the built-in PivotTable and PivotChart Wizard, located in the “Data” drop-down menu. The wizard helps you choose the data to include in your PivotChart and format that information in a meaningful manner.6. Go formula-free Getting a total without an equation might just be one of Excel's most valuable features.To get your total formula, click anywhere inside the Excel table. Then press Ctrl+Shift+T, and Excel will add the total to your total row that you created.7. Move between formulas and resultsTo efficiently switch between the cell data and formula, use the Ctrl+tilde (~) keystroke. This allows you to rapidly check formulas when working in a large spreadsheet.8. Use the status bar without a formulaThe status bar shows counts, sums, and averages, without typing up any formulas.Just select the cells in your table and look to the bottom right of the Excel window. You’ll see instant stats about the cells you selected.9. Hide zero valuesHiding zero values can be helpful within large data sets by allowing you to see data more clearly. To hide zero values, you simply need to change the options in your Excel setup. Navigate to this function by clicking the “File” drop-down menu, and choose “Options.” Then choose “Advanced” from the left-hand menu and uncheck the box for “Show a zero in cells that have zero value.” (Mac users: Go to the “Excel” drop-down menu and choose “Preferences,” then uncheck “Show zero values.”)10. Use Excel templatesTemplates are available for any version of Excel. If you want to create an amortization table using a template, right-click on a blank worksheet and gain access through the “Insert” command. Under the “Spreadsheet Solutions” tab, you will find various templates, including an amortization schedule, which can then be inserted into the spreadsheet. You can also create your own templates by saving a worksheet as an Excel template.11. Chart your data quicklySelect any cells in your data range/ table and then press ALT+F1 on your keyboard. Now you have an instant chart.12. Save time looking for worksheetsIf you access the “File” menu or “Office Button” in Excel — depending on the version you are using — there’s a handy button called “Recent.” Using this function, you can quickly generate a list of the workbooks you recently worked on.In the “Advanced” section of the “Options” menu, you can expand your view and adjust the number of recent documents shown. You can change the number to 50 for better access to recent folders and files.13. Create tables quicklyYou can turn data cells into a table quickly!All you have to do is click within the parameters of your data and press Ctrl+T. Now you have an instant table displaying your data.Now that you have some helpful features to reboot your Excel skills and those of the accounting professionals you manage, you will get more done, and before you know it, you’ll be expanding your team!
  • Read More

Connect with Us

Copyright © 2024 Excel Accountant. All Rights Reserved.