smallseo.info

microsoft-excel-2013 interview questions

Top microsoft-excel-2013 frequently asked interview questions

Excel 2013 Help Offline View

Is there a way to enable offline viewing of Excel 2013's help window like that of 2010's?

Every time I hit the F1 button, Excel wants to connect to the internet to help me!

Thank you!

Edit:

Is there a way to enable offline viewing for VBA for Excel 2013 as well?


Source: (StackOverflow)

Excel 2013, can data be re-sorted automatically?

Is there a way to re-sort automatically? I have automatically updating cells and depending on incoming values the rankings change. I am looking for a way to have the table resort automatically (similar to conditional formatting) without having to click the re-sort button.

The goal here is to accomplish such purely through a built-in Excel2013 function. I am not looking for a solution that involves additional cells that aide the sort, such as Rank(),...

Edit

I include the code of a macro that refreshes the workbook at set interval and also included code within one worksheet that is supposed to refresh the tables on that one sheet on Worksheet_Calculate. I am getting a runtime error not sure what is wrong?

Public RunWhen As Double
Const frequency = 5
Const cRunWhat = "DoIt"  ' the name of the procedure to run

Sub StartTimer()
    RunWhen = Now + TimeSerial(0, 0, frequency)
    Application.OnTime RunWhen, cRunWhat, Schedule:=True
End Sub

Sub DoIt()
    Sheets("RAWDATA").Calculate
    ActiveSheet.Calculate
    StartTimer  ' Reschedule the procedure
End Sub

Sub StopTimer()
    On Error Resume Next
    Application.OnTime RunWhen, cRunWhat, Schedule:=False
End Sub

and the code that supposedly refreshes the tables

Private Sub Worksheet_Calculate()

With Application
    .ScreenUpdating = False
    .EnableEvents = False
    .DisplayAlerts = False
End With

ActiveSheet.ListObjects("Table2").AutoFilter.ApplyFilter
    With ActiveWorkbook.Worksheets("Strategies").ListObjects("Table2").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

ActiveSheet.ListObjects("Table3").AutoFilter.ApplyFilter
    With ActiveWorkbook.Worksheets("Strategies").ListObjects("Table3").Sort
        .Header = xlYes
        .MatchCase = False
        .Orientation = xlTopToBottom
        .SortMethod = xlPinYin
        .Apply
    End With

With Application
    .ScreenUpdating = True
    .EnableEvents = True
    .DisplayAlerts = True
End With

End Sub

Source: (StackOverflow)

Conditional standard deviation and standard errors in Excel

I have data I would like to chart in an Excel 2013 spreadsheet. Here's a fragment of the data

-------------------------------------------
| intervalMode  | keyboardEventsPerSecond |
-------------------------------------------
| orienting     | 0                       |
| orienting     | 0                       |
| orienting     | 0.171115674             |
| orienting     | 0                       |
| orienting     | 0                       |
| opportunistic | 0                       |
| opportunistic | 0.016913605             |
| opportunistic | 0                       |
| opportunistic | 0                       |
-------------------------------------------

When I chart the data I'd like to put the average for each mode. This is easily achieved with a table containing the formula

=AVERAGEIF(AllDataFromSQL!A2:A14057,Graphs!A3,AllDataFromSQL!B2:B14057)

which uses the first and second column of the fragment shown as its first and third argument. Using that formula I get this table:

--------------------------------------------------
| intervalMode  | average keyboardEventsPerSecond |
--------------------------------------------------
| lean-back     | 0.009044655                     |
| opportunistic | 0.01058782                      |
| orienting     | 0.036665215                     |
| purposeful    | 0.03851359                      |
| respite       | 0.120037091                     |
--------------------------------------------------

That's great. If I chart that table I get this

Basic Chart

I'd like to add error bars, which I can do in the Excel chart tools. However I end up with this

Chart with Error Bars

The error bars for the first two bars extend below the axis, and they should not.

To fix this I was going to switch to custom error bars and add two columns to my table for the positive and the negative error bars. Hence I expect to use a formula something like

=STERRIF(AllDataFromSQL!A2:A14057,Graphs!A3,AllDataFromSQL!B2:B14057)

But no formulas seem to be built in for the standard error. So I could roll it myself using something like

=STDEVIF(AllDataFromSQL!A2:A14057,Graphs!A3,AllDataFromSQL!B2:B14057)/SQRT(COUNTIF(AllDataFromSQL!A2:A14057,Graphs!A3))

But STDEVIF does not seem to exist. Of course I can add extra columns and calculate that by hand, but is there a better way to calculate the standard error in the same way I have used AVERAGEIF to simply calculate a conditional average?


