Best Excel Quizzes
Step Open a blank spreadsheet in Excel. Use column A for the question, column B for the answer and column C to grade the answers. Step Type your first question into cell A1. For the purposes of this demonstration the question is, 'If you mix red and blue together, what is the resulting color?'
Step Scroll or arrow over to cell C1 and type the following function: =IF(B1=', ', IF(B1='answer', 'Right', 'Wrong')). Replace 'answer' with the correct answer.
Do you want to test your basic knowledge on excel sheet? Let's play this excel practice quiz and prove yourself!
Using the example, 'If you mix red and blue together, what is the resulting color?' You would replace 'answer' with 'purple' and as long as the person taking the quiz typed 'purple' in the corresponding B1 cell, the answer would show 'Right'. If someone gives a wrong answer, 'Wrong' shows up and if they put no answer, cell C1 remains blank. Step Think of questions and answers to fill the quiz. Remember column A is for inputting questions, column B should be left blank with enough space to type answers and column C is where you input the formula. Step Continue inputting questions into column A by typing your questions into subsequent cells.
Step Skip over to the C column and begin typing in the formula into the cells in the C column. Remember to change 'B1' from the original formula to the B cell number that corresponds to the question. For a question in cell A2 you will want the formula to read =IF(B2=', ', IF(B2='answer', 'Right', 'Wrong')), again replacing 'answer' with the correct answer.
Step Decide if you want to quiz to score the participant's answers. If so, simply input the following into the corresponding D cells: =IF(C1='Right', 1, 0).
Remember to update the cell numbers to correspond with each question and answer. Step Type the following formula at the bottom of the quiz if you have decided to have Excel calculate the score: =sum(D1:D10). This is the formula for a 10 question quiz-adjust accordingly.
You can either write in the cell numbers, or after you have typed the opening parenthesis you can use your mouse pointer to click and drag to select the range of cells you want added together. If you chose to select the cells this way they should be highlighted with a blue box. Step Hide the answers before you send the quiz out. Select column C and choose 'Format CellsProtection' and check the 'Hidden' selection box. Select 'OK' to close the window and apply the setting. Now you can send the quiz out knowing the answers are hidden.
There are two kinds of users in the world: Those who make neat little tables, and those who amaze their colleagues with sophisticated charts, data analysis, and seemingly magical formula and macro tricks. You, obviously, are one of the latter—or are you?
Check our list of 11 essential Excel skills to prove it—or discreetly pick up any you might have missed. Vlookup Vlookup is the power tool every Excel user should know. It helps you herd data that's scattered across different sheets and workbooks and bring those sheets into a central location to create reports and summaries. Vlookup helps you find information in large data tables such as inventory lists. Say you work with products in a retail store.
Each product typically has a unique inventory number. You can use that as your reference point for Vlookups.
The Vlookup formula matches that ID to the corresponding ID in another sheet, so you can pull information like an item description, price, inventory levels, and other data points into your current workbook. Summon the Vlookup formula in the formula menu and enter the cell that contains your reference number.
Then enter the range of cells in the sheet or workbook from which you need to pull data, the column number for the data point you’re looking for, and either “True” (if you want the closest reference match) or “False” (if you require an exact match). Creating charts To create a chart, enter data into Excel with column headers, then select Insert Chart Chart Type. Excel 2013 even includes a Recommended Charts section with layouts based on the type of data you’re working with. Once the generic version of that chart is created, go to the Chart Tools menus to customize it. Don't be afraid to play around in here—there are a surprising number of options.
Excel 2013 includes Recommended Charts with layouts based on the type of data you're working with. IF formulas IF and IFERROR are the two most useful IF formulas in Excel. The IF formula lets you use conditional formulas that calculate one way when a certain thing is true, and another way when false. For example, you can identify students who scored 80 points or higher by having the cell report “Pass” if the score in column C is above 80, and “Fail” if it’s 79 or below. IF formulas let you pull in just the data you need. IFERROR is a variant of the IF Formula. It lets you return a certain value (or a blank value) if the formula you’re trying to use returns an error.
If you’re doing a Vlookup to another sheet or table, for example, the IFERROR formula can render the field blank if the reference is not found. PivotTables PivotTables are essentially summary tables that let you count, average, sum, and perform other calculations according to the reference points you enter. Excel 2013 added Recommended PivotTables, making it even easier to create a table that displays the data you need. To create a PivotTable manually, ensure your data is titled appropriately, then go to Insert PivotTable and select your data range. The top half of the right-hand-side bar that appears has all your available fields, and the bottom half is the area you use to generate the table. PivotTables are a summarization tool that let you perform calculations according to the reference points you enter.
For example, to count the number of passes and fails, put your Pass/Fail column into the Row Labels tab, then again into the Values section of your PivotTable. It will usually default to the correct summary type (count, in this case), but you can choose among many other functions in the Values dropdown box. You can also create subtables that summarize data by category—for example, Pass/Fail numbers by gender. PivotChart Part PivotTable, part traditional Excel chart, a PivotChart lets you quickly and easily look at complex data sets in an easy-to-digest way.
PivotCharts have many of the same functions as traditional charts, with data series, categories, and the like, but they add interactive filters so you can browse through data subsets. PivotCharts help you easily digest complex data. Excel 2013 added Recommended PivotCharts, which can be found under the Recommended Charts icon in the Charts area of the Insert tab. You can preview a chart by hovering your mouse over that option. You can also manually create a PivotChart by selecting the PivotChart icon on the Insert tab.
Flash Fill Easily the in Excel 2013, Flash Fill solves one of the most frustrating problems of Excel: pulling needed pieces of information from a concatenated cell. When you’re working in a column with names in “Last, First” format, for example, you historically had to either type everything out manually or create an often-complicated workaround. Flash Fill can automtically add data formatted the way you want without using formulas. In Excel 2013, you can now just type the first name of the first person in a field immediately next to the one you’re working on, and click Home Fill Flash Fill, and Excel will automagically extract the first name from the remaining people in your table. Quick Analysis Excel 2013’s new Quick Analysis tool minimizes the time needed to create charts based on simple data sets. Once you have your data selected, an icon appears in the bottom right hand corner that, when clicked, brings up the Quick Analysis menu. Quick Analysis speeds the process of working with simple data sets.
Best Excel Quiz For Job Interview
This menu provides tools like Formatting, Charts, Totals, Tables, and Sparklines. Hovering your mouse over each one generates a live preview. Power View Power View is an interactive data exploration and visualization tool that can pull and analyze large quantities of data from external data files. Go to Insert Reports in Excel 2013. Power View creates interactive, presentation-ready reports.
Best Excel Quiz For Job Interview
Reports created with Power View are presentation-ready with reading and full-screen presentation modes. You can even export an interactive version into PowerPoint. Will help you become an expert in no time. Conditional Formatting For most tables, Excel’s extensive conditional formatting functionality lets you easily identify data points of interest. Find this feature on the Home tab in the taskbar. Select the range of cells you want to format, then click the Conditional Formatting dropdown.
The features you’ll use most often are in the Highlight Cells Rules submenu. Conditional Formatting lets you easily highlight data points of interest. For example, say you’re scoring tests for your students and want to highlight in red those whose scores dropped significantly. Using the Less Than conditional format, you can format cells that are less than -20 (a 20-point drop) with the Red Text or Light Red Fill with Dark Red Text function. You can create many different kinds of rules, with unlimited formats available via the custom format function within each item. Transposing columns into rows (and vice versa) Sometimes you’ll be working with data formatted in columns and you really need it to be in rows (or the other way around).
Simply copy the row or column you’d like to transpose, right click on the destination cell and select Paste Special. A checkbox on the bottom of the resulting popup window is labeled Transpose.
Check the box and click OK. Excel will do the rest. The Paste Special feature transposes columns and rows. Essential keyboard shortcuts Keyboard shortcuts are the best way to navigate cells or enter formulas more quickly.
We’ve listed our favorites below.