Source: (StackOverflow)

Apply Names in Excel is bugged?

I see people had this problem 10 year ago and it's still not fixed. Or maybe I just don't know how to use this feature.
enter image description here

In my sheet I have these named ranges:
A1 - "name1"
B1 - "name2"
C1 - "name3"

In some other 3 cells I have these formulas:

=A1
=A1+B1
=COUNT(A1:C1)

After running Apply Names... I expect to have this:

=name1
=name1+name2
=COUNT(name1:name3)

I never get that no matter what options I choose. Moreover, usually my formulas become completely ruined...

=name1
=name1
=name1

How could I get the result I need?
I am knowledgeable in VBA, so I tried to design a solution to this problem using VBA, but the code was not working either.

I'm using Excel 2013, 32-bit (MS Office Professional Plus). OS - Windows 7 Enterprise, 64-bit.


Source: (StackOverflow)

Can I customise the new Timeline slicer in Excel 2013?

Excel 2013 introduces timeline slicers for the first time. These allow you to filter pivot tables easily by selecting time ranges - either by year, quarter, month or day.

enter image description here

It's nearly a useful tool, but in the world of business (Excel's primary market!) we generally work in fiscal periods - April to March is common here in the UK.

Out of the box, this timeline slicer doesn't appear to support fiscal years - it's calendar periods only, which means it's useless for my users. If I presented them with a slicer referring to Q1 as Jan to March, they would be terribly confused. This is highly unintuitive to folk who have spent their entire careers referring to Q1 as Apr-Jun.

I can't find any way to set custom date periods, but nothing I've read online gives me a definitive answer as to whether there's any kind of solution, or not.

Can anybody either a) show me how to do this, or b) provide an authoritative source stating timelines simply don't allow custom time periods?


Source: (StackOverflow)

Excel TEXT function does not work properly

Let say I have the following values in the cells

A1 : `=Now()` which displays 17.03.2014 13:45 
A2 : 12.03.2014

Both are in date format and I have the function in another cells like this:

B1 : `=TEXT(A1;"dd.mm.yyyy")`
B2 : `=TEXT(A2;"dd mm yyyy")`

In the first case I received a #VALUE Error in the cell In the second case I received : dd 03 yyyy like only the month is working.

I am under Windows 7 and Office 2013 Professional.

Any guess what it could be?


Source: (StackOverflow)

Disable Excel 2013 cell animation

When clicking into a cell of a Excel 2013 sheet, the green border of the cell is animated while moving from the current cell to the new one.

This is really disturbing. How can I disable this animation?


Source: (StackOverflow)

How to merge two Excel columns into one (the other way)

I've a list as shown in A and B columns, and I want to merge as shown in D:

Enter image description here

How can I do that?

Here, alphabets (a,b,c,d,e,f,g,h) are just used as place holders. What I really need in column D is A1,B1,A2,B2,A3,B3,A4,B4.


Source: (StackOverflow)

Excel: how to delete text and fill color?

In Excel you can press the Delete key to delete the contents of highlighted cells, but not the cells' fill color. Instead you need to click on the Fill tool on the ribbon and choose No Color.

Is there a shortcut key to this? I have done some Googling and it seems like the only way is to create a macro.


Source: (StackOverflow)

How do I show only the print area in Excel and grey out the rest?

I'm trying to replicate an Excel spreadsheet that someone sent me and I can't figure out how. The print area is set, and all cells outside the print area are greyed out. The cells aren't simply shaded, because Excel says they have no fill, and they aren't hidden/locked/protected because I can still edit them just like normal cells. The only difference is that they're greyed out and the print area has a thick blue border around it. This spreadsheet doesn't have any macros, either.

What feature of Excel is this using?


Source: (StackOverflow)

Excel adds double quotes on csv export [duplicate]

This question already has an answer here:

I am experiencing a weird unknown behavior in excel. The sheet I want to export to a csv file consists of 4 columns with data like this:

site.aspx|de|lblChanges.Text|some text that will be used somewhere

Now what happens is, if the last column containg the text has doublequotes in it, Excel adds another double quote to it for every double quote already in it.

Example:

site.aspx|de|lblChanges.Text|some text that will used somewhere <a rel='nofollow' href="/clickety.aspx">here</a>

Gets transformed into

site.aspx|de|lblChanges.Text|"some text that will used somewhere <a rel='nofollow' href="/clickety.aspx">here</a>"

Notice the extra doublequotes at the beginning and the end, which clearly should not be there. this data gets inserted in a database and used as text resources for globalization. If I render a literal control with those extra double quotes the functionality breaks.

How can I supress this behavior in Excel?


Source: (StackOverflow)

How to prevent Excel to use the OS regional settings for date patterns in formulas

According to this question I have the following problem:

I want to use some Excel function (not the cell formatting) like TEXT(A1, {date_pattern})

But the person who answer my previous question make me found that the date pattern change according to the Windows Regional Settings.

However, my OS (Windows 7) is in English and the Office suite as well. By looking in my Regional settings it show even a pattern using English notation (dd.MM.yyyy)

Region and Language

I want to know if there is any way to disable such behaviour from Excel, meaning I want to always use the English patterns and never the localized ones because I do not want the behaviour of my Excel sheet to change according to localisation of the reader.

A simple case would be reformatting some date field to a computer centric way like this: "yyyymmdd_hhss" this is recognized universally and can be sorted up and down easily. But as I am in the French part of Switzerland part I should write "aaaammjj_hhss" and if I send this Excel to a colleague in Zürich he would not be able to see the proper date as he got the Swiss German localization (his excel would expect "jjjjmmtt_hhss")

We were clever enough to install all windows and office in English but we still face problem like this because this link to the OS regional settings.

For me the changing Windows Settings is not an option because all the other programs are using this settings.


Source: (StackOverflow)

Why won't Excel 2013 work as an "Open With..." program in Windows 7?

I just recently uninstalled Office 2010 32-bit and installed Office 2013 64-bit on my computer. I was sent some text files that are tab-delimited, so I want to open them with Excel.

I am trying to add Excel to the Open With... option in the right-click menu in Windows 7. Every time I try, I open the selection screen, browse to Excel.exe in the Office15 folder and press OK, but it refuses to stay as an option on the selection screen.

I know that I can open Excel and then open the file, or even drag-and-drop it onto Excel, but seeing as I'll be opening a lot of these files over the next few weeks, I'd really rather add it to the right-click menu (like I used to do all the time).

Any ideas as to why it won't allow me to open that way or how to fix it?


Source: (StackOverflow)

How to find and replace the character “*” in excel text values in cell, but NOT formulas

Very similar to this question: How to find and replace the character "*" in excel

But I need to leave formulas untouched. I've got about 50+ sheets that have two types of cells with "*"

Case 1 contents - the value of the cell might be: "*** 1.43"

Case 2 contents - the value of the cell might be: "=100*B3"

I would like to find and replace all the Case 1 asterisks with "" while ignoring the Case 2 cells that contain formulas using asterisks as a multiplier. Basically, change the static cells but don't alter cells with formulas.

Thanks!


Source: (StackOverflow)

Why is Excel 2010/2013 taking 10 seconds open any file?

I have a fast Windows 7 PC with two SSDs and 16GB of RAM, so I'm used to programs loading very fast. But recently, for no reason I can figure out, Excel has started taking way too long to open Excel files (of any size--even blank files). This is occurring with Excel 2010 and with Excel 2013 after I upgraded, hoping to solve the problem. Here a couple scenarios:

  • If I start Excel directly, it opens almost instantly. No problem there.
  • If I start Excel directly, and then open any Excel file (.xls or .xlsx), it loads almost instantly. Still no problem
  • BUT if I attempt to open any Excel file directly, with Excel not running, it consistently takes 10-11 seconds for Excel to start. I get no error messages, just a spinning cursor for 10-11 seconds, and then the file opens.

During the delay while Excel is trying to start, I'm not really seeing any discernible spike in CPU or memory usage, other than explorer.exe. This problem is only occurring with Excel, not Word or any other program I'm aware of.

I've searched around quite a bit on this question and found various others who have experienced it, but the solutions that worked for them are not working for me. For a few people it was a problem with scanning network drives, but my problem is purely with local files; I have no network drives, and the problem persists even with all network connections disabled.

Some people suggested worksheets with corrupted formulas or links, but I'm experiencing this with ANY Excel file: even blank worksheets.

Others thought it was a problem with add-ins, but I have all Excel add-ins disabled (as far as I can tell).

One person solved it by disabling a "clipboard manager" process that was running in the background, but I don't have that. I've disabled as many startup and background processes as I can, but the problem persists. I've run malware scans, disk cleanup, CCleaner, and installed Excel 2013. I've deleted temporary files, enabled SuperFetch, and edited registry keys. Still can't get rid of the problem. Any ideas?

My system details: Windows 7 Professional SP1 64-bit, Excel 2013 32-bit, 16GB RAM, all programs installed on SSD.


Source: (StackOverflow)